 |
Return Create A Forum - Home |
 |
--------------------------------------------------------- |
|
ExcelSoft Database Professionals |
 |
https://esdbp.createaforum.com |
|
--------------------------------------------------------- |
|
***************************************************** |
|
Return to: Scripts |
|
***************************************************** |
|
#Post#: 122-------------------------------------------------- |
|
Top Cached SPs By Total Logical Writes |
|
By: srinivasma_exceldbp Date: December 25, 2014, 4:23 am |
|
--------------------------------------------------------- |
|
-- Top Cached SPs By Total Logical Writes (SQL Server 2014) |
|
-- Logical writes relate to both memory and disk I/O pressure |
|
-- This helps to find the most expensive cached stored |
|
procedures from a write I/O perspective |
|
-- Look into this if there are signs of I/O pressure or of |
|
memory 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(Minute, qs.cached_time, |
|
GETDATE()), 0) AS [Calls/Minute], |
|
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_logical_writes > 0 |
|
ORDER BY qs.total_logical_writes DESC OPTION (RECOMPILE); |
|
***************************************************** |
