Databases Reference
In-Depth Information
LISTING 15-41:
Top cached stored procedures by total logical reads
-- Top Cached SPs By Total Logical Reads (SQL Server 2012).
-- Logical reads relate to memory pressure
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads
AS [TotalLogicalReads], qs.total_logical_reads/qs.execution_count
AS [AvgLogicalReads],qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0)
AS [Calls/Second], 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()
ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);
-- This helps you find the most expensive cached
-- stored procedures from a memory perspective
-- You should look at this if you see signs of memory pressure
This query returns the top cached procedures ordered by total logical reads. Logical reads equate
to memory pressure, and indirectly to I/O pressure. A logical read occurs when a query i nds
the data that it needs in the buffer pool (in memory). Once data is initially read off of the I/O
subsystem, it goes into the SQL Server buffer pool. If you have a large amount of physical RAM
and your instance-level max server memory setting is at an appropriately high level, you will have
a relatively large amount of space for the SQL Server buffer pool, which means that SQL Server is
much more likely to subsequently i nd what it needs there, rather than access the I/O subsystem
with a physical read.
If you are seeing signs of memory pressure, such as persistently low page life expectancy values, high
memory grants outstanding, and high memory grants pending, look very closely at the results of
this query. Again, you need to pay close attention to the
cached_time
column to ensure that you are
really looking at the most expensive stored procedures from a memory perspective.
After I have identii ed the top several stored procedure offenders, I like to 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 enables
me to start troubleshooting why the queries in the stored procedure are generating so many logical
reads. Perhaps the queries are doing implicit conversions that cause them to ignore a perfectly valid
index, or maybe they are using T-SQL functions on the left side of a
WHERE
clause. Another common
issue is a clustered index or table scan due to a missing index. There are many possible reasons why
a query has a very large number of logical reads.
If you are using SQL Server 2008 or later and you have Enterprise Edition, you should take a look
at SQL Server data compression. Data compression is usually touted as a way to reduce your I/O
utilization requirements in exchange for some added CPU utilization. While it does work very well
for that purpose (with indexes that are good candidates for compression), it can also reduce your
memory pressure in many cases. An index that has been compressed will stay compressed in the
Search WWH ::
Custom Search