Usefull DMV Query

  • Article

What are DMV : (Dynamic Management View)

  • View of internals informations.
  • DMV are basically SQL Views on some pretty important internal memory structures.
  • DMV informations include metrics about :
    • indexes
    • query execution
    • operating system
    • Transaction
    • Databases
    • and much more

What are DMF : (Dynamic Management Function)

As an example sys.dm_exec_query_stats records details of the SQL being processed via variable called sql_handle.

If this sql_handle is passed as a parameter to the DMF sys.dm_exec_sql_text, the DMF will return the actual SQL Text  associated with this sql_handle.

 

Find a cache plan :

Select st.Text as [SQL],
cp.cacheobjtype,
cp, objtype,
COASLESCE (Cast(pa.value, INT)), 'Resource' as [DatabaseName],
cp.usecounts as [Plan Usage],
qp.query_plan
From sys.dm_exec_cached_plan cp
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) qp
OUTER APPLY sys.dm_exec_plan_attributes (cp.plan_handle) pa
Where pa.attribute = 'dbid'

 

Most Expensive Query :

Select Cast(Total_elapsed_time / 1000000.0 as decimal(28,2)) as [Total Duration (s)],
Cast(Total_Worker_Time * 100.0 / Total_Elapsed_Time as decimal(28, 2)) as [% CPU],
Cast ((total_elapsed_time - total_worker_time) * 100.0 / total_elapsed_time as decimal(28,2)) as [% Waiting],
execution_count,
Cast (Total_elapsed_time / 1000000.0 / execution_count as decimal(28,2)) as [average duration (s)],
Substring (qt.text, (qs.statement_start_offset / 2) + 1, ((
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 ) + 1) as [Individual Query]
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) qp
INNER JOIN sys.dm_exec_cached_plans as cp
ON qs.plan_handle = cp.plan_handle
Where Total_elapsed_Time <> 0
and Execution_count <> 0
Order By total_elapsed_time DESC

 

 Missing Index :

 Select Round (avg_total_user_cost * avg_user_impact * (user_seeks + user_scans), 0) as [Total Cost],
statement as [Table Name],
equality_columns
From sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s on s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d on d.index_handle = g.index_handle

 

Why are you waiting ?

Select Wait_type, wait_time_ms, signal_wait_time_ms, wait_time_ms -signal_wait_time_ms as RealWait,
convert(Decimal(12, 2), (wait_time_ms - signal_wait_time_ms) * 100.0 / sum(wait_time_ms) over()) as [% RealWait]
From sys.dm_os_wait_stats
Where Wait_type like '%SLEEP%'
And wait_type != 'WAITFOR'
Order by wait_time_ms DESC

 

What is blocked ?

Select Blocking.Session_id as BlockingSessionId,
Sess.login_name as BlockingUser,
BlockingSQL.Text as BlockingSQL,
Waits.wait_type as WhyBlocked,
Blocked.Session_id as BlockedSessionId,
USER_NAME(Blocked.user_id) as BlockedUser,
BlockedSQL.text as BlockedSQL,
DB_NAME(Blocked.database_id) as DatabaseName
From Sys.dm_exec_connections as Blocking
INNER JOIN sys.dm_exec_requests as Blocked
ON Blocking.session_id = blocked.blocking_sessionid
INNER JOIN sys.dm_os_waiting_task as waits
ON Blocked.session_id = Waits.session_id
RIGHT OUTER JOIN sys.dm_exec_sessions Sess
ON Blocking.session_id = sess.session_id
CROSS APPLY sys.dm_exec_sql_text (Blocking.most_recent_sql_handle) as Blocking SQL
CROSS APPLY sys.dm_exec_sql_text (Blocked.sql_handle) as BlockedSQL

 

What Fill the log ?

 Select db.name as [Database Name],
db.recovery_model_desc as [Recovery Model],
db.log_reuse_wait_desc as [Log reuse Wait Description],
ls.cntr_value as [Log Size (KB)],
lu.cntr_value as [log Used (KB)],
CAST ( CAST(lu.cntr_value as float) / CAST(ls.cntr_value as float) as DECIMAL (18,2)) * 100 as [Log Used %],
db.Compatibility_level as [DB Compatibility Level],
db.page_verify_option_desc as [Page Verify_Option]
From sys.databases as db
INNER JOIN sys.dm_os_performance_counters as lu on db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters as ls on db.name = ls.instance_name
Where lu.counter_name like 'Log File(s) Used Size (KB)%'
And ls.counter_name like 'Log File(s) Size (KB)%'

 

System Information :

Select cpu_count as [Logical CPU Count],
hyperthread_ratio as [ Hyperthread Ratio],
cpu_count /hyperthread_ratio as [Physical CPU Count],
physical_memory_in_bytes / 1048576 as [Physical Memory (MB)]
From sys.dm_os_sys_info

 

Monitor Scheduler info :

SELECT AVG(current_tasks_count) AS [Avg Task Count] ,
AVG(runnable_tasks_count) AS [Avg Runnable Task Count]
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
AND [status] = 'VISIBLE ONLINE'

Monitor Memory :

SELECT total_physical_memory_kb ,
available_physical_memory_kb ,
total_page_file_kb ,
available_page_file_kb ,
system_memory_state_desc
FROM sys.dm_os_sys_memory

SELECT physical_memory_in_use_kb ,
locked_page_allocations_kb ,
page_fault_count ,
memory_utilization_percentage ,
available_commit_limit_kb ,
process_physical_memory_low ,
process_virtual_memory_low
FROM sys.dm_os_process_memory

SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName] ,
p.index_id ,
COUNT(*) / 128 AS [Buffer size(MB)] ,
COUNT(*) AS [Buffer_count]
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors
AS b ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p ON a.container_id = p.hobt_id
WHERE b.database_id = DB_ID()
AND p.[object_id] > 100
GROUP BY p.[object_id] ,
p.index_id
ORDER BY buffer_count DESC