Performance Tune-up with DMVs

Imagine that you get complaints from end users about SQL Server performance. You have to start investigating the problem immediately. How would you start? 

In a production system, end users can submit thousands of queries per hour. Which query would you analyze first?

You could try to start an Extended Events monitoring session. You could use SQL Trace. In both cases, you would have to wait for quite a while before you gather enough data to start analysis and find the most problematic queries. And what if the problematic queries are not executed soon again after you start your monitoring session? You could only hope that you would be able to catch the problems in a reasonable time.

This is the point at which DMOs become extremely helpful. With DMOs, a lot of the data that you need is already gathered. All you need to do is query appropriate DMOs with regular T-SQL queries and extract useful information. DMOs are not materialized in any database; DMOs are virtual objects that give you access to the data SQL Server collects in memory. 

Although DMOs are really useful, they have some drawbacks. The most important issue you should take care of is when the last restart of the instance you are inspecting occurred.

Cumulative information is useless if the instance was restarted recently.
You can start an analyzing session by gathering some system information about your instance, as the following query shows.

SELECT cpu_count AS logical_cpu_count,
cpu_count / hyperthread_ratio AS physical_cpu_count,
CAST(physical_memory_kb / 1024. AS int) AS physical_memory__mb,
FROM sys.dm_os_sys_info;

The SQLOS-related sys.dm_os_waiting_tasks DMO gives you information about sessions that are currently waiting on something. For example, the sessions could be blocked by another session because of locking. You can join this DMO to the execution-related sys.dm_exec_sessions DMO to get information about the user, host, and application that are waiting.

You can also use the is_user_process flag from the sys.dm_exec_sessions DMO to filter out system sessions. The following query gives this information.

SELECT S.login_name, S.host_name, S.program_name,
WT.session_id, WT.wait_duration_ms, WT.wait_type,
WT.blocking_session_id, WT.resource_description
FROM sys.dm_os_waiting_tasks AS WT
INNER JOIN sys.dm_exec_sessions AS S
ON WT.session_id = S.session_id
WHERE s.is_user_process = 1;

The sys.dm_exec_requests execution-related DMO returns information about currently executing requests. It includes a column called sql_handle, which is a hash map of the T-SQL batch text that is executed. You can use this handle to retrieve the complete text of the batch with the help of the execution-related sys.dm_exec_sql_text dynamic management function that accepts this handle as a parameter. The following query joins information about current requests, their waits, and text of their SQL batch with the sys.dm_exec_sessions dynamic management view to also get user, host, and application info.

SELECT S.login_name, S.host_name, S.program_name,
R.command, T.text,
R.wait_type, R.wait_time, R.blocking_session_id
FROM sys.dm_exec_requests AS R
INNER JOIN sys.dm_exec_sessions AS S
ON R.session_id = S.session_id
OUTER APPLY sys.dm_exec_sql_text(R.sql_handle) AS T
WHERE S.is_user_process = 1;

You can retrieve a lot of information about executed queries from the execution-related sys.dm_exec_query_stats DMO. You can retrieve information about disk IO per query, CPU consumption per query, elapsed time per query, and more. With the help of the sys.dm_exec_sql_text DMO, you can retrieve the text of the query as well. You can extract specific query text from batch text with the help of the statement_start_offset and statement_end_offset columns from the sys.dm_exec_query_stats DMO. The extraction is somewhat tricky. The
following query lists five queries that used the most logical disk IO with their query text extracted from the batch text.

(total_logical_reads + total_logical_writes) AS total_logical_IO,
(total_logical_reads/execution_count) AS avg_logical_reads,
(total_logical_writes/execution_count) AS avg_logical_writes,
(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX),text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY (total_logical_reads + total_logical_writes) DESC;

Find nonclustered indexes that were not used from the last start of the instance by using the following query. It is important is to have a representative sample of queries collected before using DMOs that return cumulative values.

SELECT OBJECT_NAME(I.object_id) AS objectname, AS indexname,
I.index_id AS indexid
FROM sys.indexes AS I
INNER JOIN sys.objects AS O
ON O.object_id = I.object_id
WHERE I.object_id > 100
AND I.type_desc = 'NONCLUSTERED'
AND I.index_id NOT IN
(SELECT S.index_id
FROM sys.dm_db_index_usage_stats AS S
WHERE S.object_id=I.object_id
AND I.index_id=S.index_id
AND database_id = DB_ID('TSQL2012'))
ORDER BY objectname, indexname;

In the following exercise, you find missing indexes.

1.  Quickly create the table and the index on that table from the practice for the previous lesson, but use 10 times less data. Then index it and execute the query that could benefit from an additional index. Here is the code.

SELECT N1.n * 100000 + O.orderid AS norderid,
INTO dbo.NewOrders
FROM Sales.Orders AS O
CROSS JOIN (VALUES(1),(2),(3)) AS N1(n);
ON dbo.NewOrders(orderid);
SELECT norderid
FROM dbo.NewOrders
WHERE norderid = 110248
ORDER BY norderid;

2.  Find missing indexes by using index-related DMOs. Use the following query.

SELECT MID.statement AS [Database.Schema.Table],
MIC.column_id AS ColumnId,
MIC.column_name AS ColumnName,
MIC.column_usage AS ColumnUsage,
MIGS.user_seeks AS UserSeeks,
MIGS.user_scans AS UserScans,
MIGS.last_user_seek AS LastUserSeek,
MIGS.avg_total_user_cost AS AvgQueryCostReduction,
MIGS.avg_user_impact AS AvgPctBenefit
FROM sys.dm_db_missing_index_details AS MID
CROSS APPLY sys.dm_db_missing_index_columns (MID.index_handle) AS MIC
INNER JOIN sys.dm_db_missing_index_groups AS MIG
ON MIG.index_handle = MID.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats AS MIGS
ON MIG.index_group_handle=MIGS.group_handle
ORDER BY MIGS.avg_user_impact DESC;

(Source: Training Kit (Exam 70-461) Querying Microsoft SQL Server 2012 (MCSA) (Microsoft Press Training Kit) by Dejan SarkaItzik Ben-Gan, Ron Talmage)