Answered by:
how to list top worst performing stored procedure?

Question
Answers
-
One way to address this is to run a trace on the server which captures the reads, writes, cpu and duration of stored procedures. This data can give a clear picture of the relative performance of different stored procedures.
Keep in mind that a trace adds some overhead as well. So you may want to capture minimal information and for minimum period of time. Having the trace data gives you the freedom to keep historical data and run various analysis.
If you need something quick and simple, take a look at the 'Performance dashboard reports'. Using the performance dashboard reports, one can see the worst queries being executed on the server.
I would also recommend taking a look at this post which shows a code snippet which retrieves details of the most expensive queries.
- Proposed as answer by Saeid Hasani Monday, September 23, 2013 4:25 AM
- Marked as answer by Allen Li - MSFTModerator Monday, September 30, 2013 3:14 PM
-
You could use the SQL Server default trace. This will list all the sql statements that has executed in the system. You could do some data massaging to filter only stored procedures and their CPU/Read/writes etc.
Satheesh
- Proposed as answer by SQL_Learn Friday, September 27, 2013 5:14 AM
- Marked as answer by Allen Li - MSFTModerator Monday, September 30, 2013 3:14 PM
-
Hi Kccrga,
To find the SPs that consumes the most CPU resources, I/O resources, we can refer to the codes on the following link:
Monitoring Stored Procedure Usage
http://www.databasejournal.com/features/mssql/article.php/3687186/Monitoring-Stored-Procedure-Usage.htmAllen Li
TechNet Community Support- Marked as answer by Allen Li - MSFTModerator Monday, September 30, 2013 3:14 PM
-
Try the below:
SELECT DB_NAME(dest.dbid) AS [Database name], OBJECT_NAME(dest.objectid, dest.dbid) AS [Stored procedure name], [Execution Count], [Avg CPU time (ms)], [Avg Elapsed time (ms)], [Creation time], [Last Execution time], [Total CPU time (ms)], [Total Elapsed time (ms)], [Plan Count], getdate() AS [Recorded date], sql_handle, dest.text AS 'Text' FROM ( SELECT TOP 100 sql_handle, COUNT(DISTINCT plan_handle) AS [Plan Count], MAX(execution_count) AS [Execution Count], SUM(total_worker_time/execution_count)/1000 AS [Avg CPU time (ms)], SUM(total_elapsed_time/execution_count)/1000 AS [Avg Elapsed time (ms)], MAX(creation_time) AS [Creation time], MAX(last_execution_time) AS [Last Execution time], SUM(total_worker_time/1000) AS [Total CPU time (ms)], SUM(total_elapsed_time/1000) AS [Total Elapsed time (ms)] FROM sys.dm_exec_query_stats GROUP BY sql_handle ORDER BY SUM(total_worker_time/execution_count)/1000 DESC ) deqs CROSS APPLY sys.dm_exec_sql_text (deqs.sql_handle) AS dest;
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
- Proposed as answer by Naomi NModerator Tuesday, September 24, 2013 3:07 AM
- Marked as answer by Allen Li - MSFTModerator Monday, September 30, 2013 3:14 PM
All replies
-
One way to address this is to run a trace on the server which captures the reads, writes, cpu and duration of stored procedures. This data can give a clear picture of the relative performance of different stored procedures.
Keep in mind that a trace adds some overhead as well. So you may want to capture minimal information and for minimum period of time. Having the trace data gives you the freedom to keep historical data and run various analysis.
If you need something quick and simple, take a look at the 'Performance dashboard reports'. Using the performance dashboard reports, one can see the worst queries being executed on the server.
I would also recommend taking a look at this post which shows a code snippet which retrieves details of the most expensive queries.
- Proposed as answer by Saeid Hasani Monday, September 23, 2013 4:25 AM
- Marked as answer by Allen Li - MSFTModerator Monday, September 30, 2013 3:14 PM
-
You could use the SQL Server default trace. This will list all the sql statements that has executed in the system. You could do some data massaging to filter only stored procedures and their CPU/Read/writes etc.
Satheesh
- Proposed as answer by SQL_Learn Friday, September 27, 2013 5:14 AM
- Marked as answer by Allen Li - MSFTModerator Monday, September 30, 2013 3:14 PM
-
Try the below:
SELECT DB_NAME(dest.dbid) AS [Database name], OBJECT_NAME(dest.objectid, dest.dbid) AS [Stored procedure name], [Execution Count], [Avg CPU time (ms)], [Avg Elapsed time (ms)], [Creation time], [Last Execution time], [Total CPU time (ms)], [Total Elapsed time (ms)], [Plan Count], getdate() AS [Recorded date], sql_handle, dest.text AS 'Text' FROM ( SELECT TOP 100 sql_handle, COUNT(DISTINCT plan_handle) AS [Plan Count], MAX(execution_count) AS [Execution Count], SUM(total_worker_time/execution_count)/1000 AS [Avg CPU time (ms)], SUM(total_elapsed_time/execution_count)/1000 AS [Avg Elapsed time (ms)], MAX(creation_time) AS [Creation time], MAX(last_execution_time) AS [Last Execution time], SUM(total_worker_time/1000) AS [Total CPU time (ms)], SUM(total_elapsed_time/1000) AS [Total Elapsed time (ms)] FROM sys.dm_exec_query_stats GROUP BY sql_handle ORDER BY SUM(total_worker_time/execution_count)/1000 DESC ) deqs CROSS APPLY sys.dm_exec_sql_text (deqs.sql_handle) AS dest;
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
- Proposed as answer by Naomi NModerator Tuesday, September 24, 2013 3:07 AM
- Marked as answer by Allen Li - MSFTModerator Monday, September 30, 2013 3:14 PM
-
Worsted by CPU? by Logical IO? By Physical IO?
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
-
-
Hi Kccrga,
To find the SPs that consumes the most CPU resources, I/O resources, we can refer to the codes on the following link:
Monitoring Stored Procedure Usage
http://www.databasejournal.com/features/mssql/article.php/3687186/Monitoring-Stored-Procedure-Usage.htmAllen Li
TechNet Community Support- Marked as answer by Allen Li - MSFTModerator Monday, September 30, 2013 3:14 PM
-
SELECT TOP 10
SELECT TOP 50 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
execution_count,
(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan,
qs.plan_handle
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0 /*AND SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1) LIKE '%DISTINCT%'*/
ORDER BY qs.total_physical_reads DESCBest Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence