DMV store in the memory, it only capture the runtime information and does not keep the historical data. Once the server bounce, the information will lost and start over.
DMV type
- dm_db_*: Database info and index
- dm_exec_*: runtime activity ,query and query plan
- dm_io_*: IO stats
- dm_os_*: OS and Hardware information
dm_db
sys.dm_db_index_operational_stats
sys.dm_db_index_physical_stats
sys.dm_db_index_usage_stats
sys.dm_db_missing_index_columns
sys.dm_db_missing_index_details
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_groups
Evaluate whether it is need to have the index
- SELECT *
- FROM
- (SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS
- index_advantage, migs.* FROM sys.dm_db_missing_index_group_stats migs) AS migs_adv
- INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs_adv.group_handle = mig.index_group_handle
- INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
- ORDER BY migs_adv.index_advantage
- GO
DM_EXEC
sys.dm_exec_requests
sys.dm_exec_connections
sys.dm_exec_sessions
sys.dm_exec_query_stats
sys.dm_exec_query_plan
To get the current running spid query plan as XML format
- SELECT * FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_query_plan(plan_handle)
- CROSS APPLY sys.dm_exec_sql_text(sql_handle)
To get the Query plan for past query from cache
- SELECT * FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_query_plan(plan_handle)
- CROSS APPLY sys.dm_exec_sql_text(sql_handle)
DM_IO
sys.dm_io_virtual_file_stats
sys.dm_io_pending_io_requests
Get the file size for the giving database
- SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'AdventureWorks2008R2'),NULL);
The number return is in the Page, it needs to times 8192 to covert to bytes.
Get the pending IO session
- select * from sys.dm_io_pending_io_request
DM_OS
sys.dm_os_windows_info
sys.dm_os_wait_stats
sys.dm_os_workers
sys.dm_os_ring_buffers
sys.dm_os_windows_info will show the OS information such as service pack. It is only available after 2008 R2 SP1.
Show the current spid wait stats.
- select * from sys.dm_os_wait_stats
The wait stats is accumulate since last server bounce or manually clean the state. we can use DBCC SQLPER to clean the wait state
- DBCC SQLPERF (WAITSTATS,CLEAR)
Show the current worker process and how long they have being running
- SELECT
- t1.session_id,
- CONVERT(varchar(10), t1.status) AS status,
- CONVERT(varchar(15), t1.command) AS command,
- CONVERT(varchar(10), t2.state) AS worker_state,
- w_suspended =
- CASE t2.wait_started_ms_ticks
- WHEN 0 THEN 0
- ELSE
- t3.ms_ticks - t2.wait_started_ms_ticks
- END,
- w_runnable =
- CASE t2.wait_resumed_ms_ticks
- WHEN 0 THEN 0
- ELSE
- t3.ms_ticks - t2.wait_resumed_ms_ticks
- END
- FROM sys.dm_exec_requests AS t1
- INNER JOIN sys.dm_os_workers AS t2
- ON t2.task_address = t1.task_address
- CROSS JOIN sys.dm_os_sys_info AS t3
- WHERE t1.scheduler_id IS NOT NULL;
Show the any OS feed back information
- select * from sys.dm_os_ring_buffers
0 comments:
Post a Comment