Databases Reference
In-Depth Information
Next, take a look at the most expensive cache stored procedures for logical writes. To do that, use
the query shown in Listing 15-43.
LISTING 15-43:
Top cached stored procedures by total logical writes
-- Top Cached SPs By Total Logical Writes (SQL Server 2012).
-- Logical writes relate to both memory and disk I/O pressure
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_writes
AS [TotalLogicalWrites], qs.total_logical_writes/qs.execution_count
AS [AvgLogicalWrites], 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_writes DESC OPTION (RECOMPILE);
-- This helps you find the most expensive cached
-- stored procedures from a write I/O perspective
-- You should look at this if you see signs of I/O pressure or of memory pressure
This query returns the most expensive cached stored procedures ordered by total logical writes,
meaning simply the stored procedures that are generating the most write activity in your database.
You might be surprised to see
SELECT
type stored procedures show up in this list, but that often
happens when the
SELECT
procedures
INSERT
intermediate results into a temp table or table variable
before doing a later
SELECT
operation.
Especially with OLTP workloads that see a lot of intensive write activity, you should pay attention
to the results of this query. As always, consider the
cached_time
column before making any judg-
ments. After you have identii ed the actual top offenders in this query, talk to your developers to see
if perhaps they are updating too much information, or updating information too frequently. I would
also be looking at the index usage on your most frequently updated tables. You might discover that
you have a number of nonclustered indexes that have a high number of writes, but no reads. Having
fewer indexes on a volatile, write-intensive table will dei nitely help write performance. After some
further investigation and analysis, you might want to drop some of those unused indexes.
From a hardware perspective, adding more physical RAM to your server might help even out your
write I/O workload a little bit. If SQL Server has more RAM in the buffer pool, it will not have to
issue automatic checkpoints to write to the data i le(s) quite as often. Going longer between auto-
matic checkpoints can help reduce total write I/O somewhat because more data in the same data
pages might have been modii ed over that longer period of time. A system that is under memory
pressure will also be forced to have the lazy writer write dirty pages in memory to the disk
subsystem more often.
Finally, improving your I/O subsystem, especially the LUN where your transaction log is located,
would be an obvious step. Again, adding more spindles to the RAID array, changing from RAID
Search WWH ::
Custom Search