sql server 2005的一些動態函式

所有物件：
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 bjtype = '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

原文：http://www.cnblogs.com/quange/archive/2009/05/18/1459272.html