--所有对象：
SELECT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc,
create_date, modify_date, is_ms_shipped, is_published, is_schema_published
FROM sys.all_objects
ORDER BY name
--索引对象：
SELECT object_id, name, index_id, type, type_desc, is_unique, data_space_id,
ignore_dup_key, is_primary_key, is_unique_constraint, fill_factor, is_padded,
is_disabled, is_hypothetical, allow_row_locks, allow_page_locks
FROM sys.indexes
--索引使用情况：
SELECT database_id, object_id, index_id, user_seeks, user_scans, user_lookups,
user_updates, last_user_seek, last_user_scan, last_user_lookup, last_user_update,
system_seeks, system_scans, system_lookups, system_updates,
last_system_seek, last_system_scan, last_system_lookup,
last_system_update
FROM sys.dm_db_index_usage_stats
WHERE (database_id = DB_ID('taobao') )
--某数据库下索引使用情况：
SELECT a.database_id, a.object_id, a.index_id, b.name, a.user_seeks, a.user_scans,
a.user_lookups, a.user_updates, a.last_user_seek, a.last_user_scan,
a.last_user_lookup, a.last_user_update, a.system_seeks, a.system_scans,
a.system_lookups, a.system_updates, a.last_system_seek, a.last_system_scan,
a.last_system_lookup, a.last_system_update
FROM sys.dm_db_index_usage_stats AS a INNER JOIN
sys.indexes AS b ON a.object_id = b.object_id
WHERE (a.database_id = DB_ID('taobao'))
--事务锁使用情况：
SELECT resource_type, resource_subtype, resource_database_id, resource_description,
resource_associated_entity_id, resource_lock_partition, request_mode, request_type,
request_status, request_reference_count, request_lifetime, request_session_id,
request_exec_context_id, request_request_id, request_owner_type,
request_owner_id, request_owner_guid, request_owner_lockspace_id,
lock_owner_address
FROM sys.dm_tran_locks
WHERE (resource_database_id = DB_ID('taobao'))
--存储过程使用次数：
SELECT usecounts, text, dbid, objectid FROM
sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
WHERE objtype = 'Proc' and dbid=db_id('taobao');
--数据库所有会话情况：
SELECT session_id, login_time, host_name, program_name, host_process_id,
client_version, client_interface_name, security_id, login_name, nt_domain,
nt_user_name, status, context_info, cpu_time, memory_usage, total_scheduled_time,
total_elapsed_time, endpoint_id, last_request_start_time, last_request_end_time,
reads, writes, logical_reads, is_user_process, text_size, language, date_format,
date_first, quoted_identifier, arithabort, ansi_null_dflt_on, ansi_defaults,
ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null,
transaction_isolation_level, lock_timeout, deadlock_priority, row_count, prev_error,
original_security_id, original_login_name, last_successful_logon,
last_unsuccessful_logon, unsuccessful_logons
FROM sys.dm_exec_sessions
--所有请求情况：
SELECT session_id, request_id, start_time, status, command, sql_handle,
statement_start_offset, statement_end_offset, plan_handle, database_id, user_id,
connection_id, blocking_session_id, wait_type, wait_time, last_wait_type,
wait_resource, open_transaction_count, open_resultset_count, transaction_id,
context_info, percent_complete, estimated_completion_time, cpu_time,
total_elapsed_time, scheduler_id, task_address, reads, writes, logical_reads,
text_size, language, date_format, date_first, quoted_identifier, arithabort,
ansi_null_dflt_on, ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls,
concat_null_yields_null, transaction_isolation_level, lock_timeout, deadlock_priority,
row_count, prev_error, nest_level, granted_query_memory,
executing_managed_code
FROM sys.dm_exec_requests
--所有连接情况：
SELECT session_id, most_recent_session_id, connect_time, net_transport, protocol_type,
protocol_version, endpoint_id, encrypt_option, auth_scheme, node_affinity,
num_reads, num_writes, last_read, last_write, net_packet_size, client_net_address,
client_tcp_port, local_net_address, local_tcp_port, connection_id,
parent_connection_id, most_recent_sql_handle
FROM sys.dm_exec_connections
--所有查询情况：
SELECT sql_handle, statement_start_offset, statement_end_offset, plan_generation_num,
plan_handle, creation_time, last_execution_time, execution_count, total_worker_time,
last_worker_time, min_worker_time, max_worker_time, total_physical_reads,
last_physical_reads, min_physical_reads, max_physical_reads, total_logical_writes,
last_logical_writes, min_logical_writes, max_logical_writes, total_logical_reads,
last_logical_reads, min_logical_reads, max_logical_reads, total_clr_time,
last_clr_time, min_clr_time, max_clr_time, total_elapsed_time, last_elapsed_time,
min_elapsed_time, max_elapsed_time
FROM sys.dm_exec_query_stats
--性能统计：
SELECT object_name, counter_name, instance_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters
--索引丢失情况:
SELECT index_handle, database_id, object_id, equality_columns, inequality_columns,
included_columns, statement
FROM sys.dm_db_missing_index_details
WHERE (database_id = DB_ID('taobao'))
--锁详细信息:
SELECT SessionID = s.Session_id,resource_type,DatabaseName = DB_NAME(resource_database_id),
request_mode,request_type,a.text,login_time,host_name,program_name,client_interface_name,
login_name,nt_domain,nt_user_name,s.status, last_request_start_time,
last_request_end_time,s.logical_reads,s.reads,request_status,request_owner_type,objectid,dbid,a.number,
a.encrypted , a.blocking_session_id FROM sys.dm_tran_locks l JOIN sys.dm_exec_sessions s
ON l.request_session_id = s.session_id LEFT JOIN (SELECT * FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle)) a ON s.session_id = a.session_id
WHERE s.session_id > 50
--查询阻塞情况:
SELECT
blocked_query.session_id AS blocked_session_id,
blocking_query.session_id AS blocking_session_id,
sql_text.text AS blocking_text,
waits.wait_type AS blocking_resource
FROM sys.dm_exec_requests blocked_query
JOIN sys.dm_exec_requests blocking_query ON
blocked_query.blocking_session_id = blocking_query.session_id
CROSS APPLY
(
SELECT * FROM sys.dm_exec_sql_text(blocking_query.sql_handle)
) sql_text
JOIN sys.dm_os_waiting_tasks waits ON
waits.session_id = blocking_query.session_id
