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,
sqlserver_start_time
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.
SELECT TOP (5)
(total_logical_reads +
total_logical_writes) AS
total_logical_IO,
execution_count,
(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,
I.name 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,
O.*
INTO dbo.NewOrders
FROM Sales.Orders AS O
CROSS JOIN (VALUES(1),(2),(3)) AS N1(n);
GO
CREATE NONCLUSTERED INDEX idx_nc_orderid
ON dbo.NewOrders(orderid);
GO
SELECT norderid
FROM dbo.NewOrders
WHERE norderid = 110248
ORDER BY norderid;
GO
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
