Sign in
 
HomeLibraryWikiLearnGalleryDownloadsSupportForumsBlogs

none
how to list top worst performing stored procedure?

 > 

    Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I need a SQL query to identify top worst performing stored procedures in a server.

    Any help is highly appreciated.

    Regards,

    Kccrga

    Monday, September 23, 2013 2:20 AM
    |

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    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.

    My Blog

    Monday, September 23, 2013 2:49 AM
    |
    Moderator
  • Question
    Sign in to vote
    1
    Sign in to vote
    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

    Monday, September 23, 2013 3:09 AM
    |
  • Question
    Sign in to vote
    1
    Sign in to vote

    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.htm

    Allen Li
    TechNet Community Support

    Tuesday, September 24, 2013 2:30 AM
    |
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    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.

    Monday, September 23, 2013 4:47 AM
    |

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote

    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.

    My Blog

    Monday, September 23, 2013 2:49 AM
    |
    Moderator
  • Question
    Sign in to vote
    1
    Sign in to vote
    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

    Monday, September 23, 2013 3:09 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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.

    Monday, September 23, 2013 4:47 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    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

    Monday, September 23, 2013 7:13 AM
    |
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote
    Worst by Average CPU and Physical IO
    Monday, September 23, 2013 11:13 PM
    |
  • Question
    Sign in to vote
    1
    Sign in to vote

    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.htm

    Allen Li
    TechNet Community Support

    Tuesday, September 24, 2013 2:30 AM
    |
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    SELECT TOP 10
       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

    SELECT TOP 50 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), 
    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 DESC

    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

    Tuesday, September 24, 2013 5:45 AM
    |
    Answerer