Navigation¶

• Main Page
  

• Random Page
• Create a new Page
• All Pages
• Categories
  

• Administration
• File Management
  

• Login/Logout
• Language Selection
• Your Profile
• Create Account
  

Quick Search
»
Advanced Search »

Dynamic Mangement Views

MSDN Link

There are two types of dynamic management views and functions:

• Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
• Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.
  

Dynamic management views and functions exist in the sys schema and follow this naming convention dm_*. When you use a dynamic management view or function, you must prefix the name of the view or function by using the sys schema.

Table of Contents [Hide/Show]       Database Related          sys.dm_db_file_space_usage          sys.dm_db_partition_stats       Execution related          sys.dm_exec_cached_plans          sys.dm_exec_connections          sys.dm_exec_cursors          sys.dm_exec_query_plan          sys.dm_exec_query_stats          sys.dm_exec_sql_text          sys.dm_exec_text_query_plan       Index Related          sys.dm_db_index_operational_stats          sys.dm_db_index_usage_stats          sys.dm_db_index_physical_stats          sys.dm_db_missing_index_columns          sys.dm_db_missing_index_details       SQL Server OS Related          sys.dm_os_performance_counters          sys.dm_os_sys_info          sys.dm_os_threads          sys.dm_os_wait_stats       Transaction Related          sys.dm_tran_active_transactions          sys.dm_tran_database_transactions          sys.dm_tran_locks

Database Related¶

sys.dm_db_file_space_usage¶

=====sys.dm_db_partition_stats=====

Execution related¶

sys.dm_exec_cached_plans¶

=====sys.dm_exec_connections=====

sys.dm_exec_cursors¶

=====sys.dm_exec_query_plan=====

sys.dm_exec_query_stats¶

=====sys.dm_exec_sql_text=====

sys.dm_exec_text_query_plan¶

Index Related¶

sys.dm_db_index_operational_stats¶

=====sys.dm_db_index_usage_stats=====

sys.dm_db_index_physical_stats¶

=====sys.dm_db_missing_index_columns=====

sys.dm_db_missing_index_details¶

SQL Server OS Related¶

sys.dm_os_performance_counters¶

Excellent! - gets the perfmon counters for given instances:

select *
from sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'SQLServer:Databases'
AND instance_name = 'tempdb'

sys.dm_os_sys_info¶

=====sys.dm_os_threads=====

sys.dm_os_wait_stats¶

Transaction Related¶

sys.dm_tran_active_transactions¶

=====sys.dm_tran_database_transactions=====

sys.dm_tran_locks¶