gathering results from extended events trace

  • I need to find any stored procedures that have not been used over a certain time period.

    I have set up an extended events session to gather sp_statement_starting and _completed.

    The trace returns the object_id's of many stored procedures. I then query the sys.procedures and plug in the object_id to return the stored proc name.

    Do I have to repeat this process for every different object_id or is there a way I can query the trace results, using the object_id as my search criteria as one query ?

    Or if anybody has done this process of finding unused sp's and can offer an alternative method then please let me know !

  • PearlJammer1 (6/16/2015)


    I need to find any stored procedures that have not been used over a certain time period.

    I have set up an extended events session to gather sp_statement_starting and _completed.

    The trace returns the object_id's of many stored procedures. I then query the sys.procedures and plug in the object_id to return the stored proc name.

    Do I have to repeat this process for every different object_id or is there a way I can query the trace results, using the object_id as my search criteria as one query ?

    Or if anybody has done this process of finding unused sp's and can offer an alternative method then please let me know !

    Hi

    I usually use this query on the individual DBs:

    SELECTsc.name as 'schema',

    p.name as 'sp_name',

    p.type_desc,

    st.last_execution_time,

    st.execution_count

    FROM sys.procedures AS p INNER JOIN sys.schemas AS sc

    ON p.[schema_id] = sc.[schema_id]

    LEFT OUTER JOIN sys.dm_exec_procedure_stats AS st

    ON p.[object_id] = st.[object_id]

    ORDER BY st.last_execution_time DESC, p.name;

    This will tell you if a stored procedure in your DB had been run since the last server restart (or freeproccache)

    Here is the original article:

    http://stackoverflow.com/questions/10421439/tsql-query-to-find-un-used-stored-procedures

    ________________________________________________________________
    "Accept conditions as they exist, or accept the responsibility for changing them."

  • JimmyJones (6/17/2015)


    This will tell you if a stored procedure in your DB had been run since the last server restart (or freeproccache)

    Or other event which removes plans from cache, or plans got aged out of cache, or....

    The query stats/procedure stats are in no way reliable ways to tell if something has been run.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • PearlJammer1 (6/16/2015)


    The trace returns the object_id's of many stored procedures. I then query the sys.procedures and plug in the object_id to return the stored proc name.

    Why don't you join the extended events result to sys.objects?

    You'll have to do it per database, but it'll be faster than per object.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (6/17/2015)


    JimmyJones (6/17/2015)


    This will tell you if a stored procedure in your DB had been run since the last server restart (or freeproccache)

    Or other event which removes plans from cache, or plans got aged out of cache, or....

    The query stats/procedure stats are in no way reliable ways to tell if something has been run.

    Thanks Gail, you are absolutely right, I worded my sentence poorly about the plan cache.

    Do you have any 'foolproof' method to check the stored proc usage?

    ________________________________________________________________
    "Accept conditions as they exist, or accept the responsibility for changing them."

  • good script..thanks very much 🙂

  • PearlJammer1 (6/17/2015)


    good script..thanks very much 🙂

    Please note Gail's comment.

    It is absolutely correct, that script may not be the most accurate as your cache can change rapidly.

    ________________________________________________________________
    "Accept conditions as they exist, or accept the responsibility for changing them."

  • yeah I have made a note of that comment from Gail and tried something different.

    I found this approach to be quite helpful

    http://www.sqlservercentral.com/articles/Administration/2582/

  • PearlJammer1 (6/17/2015)


    yeah I have made a note of that comment from Gail and tried something different.

    I found this approach to be quite helpful

    http://www.sqlservercentral.com/articles/Administration/2582/%5B/quote%5D

    Make it easier for others to follow: http://www.sqlservercentral.com/articles/Administration/2582/

  • JimmyJones (6/17/2015)


    Do you have any 'foolproof' method to check the stored proc usage?

    Yup. Extended events run for a full business cycle (usually a year). Use the bucketiser and group on the object_id and database_id, that should reduce the data volumes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (6/17/2015)


    Yup. Extended events run for a full business cycle (usually a year). Use the bucketiser and group on the object_id and database_id, that should reduce the data volumes.

    Thank you!

    ________________________________________________________________
    "Accept conditions as they exist, or accept the responsibility for changing them."

Viewing 11 posts - 1 through 10 (of 10 total)

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