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