Home > Performence Tuning > Troubleshoot Store procedure at SQL 2008

Troubleshoot Store procedure at SQL 2008


Store procedure one of the object which are used in SQL server database very massively to line up with application. From DBA and Developer end it is tough deal to figure out the culprit store procedure which is consume high resource. We have observed the in production server more than 1000 user define store procedure created to run the application. By help of below code Developer and DBA can easily track which store procedure taking too much time and consume high CPU,IO and Memory. It help us our life to tune the program which required attention.

– Top Cached SPs By Execution Count (SQL 2008)

SELECT TOP(250) p.name AS [SP Name], qs.execution_count,

ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],

qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.total_worker_time AS [TotalWorkerTime],

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.execution_count DESC OPTION (RECOMPILE);

– Tells you which cached stored procedures are called the most often

– This helps you characterize and baseline your workload

– Top Cached SPs By Avg Elapsed Time (SQL 2008)

SELECT TOP(25) p.name AS [SP Name], qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],

qs.total_elapsed_time, qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time,

GETDATE()), 0) AS [Calls/Second], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],

qs.total_worker_time AS [TotalWorkerTime], 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 avg_elapsed_time DESC OPTION (RECOMPILE);

– This helps you find long-running cached stored procedures that

– may be easy to optimize with standard query tuning techniques

– Top Cached SPs By Total Worker time (SQL 2008). Worker time relates to CPU cost

SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime],

qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], 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_worker_time DESC OPTION (RECOMPILE);

– This helps you find the most expensive cached stored procedures from a CPU perspective

– You should look at this if you see signs of CPU pressure

– Top Cached SPs By Total Logical Reads (SQL 2008). 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

– Top Cached SPs By Total Physical Reads (SQL 2008). Physical reads relate to disk I/O pressure

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);

– 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

 

– Top Cached SPs By Total Logical Writes (SQL 2008).

– 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

Advertisements
Categories: Performence Tuning
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: