Home > sql > SQL Server DMV’s

SQL Server DMV’s

I have been focused a good bit lately into diving into the DMV’s to make my job easier.  Below are some useful ones that I use on a frequent basis.

Dymanic Management Views
sys.dm_exec_sessions (Current sessions)
sys.dm_exec_connections (Current Connections)
sys.dm_exec_requests (Currently executing requests)
sys.dm_exec_query_stats (previously executed SQL that exists in Plan Cache)
sys.dm_exec_cached_plans (Plan Cache)

Dynamic Management Functions
sys.dm_exec_sql_text (Extracts readable SQL from the sql_handle
sys.dm_exec_query_plan (Extracts XML execution plan using plan_handle)

Here are a few examples how these can be used together

--Find Procedures consuming the most logical reads
SELECT 
    DB_NAME(st.dbid) DBName,
    OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName,
    OBJECT_NAME(objectid,st.dbid) StoredProcedure,
    max(cp.usecounts) execution_count,
    sum(qs.total_physical_reads) total_physical_reads,
    sum(qs.total_physical_reads) / max(cp.usecounts) avg_physical_reads,
    sum(qs.total_logical_reads) total_logical_reads,
    sum(qs.total_logical_reads) / max(cp.usecounts) avg_logical_reads
FROM sys.dm_exec_query_stats qs 
    CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
    INNER JOIN sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
WHERE DB_NAME(st.dbid) is not null 
and cp.objtype = 'proc'
GROUP BY DB_NAME(st.dbid),
        OBJECT_SCHEMA_NAME(objectid,st.dbid), 
        OBJECT_NAME(objectid,st.dbid)
ORDER BY sum(qs.total_logical_reads) / max(cp.usecounts) desc
 
--Find reusability of plan cache (high usecounts)
SELECT 
    c.cacheobjtype , 
    c.objtype , 
    c.usecounts , 
    c.size_in_bytes , 
    t.dbid , 
    t.text 
FROM sys.dm_exec_cached_plans as c
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) as t
WHERE c.cacheobjtype = 'Compiled Plan'
ORDER BY c.usecounts DESC
Advertisements
Categories: sql
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: