1 Performance Tuning: DMV 101

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

1. dm_db_*: Database info and index
2. dm_exec_*: runtime activity ,query and query plan
3. dm_io_*: IO stats
4. 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

1. SELECT *
2. FROM
3. (SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS
4. index_advantage, migs.* FROM sys.dm_db_missing_index_group_stats migs) AS migs_adv
5. INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs_adv.group_handle = mig.index_group_handle
6. INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
7. ORDER BY migs_adv.index_advantage
8. 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

1. SELECT * FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_query_plan(plan_handle)
2.     CROSS APPLY sys.dm_exec_sql_text(sql_handle)

To get the Query plan for past query from cache

1. SELECT * FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_query_plan(plan_handle)
2.     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

1. 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

1. 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.

1. 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

1. DBCC SQLPERF (WAITSTATS,CLEAR)

Show the current worker process and how long they have being running

1. SELECT
2.     t1.session_id,
3.     CONVERT(varchar(10), t1.status) AS status,
4.     CONVERT(varchar(15), t1.command) AS command,
5.     CONVERT(varchar(10), t2.state) AS worker_state,
6.     w_suspended =
7.       CASE t2.wait_started_ms_ticks
8.         WHEN 0 THEN 0
9.         ELSE
10.           t3.ms_ticks - t2.wait_started_ms_ticks
11.       END,
12.     w_runnable =
13.       CASE t2.wait_resumed_ms_ticks
14.         WHEN 0 THEN 0
15.         ELSE
16.           t3.ms_ticks - t2.wait_resumed_ms_ticks
17.       END
18.   FROM sys.dm_exec_requests AS t1
19.   INNER JOIN sys.dm_os_workers AS t2
20.     ON t2.task_address = t1.task_address
21.   CROSS JOIN sys.dm_os_sys_info AS t3
22.   WHERE t1.scheduler_id IS NOT NULL;

Show the any OS feed back information

1.   select * from sys.dm_os_ring_buffers