SQL Server queries with DMVs for examining 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:

Download this free guide

Download: 60-Page Expert RDBMS Guide

Trends come and go, but your DB strategy shouldn’t be a flavor of the month. Learn why you shouldn’t get distracted by new DB technology, how Facebook is using a RDBMS to do the data slicing and dicing they can’t in Hadoop, and more.

Start Download

• Corporate E-mail Address:

  You forgot to provide an Email Address.

  This email address doesn’t appear to be valid.

  This email address is already registered. Please login.

  You have exceeded the maximum character limit.

  Please provide a Corporate E-mail Address.

• • I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.

  Please check the box if you want to proceed.

• • I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.

  Please check the box if you want to proceed.

By submitting my Email address I confirm that I have read and accepted the Terms of Use and Declaration of Consent.

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.

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.

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