Dynamic Management Views and Functions

Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

Dynamic management views and functions return internal, implementation-specific state data. Their schemas and the data they return may change in future releases of SQL Server. Therefore, dynamic management views and functions in future releases may not be compatible with the dynamic management views and functions in SQL Server 2008.

There are two types of dynamic management views and functions:

• Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
• Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.

Most Important DMVs

1)  sys.dm_exec_sessions and sys.dm_exec_requests. These two combine effectively as the replacement of sysprocesses

2)  sys.dm_exec_query_stats. Current performance statistics for cached objects and queries

3)  sys.dm_os_wait_stats. Find out where the waits are occuring in the engine. Replaces DBCC SQLPERF('WAITSTATS')

4)  sys.dm_db_missing_index_details. See what indexes could have helped performance of previous queries. A must to look at for any

   server that is having performance problems

5)  sys.dm_exec_sql_text. A dynamic management function to get the text of a SQL query based on its SQL handle

6)  sys.dm_os_ring_buffers  - for advanced use

TIP:

To “join” the use of a function like sys.dm_exec_sql_text you need to use the CROSS APPLY operator.

For example, here is a query to get the “input buffer” for sessions found in sys.dm_exec_requests:

Select session_id, s2.text from sys.dm_exec_requests

Cross apply sys.dm_exec_sql_text(sql_handle) s2

Don’t run this query in the context of a database in a compatibility mode < 90. It will fail because the APPLY operator is new to SQL 2005

The following query allows you to identify what SQL statements are currently executing. This information can be useful in debugging the cause of both long running queries and blocking, and also showing the execution progress of a stored procedure or batch of SQL.

SELECT [Spid] = session_Id

            , ecid

            , [Database] = DB_NAME(sp.dbid)

            , [User] = nt_username

            , [Status] = er.status

            , [Wait] = wait_type

            , [Individual Query] = SUBSTRING (qt.text, er.statement_start_offset/2,

                   (CASE WHEN er.statement_end_offset = -1

                        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

                    ELSE er.statement_end_offset END - er.statement_start_offset)/2)

            ,[Parent Query] = qt.text

            , Program = program_name

            , Hostname

            , nt_domain

            , start_time

      FROM sys.dm_exec_requests er

      INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid

      CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as qt

      WHERE session_Id > 50               -- Ignore system spids.

      AND session_Id NOT IN (@@SPID)      -- Ignore this current statement.

      ORDER BY 1, 2

Column name	Data type	Description
spid	smallint	SQL Server process ID.
ecid	smallint	Execution context ID used to uniquely identify the subthreads operating on behalf of a single process.
dbid	smallint	ID of the database currently being used by the process.
nt_username	nchar(128)	Windows user name for the process, if using Windows Authentication, or a trusted connection.
status	nchar(30)	Process ID status. For example, running and sleeping.
wait_type	bigint	Current wait time in milliseconds.
Individual Query	varchar	SQL Statement currently running.
Parent Query	varchar	Routine that contains the Individual Query.
program_name	nchar(128)	Name of the application program.
Hostname	nchar(128)	Name of the workstation.
nt_domain	nchar(128)	Microsoft Windows domain for the client, if using Windows Authentication, or a trusted connection.
Start_time	datetime	Time when the request is scheduled to run.

Yet we often hear confusion about their use.  People ask, " there are so many of them—which are the most important ones?  Where do I begin?"

You can begin right here.

I have drafted a team of five first-round picks which comprise DMV All-Stars Dream Team. They are simple to use & provide a rich source of useful information.

The chosen queries provide information on the following topics:

·         Expensive Queries (CPU, reads, frequency, etc.)

·         Wait Stats

·         Virtual File Stats (including calculations for virtual file latency)

·         Plan Cache

·         Blocking (real-time)

I have provided backward-compatible versions for three of them. The first has no equivalent in SQL Server 2000.  The fifth has an equivalent, but would require a big post all its own.

These are among the most frequent DMVs I use—that's why I put them in one place.  I hope you find them helpful as well.  There’s not much unique in what I’ve provided (though I do have a surprise for a forthcoming post).  In fact, you're likely to recognize all of them.  Some of them I've liberated right out of BOL (2005 2008).  (And sh-h-h-h—don't tell anyone but I've copied-&-pasted one directly from the SQL Server Customer Advisory Team's (SQL CAT) Best Practices Toolbox).  Yet, the point is, ladies & gents, goyles & geeks, you have here in one place many of the DMVs you’ll need for much of your routine work.

There were many worthy queries which I have not included.  Perhaps someday I’ll draft a team of bench warmers which might include, e.g., DMVs for fragmentation.  If you don’t like my picks, nominate DMVs for your own  team.

--<<<<<<<<<<----------------------------------------------------------------->>>>>>>>>>--

--DMV_All-Stars.sql

--Table of Contents

--1. expensive queries

--2. wait stats

--3. virtual file stats (& virtual file latency)

--4. plan cache interrogation

--5. real-time blockers

--<<<<<<<<<<----------------------------------------------------------------->>>>>>>>>>--

--Weasel Clause: This script is provided "AS IS" with no warranties, and confers no rights.

--  Use of included script samples are subject to the terms specified at

--  http://www.microsoft.com/info/cpyright.htm

--<<<<<<<<<<----------------------------------------------------------------->>>>>>>>>>--

--1. expensive queries

    --text *and* statement

    --usage: modify WHERE & ORDER BY clauses to suit circumstances

SELECT TOP 25

      -- the following four columns are NULL for ad hoc and prepared batches

      DB_Name(qp.dbid) as dbname , qp.dbid , qp.objectid , qp.number

    --, qp.query_plan --the query plan can be *very* useful; enable if desired

    , qt.text

    , SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,

        ((CASE statement_end_offset

            WHEN -1 THEN DATALENGTH(qt.text)

            ELSE qs.statement_end_offset END

                - qs.statement_start_offset)/2) + 1) as statement_text

    , qs.creation_time , qs.last_execution_time , qs.execution_count

    , qs.total_worker_time    / qs.execution_count as avg_worker_time

    , qs.total_physical_reads / qs.execution_count as avg_physical_reads

    , qs.total_logical_reads  / qs.execution_count as avg_logical_reads

    , qs.total_logical_writes / qs.execution_count as avg_logical_writes

    , qs.total_elapsed_time   / qs.execution_count as avg_elapsed_time

    , qs.total_clr_time       / qs.execution_count as avg_clr_time

    , qs.total_worker_time , qs.last_worker_time , qs.min_worker_time , qs.max_worker_time

    , qs.total_physical_reads , qs.last_physical_reads , qs.min_physical_reads , qs.max_physical_reads

    , qs.total_logical_reads , qs.last_logical_reads , qs.min_logical_reads , qs.max_logical_reads

    , qs.total_logical_writes , qs.last_logical_writes , qs.min_logical_writes , qs.max_logical_writes

    , qs.total_elapsed_time , qs.last_elapsed_time , qs.min_elapsed_time , qs.max_elapsed_time

    , qs.total_clr_time , qs.last_clr_time , qs.min_clr_time , qs.max_clr_time

    --, qs.sql_handle , qs.statement_start_offset , qs.statement_end_offset

    , qs.plan_generation_num  -- , qp.encrypted

    FROM sys.dm_exec_query_stats as qs

    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

    --WHERE...

    --ORDER BY qs.execution_count      DESC  --Frequency

      ORDER BY qs.total_worker_time    DESC  --CPU

    --ORDER BY qs.total_elapsed_time   DESC  --Durn

    --ORDER BY qs.total_logical_reads  DESC  --Reads

    --ORDER BY qs.total_logical_writes DESC  --Writes

    --ORDER BY qs.total_physical_reads DESC  --PhysicalReads   

    --ORDER BY avg_worker_time         DESC  --AvgCPU

    --ORDER BY avg_elapsed_time        DESC  --AvgDurn    

    --ORDER BY avg_logical_reads       DESC  --AvgReads

    --ORDER BY avg_logical_writes      DESC  --AvgWrites

    --ORDER BY avg_physical_reads      DESC  --AvgPhysicalReads

    --sample WHERE clauses

    --WHERE last_execution_time > '20070507 15:00'

    --WHERE execution_count = 1

    --  WHERE SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,

    --    ((CASE statement_end_offset

    --        WHEN -1 THEN DATALENGTH(qt.text)

    --        ELSE qs.statement_end_offset END

    --            - qs.statement_start_offset)/2) + 1)

    --      LIKE '%MyText%'

--<<<<<<<<<<----------------------------------------------------------------->>>>>>>>>>--

--2. wait stats

--2000

--DBCC sqlperf(waitstats,CLEAR)               --re-initialize waitstats

DBCC sqlperf(waitstats)

--2005

--DBCC sqlperf('sys.dm_os_wait_stats',CLEAR)  --re-initialize waitstats

SELECT * , (wait_time_ms - signal_wait_time_ms) as resource_wait_time_ms

    FROM sys.dm_os_wait_stats

    ORDER BY resource_wait_time_ms DESC

    --ORDER BY wait_time_ms DESC

    --ORDER BY signal_wait_time_ms DESC

    --ORDER BY waiting_tasks_count DESC

    --ORDER BY max_wait_time_ms DESC

--<<<<<<<<<<----------------------------------------------------------------->>>>>>>>>>--

--3. virtual file stats

--2000

SELECT DB_NAME(DBID) as DbName

    , (IoStallMS/(NumberReads + NumberWrites)) as IsStall

    , * FROM ::fn_virtualfilestats(-1,-1)

--2005

SELECT

    -- virtual file latency calcs

    IsReadStall

        = CASE WHEN num_of_reads = 0

               THEN 0 ELSE (io_stall_read_ms/num_of_reads) END

  , IsWriteStall

       = CASE WHEN io_stall_write_ms = 0

              THEN 0 ELSE (io_stall_write_ms/num_of_writes) END

  , IsStall

      = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)

             THEN 0 ELSE (io_stall/(num_of_reads + num_of_writes)) END

  , LEFT(mf.physical_name,2) as Drive

  , DB_NAME(vfs.database_id) as DB

  --, mf.name AS FileName

  , vfs.*

  , mf.physical_name

  FROM sys.dm_io_virtual_file_stats(NULL,NULL) as vfs

    JOIN sys.master_files as mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id

  --WHERE vfs.file_id = 2 -- log files

  ORDER BY IsStall DESC

  -- ORDER BY IsReadStall DESC

  -- ORDER BY IsWriteStall DESC

--<<<<<<<<<<----------------------------------------------------------------->>>>>>>>>>--

--4. plan cache interrogation

-- note: sys.dm_exec_cached_plans is diminutive version of syscacheobjects

    -- no dbid, setopts

-- we want reusable code, absence of ad hoc SQL

-- we want relatively few rows with low usecounts

--2000  

SELECT cacheobjtype , objtype , usecounts , pagesused , dbid , sql

    FROM master.dbo.syscacheobjects

    WHERE cacheobjtype = 'Compiled Plan'

    ORDER BY usecounts DESC

    --ORDER BY sql

--2005

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

    --ORDER BY t.text

--<<<<<<<<<<----------------------------------------------------------------->>>>>>>>>>--

--5. real-time blockers

    --Report Blocker and Waiter SQL Statements

    --http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/trans/sql05vb044.mspx?mfr=true

    -- SQLCAT BPT

SELECT

    t1.resource_type as lock_type

  , db_name(resource_database_id) as DB

  , t1.resource_associated_entity_id as blkd_obj

  , t1.request_mode as lock_req          -- lock requested

  , t1.request_session_id as waiter_sid-- spid of waiter

  , t2.wait_duration_ms as waittime

  , (SELECT text FROM sys.dm_exec_requests as r  --- get sql for waiter

        CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)

            WHERE r.session_id = t1.request_session_id) as waiter_batch

  , (SELECT SUBSTRING(qt.text , r.statement_start_offset/2

          , (CASE WHEN r.statement_end_offset = -1

                THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2

                ELSE r.statement_end_offset END - r.statement_start_offset)/2)

        FROM sys.dm_exec_requests as r

            CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as qt

            WHERE r.session_id = t1.request_session_id) as waiter_stmt    --- this is the statement executing right now

   , t2.blocking_session_id as blocker_sid -- spid of blocker

   , (SELECT text FROM sys.sysprocesses as p       --- get sql for blocker

            CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)

            WHERE p.spid = t2.blocking_session_id) as blocker_stmt

FROM sys.dm_tran_locks as t1

    JOIN sys.dm_os_waiting_tasks as t2

        ON t1.lock_owner_address = t2.resource_address

Script Repository: SQL Server 2005

http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/default.mspx?mfr=true