SQL Server Performance Using DMVs

SQL Server Performance using Dynamic management Views Part 1

1 Introduction

1.1 What are DMVs ?

Dynamic managment views (DMVs) are introduced in SQL Server 2005 onwards. They are Views and System functions built on top of internal structures.DMVs are handy to troubleshoot performance issue when there is no option of using profiler since troubleshooting with the aid of profiler will take lot of time as well as they take more system resources than DMVs. DMVs need only “View server state / View database state privilege. A correct DMV query can find potential performance bottleneck online in short time. This article illustrates using DMVs how best we can find the following informations

1.2 Types of DMVs

DMVs are in Sys schema stats with dm_* and the following are the types of DMVs

• dm_exec_*
• Execution of user code and associated connections
• dm_os_*
• Memory, locking & scheduling
• dm_tran_*
• Transactions & isolation
• dm_io_*
• I/O on network and disks
• dm_db_*
• Databases and database objects

The following query will list all DMVs

SELECT * FROM sys.all_objects
  WHERE [name] LIKE '%dm_%'
        AND [type] IN ('V', 'TF', 'IF')
ORDER BY [name]

1.3 What are things we can get from DMVs

Using DMVs we can find the following information\

• Index Tuning / Statistics
• Identify top resource (IO, CPU, etc...) consuming queries
• Wait Statistics
• Locking & blocking
• Perfmon
• TempDB
• I/O
• Memory

2.0 Current Execution Requests

Using Sys.dm_exec_Requests we can find the queries running currently. The following information can be obtained

• Execution of user code and associated connections
• Useful in identifying blocking sessions , resource wait time.

using the below query we can find the resources which are blocing each other

Select Blocking_Session_ID,Session_Id,Wait_Time,sql_handle from sys.dm_exec_requests
Where Wait_Time > 0

Exact query can be retrieved by dbcc inputbuffer (blocking_session_id) Or sys.dm_exec_sql_text (TF).
By combining with sys.dm_tran_locks,sys.dm_os_waiting_tasks we canfind Blockers and waiters (blocking and waiting tasks) .

The following query can be used to find all blockers are waiters

WITH blocking_info AS
(

SELECT

[blocker] = wait.blocking_session_id,

[waiter] = lock.request_session_id,

b_handle = br.[sql_handle],

w_handle = wr.[sql_handle],

[dbid] = lock.resource_database_id,

duration = wait.wait_duration_ms / 1000,

lock_type = lock.resource_type,

lock_mode = block.request_mode

FROM

sys.dm_tran_locks AS lock

INNER JOIN

sys.dm_os_waiting_tasks AS wait

ON lock.lock_owner_address = wait.resource_address

INNER JOIN

sys.dm_exec_requests AS br

ON wait.blocking_session_id = br.blocking_session_

INNER JOIN

sys.dm_exec_requests AS wr

ON lock.request_session_id = wr.session_id

INNER JOIN

sys.dm_tran_locks AS block

ON block.request_session_id = br.session_id

WHERE

block.request_owner_type = 'TRANSACTION'

)

SELECT
[database] = DB_NAME(bi.[dbid]),
bi.blocker,
blocker_command = bt.[text],
bi.waiter,
waiter_command = wt.[text],
[duration MM:SS] = RTRIM(bi.duration / 60) + ':'
+ RIGHT('0' + RTRIM(bi.duration % 60), 2),
bi.lock_type,
bi.lock_mode
FROM
blocking_info AS bi
CROSS APPLY
sys.dm_exec_sql_text(bi.b_handle) AS bt
CROSS APPLY
sys.dm_exec_sql_text(bi.b_handle) AS wt;

3.0 Top Consumers

The following query gives top resource intensive and resource consuming queries at a given time

SELECT TOP 50
qs.total_worker_time/qs.execution_count as [Avg CPU Time],
SUBSTRING(qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end -qs.statement_start_offset)/2)
as query_text,
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
cross apply sys.dm_exec_query_plan(qs.plan_handle) as qp
ORDER BY
[Avg CPU Time] DESC

4.0 Causes for the High CPU or I/O

The following are the root cause for high CPU utilization and high I/O

• Excessive compilation/recompilation

1. Query plan cannot be reused due to changes in:Schema, statistics, set options, temp table, WITH RECOMPILE declaration

• Inefficient query plan

1. Use DTA to check for index recommendations
2. Check for bad cardinality estimates (Actual vs. Estimated)
3. Use restrictive WHERE
4. Ensure database statistics are keep up to date
5. Watch for query constructs that may be a problem for cardinality estimate

• Consider using query hints (last resort)

1. OPTIMIZE FOR – particular parameter values for optimization
2. FORCE ORDER – preserves join order
3. USE PLAN – plan forcing

• Intra-query parallelism

1. Identify parallel queries and make sure they are efficient

4.0 Index usage

The following DMVs are useful in findign and anlysing index usage

• Index Usage and Unused Indexes
• Sys.dm_db_index_usage_stats
• Index Access, Blocks, Contention e.g. waits
• Sys.dm_db_index_operational_stats()
• Sys.dm_db_index_physical_stats()
• Missing indexes
• Sys.dm_db_missing_index_group_stats
• Sys.dm_db_missing_index_groups
• Sys.dm_db_missing_index_details
• Sys.dm_exec_query_plan(plan_handle) - Look for

Having index won’t help always some indexes may not be used at all. When indexes are not used by Query they are overheads as they slow down inserts / updates. The following query used to find the index usage

declare @dbid int
select @dbid = db_id()
select objectname=object_name(s.object_id), s.object_id, indexname=i.name, i.index_id
, user_seeks, user_scans, user_lookups, user_updates
from sys.dm_db_index_usage_stats s,
sys.indexes i
where database_id = @dbid and objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by (user_seeks + user_scans + user_lookups + user_updates) asc

4.1 Best practices for Index

• Avoid long (or wide) custered index key if table has nonclustered (N/C) indexes
• Leaf of Nonclustered index uses the clustered index key (primary key) to locate the data row,Since a wide clustered index key increases size of N/C, (covered) nonclustered range scans results in more IO

• Clustered index benefits

• high volume lookups (avoids RID lookups)
• Range scans – access to entire data row
• Non-clustered index benefits
• Query covering
• Can be used to avoid sorts

... We will dwell into more in Part2..

Please write your comments to improve this blog.