SQL Server queries with DMVs for examining bottlenecks

Here, you'll find queries incorporating Dynamic Management Views (DMVs) that display information on some of the common SQL Server performance bottlenecks.

Queries using DMVs

Here are some queries incorporating DMVs that display information on some of the bottlenecks. Most of these DMV queries come from Troubleshooting performance problems on SQL Server 2005:

  • Memory. If you use the DMVs for memory, check how SQL Server internally allocates its memory. Run DBCC PROCCACHE to see how the total number of allocated buffers (num proc buffs) compares with the number used (num proc buffs used). A high-value percentage indicates poor use of procedure cache.

    Run DBCC MEMORYSTATUS and observe the values for buffer distribution table. If the number of targeted pages decreases over time, it is likely that your SQL Server is experiencing external memory pressure. Compare the number of targeted pages against the stolen pages. If the number of stolen pages does not stabilize over time, the server may eventually get into internal physical memory pressure. Here is another SQL Server 2005 query that will expose these same counters:

  • SELECT * FROM SYS.SYSPERFINFO WHERE
    OBJECT_NAME='SQLSERVER:BUFFER MANAGER' AND
    (COUNTER_NAME='TARGET PAGES' OR
    COUNTER_NAME='TOTAL PAGES' OR
    COUNTER_NAME='DATABASE PAGES' OR
    COUNTER_NAME='STOLEN PAGES' OR
    COUNTER_NAME='FREE PAGES')

    Use the following DMV query to determine which SQL Server components are consuming the most amount of memory, and observe how this changes over time:

    SELECT TYPE, SUM(MULTI_PAGES_KB) FROM
    SYS.DM_OS_MEMORY_CLERKS WHERE
    MULTI_PAGES_KB != 0 GROUP BY TYPE

    This query will show which SQL Server objects are consuming memory:

    SELECT TYPE, PAGES_ALLOCATED_COUNT FROM
    SYS.DM_OS_MEMORY_OBJECTS WHERE
    PAGE_ALLOCATOR_ADDRESS IN (SELECT TOP 10
    PAGE_ALLOCATOR_ADDRESS FROM
    SYS.DM_OS_MEMORY_CLERKS ORDER BY
    MULTI_PAGES_KB DESC) ORDER BY
    PAGES_ALLOCATED_COUNT DESC

    To get an idea of which individual processes are taking up memory, use the following query:

    SELECT TOP 10 SESSION_ID, LOGIN_TIME, HOST_NAME,
    PROGRAM_NAME, LOGIN_NAME, NT_DOMAIN,
    NT_USER_NAME, STATUS, CPU_TIME, MEMORY_USAGE,
    TOTAL_SCHEDULED_TIME, TOTAL_ELAPSED_TIME,
    LAST_REQUEST_START_TIME,
    LAST_REQUEST_END_TIME, READS, WRITES,
    LOGICAL_READS, TRANSACTION_ISOLATION_LEVEL,
    LOCK_TIMEOUT, DEADLOCK_PRIORITY, ROW_COUNT,
    PREV_ERROR FROM SYS.DM_EXEC_SESSIONS ORDER
    BY MEMORY_USAGE DESC

    To solve memory problems, see if SQL Server memory is correctly allocated. If so, ensure that the procedure cache has not been squeezed. Typical culprits eating up procedure cache are large stored procedures or large amounts of ad hoc SQL that are not parameterized. Then examine large memory consumers and objects to see if your applications need re-architecting. Processes consuming large amounts of memory should be redesigned to consume less memory.

  • Disk.
  • Processes that are disk intensive typically do not have the appropriate indexes or have poor execution plans. Here is a DMV query that lists the top 25 tables experiencing I/O waits.

    SELECT TOP 25 DB_NAME(D.DATABASE_ID) AS
    DATABASE_NAME,
    QUOTENAME(OBJECT_SCHEMA_NAME(D.OBJECT_ID,
    D.DATABASE_ID)) + N'.' +
    QUOTENAME(OBJECT_NAME(D.OBJECT_ID,
    D.DATABASE_ID)) AS OBJECT_NAME, D.DATABASE_ID,
    D.OBJECT_ID, D.PAGE_IO_LATCH_WAIT_COUNT,
    D.PAGE_IO_LATCH_WAIT_IN_MS, D.RANGE_SCANS,
    D.INDEX_LOOKUPS FROM (SELECT DATABASE_ID,
    OBJECT_ID, ROW_NUMBER() OVER (PARTITION BY
    DATABASE_ID ORDER BY
    SUM(PAGE_IO_LATCH_WAIT_IN_MS) DESC) AS
    ROW_NUMBER, SUM(PAGE_IO_LATCH_WAIT_COUNT) AS
    PAGE_IO_LATCH_WAIT_COUNT,
    SUM(PAGE_IO_LATCH_WAIT_IN_MS) AS
    PAGE_IO_LATCH_WAIT_IN_MS,
    SUM(RANGE_SCAN_COUNT) AS RANGE_SCANS,
    SUM(SINGLETON_LOOKUP_COUNT) AS
    INDEX_LOOKUPS FROM
    SYS.DM_DB_INDEX_OPERATIONAL_STATS(NULL, NULL,
    NULL, NULL) WHERE PAGE_IO_LATCH_WAIT_COUNT > 0
    GROUP BY DATABASE_ID, OBJECT_ID ) AS D LEFT JOIN
    (SELECT DISTINCT DATABASE_ID, OBJECT_ID FROM
    SYS.DM_DB_MISSING_INDEX_DETAILS) AS MID ON
    MID.DATABASE_ID = D.DATABASE_ID AND
    MID.OBJECT_ID = D.OBJECT_ID WHERE
    D.ROW_NUMBER>20 ORDER BY
    PAGE_IO_LATCH_WAIT_COUNT DESC

    You can also generate a list of columns that should have indexes on them:

    SELECT * FROM SYS.DM_DB_MISSING_INDEX_GROUPS
    G JOIN SYS.DM_DB_MISSING_INDEX_GROUP_STATS GS
    ON GS.GROUP_HANDLE = G.INDEX_GROUP_HANDLE
    JOIN SYS.DM_DB_MISSING_INDEX_DETAILS D ON
    G.INDEX_HANDLE = D.INDEX_HANDLE

    You can radically improve disk use with the correct index choices. Examine your execution plans carefully to improve disk use. You may need to change the RAID type or disk speeds to improve disk subsystem performance.

  • CPU. One of the most frequent contributors to high CPU consumption is stored procedure recompilation. Here is a DMV that displays the list of the top 25 recompilations:
  • SELECT TOP 25 SQL_TEXT.TEXT, SQL_HANDLE,
    PLAN_GENERATION_NUM, EXECUTION_COUNT, DBID,
    OBJECTID FROM SYS.DM_EXEC_QUERY_STATS A
    CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE)
    AS SQL_TEXT WHERE PLAN_GENERATION_NUM >1
    ORDER BY PLAN_GENERATION_NUM DESC

    This DMV lists the top CPU consumers:

    SELECT TOP 50 SUM(QS.TOTAL_WORKER_TIME) AS
    TOTAL_CPU_TIME, SUM(QS.EXECUTION_COUNT) AS
    TOTAL_EXECUTION_COUNT, COUNT(*) AS
    NUMBER_OF_STATEMENTS, SQL_TEXT.TEXT,
    QS.PLAN_HANDLE FROM SYS.DM_EXEC_QUERY_STATS
    QS CROSS APPLY
    SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) AS SQL_TEXT
    GROUP BY SQL_TEXT.TEXT,QS.PLAN_HANDLE ORDER
    BY SUM(QS.TOTAL_WORKER_TIME) DESC

    Other things that cause high CPU usage are bookmark lookups, bad parallelism and looping code.

    Final note

    When searching for bottlenecks, look for memory bottlenecks, then disk and finally CPU. Capture a baseline using System Monitor, SQL Profiler and DMVs to determine what is causing the bottleneck and if it can be solved by a hardware upgrade. Once you have a baseline, you are ready to start diagnosing the problem. In most cases, the solution will involve query tuning, query rewrites or re-architecting your solution. Many times, throwing hardware at the problem will not have the performance gains of simple index placement.


    ABOUT THE AUTHOR:   
    Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and studied economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.
    Copyright 2007 TechTarget

    This was first published in July 2007

    Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

    Pro+

    Features

    Enjoy the benefits of Pro+ membership, learn more and join.

    0 comments

    Oldest 

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to:

    -ADS BY GOOGLE

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close