DMVs for performance tuning

DMV Categories

Most important DMV Categories for performance tuning are

dm_db_* – General database and index utilization
dm_exec_* – Statistics for queries that are executing, as well as queries that have completed and still have plans in the query cache
dm_io_* – Disk subsystem statistics
dm_os_* – Statistics related to use of hardware resources

Database Statistics
Most common DMVs used for database statistics are

1. sys.dm_db_index_usage_stats – number of times (and the last time) each index was used to satisfy a seek, scan or lookup , as well as the number of time and the last time an update was performed to each index.
2. sys.dm_db_index_operational_stats – this is a function that takes 4 parameters (database_id, object_id, index_id and partition_id). Returns locking, latching and access statistics for each index that can help you determine how heavily an index is being used. Also helps you diagnose contention issues due to locking and latching.
3. sys.dm_db_index_physical_stats – function that takes in 5 parameters – all 4 of above and mode. Returns size and fragmentation statistics for each index and should be the primary source for determining when an index needs to be defragmented.
4. sys.dm_db_missing_index_groups –
5. sys.dm_db_missing_index_group_stats
6. sys.dm_db_missing_index_details

SQL Server logs details of an index miss which can be viewed using sys.dm_db_missing_index_* views (4, 5, 6 above) . Index miss is a situation when an index would be beneficial but doesn’t exist.

Query Statistics
The dm_exec_* views provide information related to connection to an instance as well as query execution.

• dm_exec_connections – one row for each connection to the instance
• dm_exec_sessions – one row for each currently authenticated session- also returns accumulated reads, writes, cpu and query execution duration for session.
• dm_exec_requests – one row for each currently executing request in the instance. Blocking_session_id columns helps to diagnose contention issues.
• dm_exec_query_stats – contains detailed statistics on performance and resources consumed for every query in the query cache.
• dm_exec_sql_text function returns the text of the sql statement associated to the handle that was passed in
• dm_exec_query_plan accepts a plan handle and returns corresponding xml showplan

If you want to see the currently executing queries along with xml plan

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

IF you want to see the sql statement and xml plan for every query in the query 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)

DISK SUBSYSTEM STATISTICS
sys.dm_io_virtual_file_stats – statistics about reads and writes for every database file. IOStalls for both reads and writes can be found here.
sys.dm_io_pending_requests – contains one row for each request that is waiting for the disk subsystem to complete an I/O request.

HARDWARE RESOURCES
When a request is sen to the waiting queue, SQL Server sets a value called teh wait type that designates the type of resources that the rqeuest is waiting on. As soon as wait type is set, SQL Server starts a clock. When the resource becomes avaialble, SQL Server stops the clock and records the amount of time that the request had to wait for the resource to become avialable, called teh wait time. SQL Server also sets a clcok when a request enters the running queue, called teh signal wait, which records how long it takes a process to get to the top of the queue and begin executing.

dm_os_wait_stats lists the aggregate amount of signal wait and wait time for each wait type.

Source :MCTS Training Kit – Mike Hotek