Post reply

Help Needed in Find the Proc

  • February 23, 2015 at 9:47 am

    #294806

    Hi,

    I have been investigating which stored procedures taking long time to run and i am trying the below script to idnetify.

    SELECT

    t.TEXT QueryName,

    s.execution_count AS ExecutionCount,

    s.max_elapsed_time AS MaxElapsedTime,

    ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,

    s.creation_time AS LogCreatedOn,

    ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec

    FROM sys.dm_exec_query_stats s

    CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t

    ORDER BY

    s.max_elapsed_time DESC

    GO

    But it gives only the query and not procedure name. Please help me on finding the procedures taking long time to run.

  • February 23, 2015 at 9:54 am

    #1779323

    You need to use a different system view.

    SELECT

    p.name ProcedureName,

    t.TEXT QueryName,

    s.execution_count AS ExecutionCount,

    s.max_elapsed_time AS MaxElapsedTime,

    ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,

    p.modify_date AS LogCreatedOn,

    ISNULL((s.execution_count * 1.)/ DATEDIFF(s, p.modify_date , GETDATE()), 0) AS FrequencyPerSec

    FROM sys.dm_exec_procedure_stats s

    CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t

    JOIN sys.procedures p ON s.object_id = p.object_id AND s.database_id = DB_ID()

    ORDER BY

    s.max_elapsed_time DESC

    GO

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • February 23, 2015 at 10:18 am

    #1779328

    Just remember that those queries are completely cache dependent. If anything has aged out of cache or queries that don't go into the cache (such as queries with RECOMPILE hints), you won't see them.

    But yeah, sys.dm_exec_procedure_stats will give you the proc.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • February 23, 2015 at 10:23 am

    #1779332

    Thanks Luis, Appreciated.

  • February 23, 2015 at 10:27 am

    #1779334

    hi Grant,

    Thanks for your reply and if that is the case what is the best way to achieve this. If am not, the proc modified (adding removing column or etc), the cache will be recompiled as the old cache no longer valid.

    Please help me on achieving this task

  • February 23, 2015 at 10:32 am

    #1779335

    It really depends on what you're trying to do. If you're trying to identify long running queries, you'll probably get most of them using the approach outlined. If you're trying to get all executions of all long running queries, you might not. In that case, you need to use extended events or trace events.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • February 23, 2015 at 11:18 am

    #1779351

    Thanks Grant

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply