[sourcecode language=”sql”]
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);
[/sourcecode]
– 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
[sourcecode language=”sql”]
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);
[/sourcecode]
— 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
<div role="option"> <span style="color: #0000ff;"> padding: 0 0 28px;</span> </div> <div role="option"> <span style="color: #0000ff;"> position: relative;</span> </div> </div> <div role="option"> <span style="color: #0000ff;"> padding: 0 0 28px;</span> </div> <div role="option"> <span style="color: #0000ff;"> position: relative;</span> </div> </div> <div role="group"> <div role="listbox"> <div role="option"> <span style="color: #ff0000;"><del>padding: 0 0 28px;</del></span> </div> </div> </div> <div role="presentation"> } </div>
This is a great idea for Dads who are currently or about to babysit their kid 🙂 Awesome…
This is a copy of the Microsoft Windows Server 2012 launch event slide show.