SQL Server queries with DMVs for examining bottlenecks

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.

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.

---

• Home: Introduction
• Step 1: Create baseline and consider bottleneck symptoms
• Step 2: Detect performance bottlenecks with these tools
• Step 3: SQL Server queries with DMVs for examining bottlenecks

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