Advanced Search

Cached SPs By Total Physical Reads (SQL 2008 only)

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. SELECT p.name AS [SP Name],qs.total_physical_reads AS [TotalPhysicalReads], 
  2. qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.execution_count, 
  3. ISNULL(qs.execution_count/DATEDIFF(SECOND, qs.cached_time, GETDATE()), 0) AS [Calls/SECOND],
  4. qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count 
  5. AS [avg_elapsed_time], qs.cached_time 
  6. FROM sys.procedures AS p
  7. INNER JOIN sys.dm_exec_procedure_stats AS qs
  8. ON p.[object_id] = qs.[object_id]
  9. WHERE qs.database_id = DB_ID()
  10. ORDER BY qs.total_physical_reads DESC;

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.