594
C
HAPTER
47
How to use Dynamic Management Views
queries that are relatively inexpensive for individual executions but are called very fre-
quently (which makes them expensive in aggregate), or you may have individual que-
ries that are more expensive
CPU
-wise, but are not called as often. Looking at total
worker time is a reliable method for finding the most expensive queries from an over-
all
CPU
perspective.
Another similar
DMV
query, shown in listing 6, sorts by average worker time. This
will let you find expensive
CPU
queries that may be easier to improve at the database
level with standard database tuning techniques.
-- Get Top 20 executed SP's ordered by Avg worker time (CPU pressure)
SELECT TOP 20
qt.text AS 'SP Name', qs.total_worker_time/qs.execution_count
AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime', qs.execution_count
AS 'Execution Count',
ISNULL(qs.execution_count/
DATEDIFF(Second, qs.creation_time, GetDate()), 0)
AS 'Calls/Second',
ISNULL(qs.total_elapsed_time/qs.execution_count, 0)

prevent the acquisition of enough
I/O
capacity to support a large workload.
Whatever your situation, it helps if you know how recognize and measure signs of
I/O
pressure on
SQL
Server 2005/2008. One thing you can do to help reduce
I/O
pressure in general is to make sure you’re not under memory pressure, which will
cause added
I/O
pressure. We’ll look at how to detect memory pressure a little later.
For large
SQL
Server 2005/2008 deployments, you should make sure you’re run-
ning a 64-bit edition of
SQL
Server (so you can better use the
RAM
that you have),
and you should try to get as much
RAM
as you can afford or will fit into the database
server. Having sufficient
RAM
installed will reduce
I/O
pressure for reads, and will
allow

(which isn’t a good idea). This query will help prove that the log
file is causing user waits.
-- Avg I/O Stalls (Lower is better)
SELECT database_id, file_id , io_stall_read_ms, num_of_reads,
CAST(io_stall_read_ms/(1.0 + num_of_reads)
AS NUMERIC(10,1)) AS 'avg_read_stall_ms',
io_stall_write_ms, num_of_writes,
CAST(io_stall_write_ms/(1.0 + num_of_writes) AS NUMERIC(10,1))
AS 'avg_write_stall_ms',
io_stall_read_ms + io_stall_write_ms
AS io_stalls, num_of_reads + num_of_writes AS total_io,
CAST((io_stall_read_ms + io_stall_write_ms)
/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1))
AS 'avg_io_stall_ms'
FROM sys.dm_io_virtual_file_stats(null,null)
-- This can be filtered by database and file id
ORDER BY avg_io_stall_ms DESC
The query in listing 9 lets you focus on the read/write activity for each file in a partic-
ular database. It shows you the percentage of reads and writes, both in number of
reads and writes and in actual bytes read and written. This can help you analyze and
size your disk
I/O
subsystem.
-- I/O Statistics for a single database
SELECT
file_id
, num_of_reads
, num_of_writes
, (num_of_reads + num_of_writes) AS 'Writes + Reads'
, num_of_bytes_read

ence also.
-- Get Top 20 executed SP's ordered by physical reads (read I/O pressure)
SELECT TOP 20 qt.text AS 'SP Name', qs.total_physical_reads,
➥
qs.total_physical_reads/qs.execution_count AS 'Avg Physical Reads',
qs.execution_count AS 'Execution Count',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS
➥
'Calls/Second',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id() -- Filter by current database
ORDER BY qs.total_physical_reads DESC
The query in listing 11 will help you find the stored procedures that are causing the
most write activity. Query and index tuning can help here. You can also talk to your
developers about middle-tier write caching or other application changes to reduce
writes if possible.
-- Get Top 20 executed SP's ordered by logical writes/minute (write I/O
➥
pressure)
SELECT TOP 20 qt.text AS 'SP Name', qs.total_logical_writes,
➥
qs.total_logical_writes/qs.execution_count AS 'AvgLogicalWrites',
qs.total_logical_writes/DATEDIFF(Minute, qs.creation_time, GetDate())
➥

GB
to 64
GB
to 128
GB
over the past several years,
which makes it even more important to make sure you’re running a 64-bit version of
SQL
Server.
Once you’ve loaded up your 64-bit database server with as much memory as possi-
ble, it’s important that you do two things to make sure that
SQL
Server 2005/2008 will
play nicely with all of the available memory. First, you should grant the Lock Pages in
Memory Windows right (using gpedit.msc) to the
SQL
Server Service account. Sec-
ond, you should set the Max Server Memory setting in
SQL
Server to a value that will
leave sufficient available memory for the operating system (and anything else that’s
running on the server) while
SQL
Server is under a load. This is typically anywhere
from about 2
GB
to 4
GB
available, depending on how much
RAM

queries that give you much more detail about how
SQL
Server 2005/2008 is using memory. We’ll cover some of the more useful ones.
Listing 12 lists the top 10 memory consumers in your buffer pool.
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
598
C
HAPTER
47
How to use Dynamic Management Views

SELECT TOP (10) type, SUM(single_pages_kb) AS [SPA Mem, KB]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC
Here are some of the common types you’ll see when you run the query in listing 12:

CACHESTORE_SQLCP
—
SQL
plans (dynamic or prepared
SQL
)

CACHESTORE_OBJCP
—Object plans (stored procedures, functions, and triggers)

CACHESTORE_PHDR
—Bound Trees

INNER JOIN sys.partitions AS p
ON a.container_id = p.hobt_id
WHERE b.database_id = db_id()
GROUP BY p.object_id, p.index_id
ORDER BY buffer_count DESC
In listing 15, we’ll find the largest ad hoc queries sitting in the plan cache.
SELECT TOP(100) [text], size_in_bytes
FROM sys.dm_Exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
Listing 12 Top 10 consumers of memory from buffer pool
Listing 13 Getting query mix and use counts for each plan
Listing 14 Finding indexes and tables that use the most buffer space
Listing 15 Finding ad hoc queries that are bloating the plan cache
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
599
SQL Server memory pressure
WHERE cacheobjtype = 'Compiled Plan'
AND objtype = 'Adhoc' AND usecounts = 1
ORDER BY size_in_bytes DESC
Again, using the new Optimize for Ad Hoc Workloads instance setting in
SQL
Server
2008 can really help if you have problems here.
The query in listing 16 will show you your 25 most expensive queries from a logical
reads perspective (which equates to memory pressure).
-- Get Top 25 executed SP's ordered by logical reads (memory pressure)
SELECT TOP 25 qt.text AS 'SP Name', total_logical_reads,
qs.execution_count AS 'Execution Count',
➥

The query in listing 18 will help you find tables with the most writes.
SELECT object_name(s.object_id) AS 'Tablename',
SUM(user_updates) AS 'Total Writes',
SUM(user_seeks) AS 'User Seeks', SUM(user_scans) AS 'User Scans',
Listing 16 Finding your 25 most expensive queries
Listing 17 Finding tables with the most reads
Listing 18 Finding tables with the most writes
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
600
C
HAPTER
47
How to use Dynamic Management Views
SUM(user_lookups)AS 'User Lookups',
SUM(user_seeks + user_scans + user_lookups)AS 'Total Reads'
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE objectproperty(s.object_id,'IsUserTable') = 1
AND s.database_id = db_id()
GROUP BY object_name(s.object_id)
ORDER BY 'Total Writes' DESC
SQL Server index usage
As you’re probably aware, having proper indexes in place to support your workload is
critical with
SQL
Server 2005/2008 (as with any relational database). Generally speak-
ing, you’ll want more indexes with a reporting or

Server thinks it wants the index that it thinks is “missing.”
If you see a row with a high index advantage with a last_user_seek from a few seconds
or minutes ago, it’s probably from your regular workload, so you probably want to seri-
ously consider adding that index. You should also be aware that this query won’t rec-
ommend adding any clustered indexes.
One feature I’ve discovered over time is that if you add a new index of any sort to a
table, or if you delete an index, it will clear out all of the missing index stats for that
table. This may lead you to believe that there are no more missing indexes on the
table, which is probably not true. Wait a little while, and then run the missing index
query again to confirm whether there are any more missing indexes for the table.
The
DMV
queries in listings 19 through 21 will show bad indexes and missing
indexes, and then let you concentrate on an individual table to determine whether
you should make any index changes for that table based on your workload.
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
601
SQL Server index usage

-- Possible Bad Indexes (writes > reads)
SELECT object_name(s.object_id) AS 'Table Name',
i.name AS 'Index Name', i.index_id,
user_updates AS 'Total Writes',
user_seeks + user_scans + user_lookups AS 'Total Reads',
user_updates - (user_seeks + user_scans + user_lookups) AS 'Difference'
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id

mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost,
➥
migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
ORDER BY migs.last_user_seek DESC;
Listing 19 Finding bad indexes
Listing 20 Looking at Index Advantage to find missing indexes
Listing 21 Looking at Last User Seek to find missing indexes
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
602
C
HAPTER
47
How to use Dynamic Management Views
After running the
DMV
queries in listings 20 and 21, you may see the same table
showing up with multiple missing indexes. If I see this, I start to examine that table
more closely.
Once you’ve narrowed your focus to a particular table, you can gather more spe-
cific index information about that table with the queries in listings 22 and 23.
-- Index Read/Write stats for a single table
SELECT object_name(s.object_id) AS 'TableName',
i.name AS 'IndexName', i.index_id,

-- Show existing indexes for this table (does not show included columns)
EXEC sp_HelpIndex 'yourtablename';
As you consider making index changes to a large, busy table, you need to consider
your workload characteristics. You should be more reluctant to add additional indexes
if you have an
OLTP
workload. You should take advantage of online index operations
whenever possible if you’re running Enterprise Edition in order to avoid locking and
blocking issues during index builds. You also should consider using the
MAXDOP
option
during index builds to prevent
SQL
Server from using all of the
CPU
cores for an
index build. This may mean that the index takes longer to build, but the rest of your
workload won’t be starved for
CPU
during the index-creation process.
Listing 22 Getting statistics for a table
Listing 23 Missing indexes for a single table
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
603
Detecting blocking in SQL Server
Detecting blocking in SQL Server
Especially with
OLTP
workloads, you may run into blocking issues with

(SELECT * FROM sys.dm_exec_sql_text(blocked_query.sql_handle)) AS sql_text
INNER JOIN sys.dm_os_waiting_tasks AS waits
ON waits.session_id = blocking_query.session_id
SELECT t1.resource_type AS 'lock type',db_name(resource_database_id) AS
➥
'database',
t1.resource_associated_entity_id AS 'blk object',t1.request_mode AS 'lock
➥
req', --- lock requested
t1.request_session_id AS 'waiter sid', t2.wait_duration_ms AS 'wait time',
(SELECT [text] FROM sys.dm_exec_requests AS r
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',
t2.blocking_session_id AS 'blocker sid',
(SELECT [text] FROM sys.sysprocesses AS p
Listing 24 Checking SQL Server schedulers to see if you may have blocking
Listing 25 Detecting blocking
Listing 26 Detecting blocking (a more accurate and complete version)
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
604
C
HAPTER

Profiler traces, and they pro-
vide much more granular
SQL
Server–specific detail than Performance Monitor
traces. They also give you more control and detail than
SQL
Server Activity Monitor.
DMV
queries are oriented toward gathering snapshot-type information about current
activity and aggregate activity since
SQL
Server was last started. As such, they’re less
useful for historical reporting and trend analysis unless you take steps to write their
output to permanent tables.
You can easily set up a dedicated
SQL
Server login that your operations staff can
use to run these queries to do first-level troubleshooting on a database server or data-
base. It’s easy to convert any of these queries to stored procedures, and then call them
from an application.
DMV
queries were one of the more useful features added in
SQL
Server 2005. If you use them wisely, you can quickly and easily detect and diagnose
many
SQL
Server ailments, truly becoming Dr.
DMV
.
Listing 27 Looking at locks that are causing problems

606
48 Query performance
and disk I/O counters
Linchi Shea
SQL
Server provides an excellent set of tools for troubleshooting query perfor-
mance problems. At the top of the list, you have tools for capturing and analyzing
query plans, tools for checking whether statistics are up to date or an index is use-
ful, tools for capturing the time and resource cost of processing a query, and tools
to help optimize
SQL
queries.
What you do not typically find in the toolset recommended for troubleshoot-
ing
SQL
Server query performance problems are the disk
I/O
performance coun-
ters. This is understandable because the disk
I/O
counters are the statistics at the
operating system drive level. Although they can help you determine whether your
disk subsystem is a bottleneck in the overall resource consumption of your
SQL
Server system, they do not generally reveal useful information on processing a
particular query.
In some scenarios you can use the disk
I/O
performance counters in trouble-
shooting query performance problems. To completely ignore the disk

disk access speed is measured in milliseconds (for example, 5 ms), whereas memory
access speed is measured in nanoseconds (for example 100 ns).
What is important to recognize is that not all disk
I/O
s are equal in their perfor-
mance. More specifically, random
I/O
s are far slower, or more expensive, than
sequential
I/O
s.
Whether
I/O
s are random or sequential can be defined by the relative data loca-
tions of two consecutive
I/O
requests. If the next
I/O
request is for data at a ran-
dom location, the
I/O
requests are random, whereas if the next
I/O
request is for
data residing next to the currently requested data, the
I/O
requests are sequential.
On a conventional disk, the time it takes to complete the operation of a random
I/O
typically includes moving the disk drive head to the right track on the platter and

lowing counters under the
LogicalDisk
object as the key performance indicators to
evaluate disk
I/O
performance:

Avg.

Disk sec/Read
—The number of seconds to complete a read operation on
the disk drive, averaged over the polling interval

Avg.

Disk sec/Write
—The number of seconds to complete a write operation
on the disk drive, averaged over the polling interval

Avg.

Disk Bytes/Read
—The number of bytes transferred from the disk drive
per read operation, averaged over the polling interval

Avg.

Disk Bytes/Write
—The number of bytes transferred to the disk drive per
write operation, averaged over the polling interval

Bytes/sec
—The number of bytes transferred to the disk drive per
second

Current

Disk

Queue

Length
—The number of requests outstanding on the disk
drive
These counters measure five key
I/O
metrics:

Disk
I/O
latency—For example,
Avg.

Disk

sec/Read

Disk
I/O
size—For example,
Avg.

performance counters
be collected and evaluated as a whole in order to see the complete picture of the disk
I/O
activities. In particular, if you focus on the disk latency counters without also
checking the
I/O
size counters, you may end up drawing a wrong conclusion. For
instance, a commonly accepted threshold for judging whether a disk
I/O
is taking too
long is 10 ms. In practice, you need to make sure that this threshold applies only to
smaller
I/O
s. When a system is doing large
I/O
s, the threshold of 10 ms may be too
low, and can lead to false alarms.
Random or sequential I/Os
and disk performance counters
By now, hopefully you agree that to gain better
I/O
throughput, sequential disk
I/O
s
are much preferred over random disk
I/O
s. But how do you identify whether
SQL
Server is doing sequential
I/O

I/O
is smaller than 64
KB
,
I/O latency
The latency of an
I/O
request is also known as
I/O
response time. It is measured by
taking the difference between the time the
I/O
request is submitted and the time the
completion acknowledgement is received.
I/O
latency can be measured at different
levels of the
I/O
stack. The
Avg.

Disk

sec/Read
counter is a measure taken in the
Windows logical disk driver.
Licensed to Kerri Ross <>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
609
SQL Server operations and I/O sizes

KB
sequential reads and writes.
Keep in mind that, as a
SQL
Server professional, you are interested in disk
I/O
s gener-
ated by
SQL
Server. And with the exception of database transaction logging,
SQL
Server is coded to avoid making small sequential
I/O
requests when it can issue large
sequential
I/O
s. Whenever possible,
SQL
Server combines otherwise multiple sequen-
tial
I/O
s into a single larger
I/O
request. As described in Bob Dorr’s excellent white
papers, “
SQL
Server 2000
I/O
Basics” and “
SQL

I/O
s.
Although life is made difficult by not having any disk performance counters to
distinguish random
I/O
s from sequential
I/O
s, it is easy to tell small
I/O
s from large
I/O
s using the following performance counters under the
LogicalDisk
object:

Avg.

Disk

Bytes/Read

Avg.

Disk

Bytes/Write
If you don’t care about separating reads from writes,
Avg.

Disk