locked
How to find which stored procedure not used in last 3 months RRS feed

  • Question

  • How do I identify which stored procedures are not using from last 3 months.

    I use the below query to find it but not get desired result

    -- Get list of possibly unused SPs (SQL 2008 only)
        SELECT p.name AS 'SP Name',s.name        -- Get list of all SPs in the current database
        FROM sys.procedures AS p
    inner join sys.schemas AS s on p.schema_id = s.schema_id 
        WHERE p.is_ms_shipped = 0 and s.schema_id = 9

        EXCEPT

        SELECT p.name AS 'SP Name' ,s.name        -- Get list of all SPs from the current database 
        FROM sys.procedures AS p          -- that are in the procedure cache
        INNER JOIN sys.dm_exec_procedure_stats AS qs ON p.object_id = qs.object_id
    inner join sys.schemas AS s on p.schema_id = s.schema_id 
        WHERE p.is_ms_shipped = 0 and s.schema_id = 9

    Please help me.

    Friday, April 19, 2019 6:21 AM

Answers

  • How do I identify which stored procedures are not using from last 3 months.

    You'd have to set something such as an Extended Event session, Server Side trace, etc. Since nothing was set in advance, it's difficult to get a clear picture. As a caution, don't rely on the DMV's or any other views that fetch data from the procedure cache in determining the stored proc historical executions. They only get you the information since the last restart or as long as the plan exists in the cache. So there's a high chance you will miss their history. 

    The following article talks about several ways you can set up auditing (I'd choose extended events) to capture the stored procedure invocations. 

    https://www.mssqltips.com/sqlservertip/3259/several-methods-to-collect-sql-server-stored-procedure-execution-history/


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Friday, April 19, 2019 12:35 PM

All replies

  • Check this link: It has multiple ways to check it:

    https://stackoverflow.com/questions/595742/last-run-date-on-a-stored-procedure-in-sql-server

    from trace to inbuilt views let us  know how it goes.


    Santosh Singh

    Friday, April 19, 2019 10:02 AM
  • find unused store proc

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


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Friday, April 19, 2019 11:24 AM
  • How do I identify which stored procedures are not using from last 3 months.

    You'd have to set something such as an Extended Event session, Server Side trace, etc. Since nothing was set in advance, it's difficult to get a clear picture. As a caution, don't rely on the DMV's or any other views that fetch data from the procedure cache in determining the stored proc historical executions. They only get you the information since the last restart or as long as the plan exists in the cache. So there's a high chance you will miss their history. 

    The following article talks about several ways you can set up auditing (I'd choose extended events) to capture the stored procedure invocations. 

    https://www.mssqltips.com/sqlservertip/3259/several-methods-to-collect-sql-server-stored-procedure-execution-history/


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Friday, April 19, 2019 12:35 PM
  • Hi SouravDutta,

     

    Mohsin has  provided you with a good link for reference. I also recommend use DMV sys.dm_exec_procedure_stats ,But it has a lot of limitations(if sql server is restarted, then the data is flushed).

     

     -- Get list of possibly unused SPs    

    SELECT p.name AS 'SP Name'        -- Get list of all SPs in the current database    

    FROM sys.procedures AS p    

    WHERE p.is_ms_shipped = 0

    EXCEPT

    SELECT p.name AS 'SP Name'        -- Get list of all SPs from the current database    

    FROM sys.procedures AS p          -- that are in the procedure cache    

    INNER JOIN sys.dm_exec_procedure_stats AS qs    

    ON p.object_id = qs.object_id    

    WHERE p.is_ms_shipped = 0;

     

    For more details ,please refer to Identifying Unused Stored Procedures

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, April 22, 2019 2:43 AM