Databases Reference
In-Depth Information
buffer pool, until the data is updated. This can dramatically reduce the space required in the buffer
pool for that index.
The next query, shown in Listing 15-42, looks at the most expensive stored procedures from a
physical reads perspective.
LISTING 15-42:
Top cached stored procedures by total physical reads
-- Top Cached SPs By Total Physical Reads (SQL Server 2012).
-- Physical reads relate to disk I/O pressure
SELECT TOP(25) p.name AS [SP Name],qs.total_physical_reads
AS [TotalPhysicalReads],qs.total_physical_reads/qs.execution_count
AS [AvgPhysicalReads], qs.execution_count, qs.total_logical_reads,
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
AND qs.total_physical_reads > 0
ORDER BY qs.total_physical_reads DESC,
qs.total_logical_reads DESC OPTION (RECOMPILE);
-- This helps you find the most expensive cached
-- stored procedures from a read I/O perspective
-- You should look at this if you see signs of I/O pressure or of memory pressure
This query returns the top cached stored procedures ordered by total physical reads. Physical reads
equate to disk I/O cost. A physical read happens when SQL Server cannot i nd what it needs in the
SQL Server buffer pool, so it must go out to the storage subsystem to retrieve the data. No matter
what kind of storage you are using, it is much slower than physical memory.
If you are seeing signs of I/O pressure, such as I/O-related wait types in your top cumulative wait
types query, or high times for disk seconds/read in Windows Performance Monitor, examine the
results of this query very closely. Don't forget to consider how long a stored procedure has been in
the cache by looking at the
cached_time
column. A very expensive stored procedure that was just
recently cached will probably not show up at the top of the list compared to other stored procedures
that have been cached for a long period of time.
After identifying the top several stored procedure offenders, run them individually (with appropriate
input parameters captured from SQL Server Proi ler) in SSMS with the
SET STATISTICS IO ON
command enabled and the graphical execution plan enabled. This will help you determine why
the queries in the stored procedure are generating so many physical reads. Again, after you have
exhausted standard query-tuning techniques to improve the situation, you should consider using SQL
Server data compression (if you have Enterprise Edition) to further reduce the amount of data being
read off of the I/O subsystem. Other options (besides standard query tuning) include adding more
physical RAM to your server and improving your I/O subsystem. Perhaps you can add additional
spindles to a RAID array, change the RAID level, change the hardware cache policy, and
so on.
Search WWH ::
Custom Search