How to find unused Stored Procedures in SQL Server 2005/2008

  • tony.turner

    Right there with Babe

    Points: 783

    dma-669038 wrote: We typically go by below DMV based query - it has some limitations, only gives what is in cache since last reboot, but depending on your environment it might work well

    Not so sure about that "since last reboot". DMVs like sys.dm_exec_query_stats have a creation time; observation has shown a relatively short life (in the order of hours) on an active system. Appears it has a lot to do with statistics updates. Observation has also shown sys.dm_sql_text to have a limited lifespan. There is nothing in SQL BOL that implies the life of sys.dm_exec_cached_plans exists beyond the cache entry lifetime. The implication is that you need a feeling for the average cache lifetime in your particular server before you can reasonably set a monitor interval. By contrast cache entries on inactive (test) servers may appear to last forever

    It appears there may also be (rare) circumstances where cache entries are purged, and thus missed from your stats. SQL entries may also not necessarily be kept under certain circumstances

  • irozenberg

    SSC-Addicted

    Points: 478

    Well done!

    I would recommend to use built-in information views instead of system table sys.sysobjects

    Cheers

  • Jeff Moden

    SSC Guru

    Points: 996843

    First up I start with the SQL Server Profiler with a slightly changed version of the "SP_Counts" template. After my modifications [font="Arial Black"]I have based the template on the "SP:Starting" event [/font]with the following columns:

    ObjectName (name of the Stored Procedure)

    [font="Arial Black"]Duration [/font](not really needed, I'll explain the purpose later)

    DatabaseName (not really needed since we'll filter on the database anyway)

    SPID (mandatory, I don't want it but Profiler obviously need it)

    StartTime (same as Duration)

    [font="Arial Black"]EndTime [/font](same as Duration)

    Hmmmm.... I'm curious as to how you got "Duration" and "EndTime" to work on the "SP:Starting" event. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Craig Lucas

    SSC Veteran

    Points: 242

    Funny you should write an article about this. This is a good topic. I solved this problem in a similar but different way.

    We have hundreds of SQL servers in our environment with thousands of changes going in a month. Over time objects become stale and no one really does the due diligence to clean up unused objects. I estimated that we had hundreds of gigabytes of unused tables as well as thousands of unused objects spread across hundreds of production databases.

    We also have a mixture of SQL 2000, 2005 and 2008. So this technique had to work on all environments.

    (Before reading keep in mind that what my process does is all configurable. You setup what servers and databases you want to monitor)

    So I developed an automated process to collect object usage. I first take a snapshot of the target database and store the results in a table. This captures every object in the database and what objects use those objects (it goes through syscomments to find which objects are used by which objects). As someone else pointed out, what happens when a stored procedure calls a stored procedure, or a stored procedure uses a view...etc. This takes care of that concern.

    Figuring out what objects use other objects is the easy part. Its figuring out what objects are used by ad-hoc queries that is hard. And for that we have to use a SQL trace.

    The process starts a SQL trace to dump trace files out to directory for a given amount of time. Then I use an SSIS package to load trace files into a table and parse through all the SQL being executed against the server. I break apart every "word" in all the SQL and count each word as a potential object name. A stored procedure then executes after this that updates the meta data that was collected in the snapshot step. Keep in mind that this method has a short-coming and that is when you have a table named LA. If anywhere in your code there is a column called LA then its going to assume that the table LA is used. But I wasnt going to spend 6 months writing SQL parsing code. I think its "close enough"

    When complete you will have a table that contains all the metadata you need to delete objects that are no longer used.

    If you guys want the process let me know and I can email out the SSIS package and database scripts.

    Below is the poorly formatted example of the results you will get:

    ServerName DatabaseName ObjectName ObjectType UsedInProcedures UsedInFunctions UsedInViews UsedInTriggers UsedInAdHocQueries

    NHQRPTSQL004 ATT_WHSE sysconstraints V No No No No Yes

    NHQRPTSQL004 ATT_WHSE syssegments V No No No No No

    NHQRPTSQL004 AYS_Asurion Customers U No No No No Yes

    NHQRPTSQL004 AYS_Asurion Inventory U No No No No Yes

    NHQRPTSQL004 AYS_Asurion Invoices U No No No No Yes

    NHQRPTSQL004 AYS_Asurion LedgerInvoices U No No No No Yes

    NHQRPTSQL004 AYS_Asurion PurchaseOrders U No No No No Yes

    NHQRPTSQL004 AYS_Asurion RepairOrders U No No No No Yes

    NHQRPTSQL004 AYS_Asurion RODetails U No No No No Yes

    NHQRPTSQL004 AYS_Asurion Shipping U No No No No Yes

    NHQRPTSQL004 AYS_Asurion syssegments V No No No No No

    NHQRPTSQL004 AYS_Asurion sysconstraints V No No No No Yes

    NHQRPTSQL004 AYS_Asurion MSreplication_subscriptions U No No No No Yes

    This is just the results of a view I wrote. There is enough metadata to drill into exactly what objects use each object 🙂

  • TomThomson

    SSC Guru

    Points: 104773

    This sort of thing scares me rigid. The warning at the end is nowhere near strong enough.

    Whatever sort or profiler run you use, or whatever sort of trace, you have to make extremely sure that you don't delete SPs that are essential.

    There are often SPs that are called only when something has gone horribly wrong, as part of error recovery. SPs that are there just in case - you don't want the circumstances in which they would be called to arise, but if those circumstances do arise you had better have the SPs that are needed to deal with them. The extreme case of this is SPs that are part of disaster recovery. Unless you are unlucky and something pretty nasty happens while you are monitoring none of these will show up in your traces or profiler output.

    There may be SPs that run annually, SPs that run quarterly; SPs that run only on DBMS startup - you don't want to get rid of those just because your production system has had no down time for a few months. The size of the profiler files that you would get even to catch SPs that only run monthly (and no, you can't assume they all run on the first or last working day of the month and get away with just a few days monitoring) would be enormous, looking at all that in excel is crazy: do some aggregation in the DB to get the size down, import the results of that into excel if you need to; it's not generally a good idea to use a pin hammer (excel) to where you need a sledge hammer (SQLS).

    The basic rule has to be: never delete an SP unless (a) you know what id does and (b) you are certain that it is not required in the system. Think you can be certain by searching through the source code of every app that is ever allowed to run on your system? If so, think again, unless you have no third party apps for which you don't have the source and you know that the CIO, the CFO, the COO, the CEO, and the head of Marketting have carefully informed you of all the apps that they and their staff sometimes want to use on the system (perhaps once in a couple of years) and have accepted responsability for any consequences of their lists being incomplete.

    Tom

  • neerajkhur

    Newbie

    Points: 7

    nice article but don't you think, creating a new temp table and simply inserting store proc name and time it has been used will do the job. we might have to run a trigger after every query in the database but running this for one day and putting db back to normal next day will do the job and I believe, getting data for 24 hrs testing of normal running of application will do much better than job than running it for 30 min although i don't mind 30 min too.

  • ronnydahl

    SSC Enthusiast

    Points: 130

    Thanks for feedback, Mister.Magoo! I didn't look at that particular article before posting but I'll put it on my todo list and review it soon! 🙂

  • ronnydahl

    SSC Enthusiast

    Points: 130

    Thank you all for great feedback! As this was my first article I haven't refined it nearly good enough. Nor have I done sufficient research into alternative methods of solving the case at hand.

    I will absolutely rewrite the last part of the article to underline the danger of deleting stored procedures that seems to be unused. I do believe we can all agree on that this article provides some help for small-to-midsize applications but that it doesn't really work well on largescale applications.

    Again, thanks for feedback! 🙂

  • John Waclawski

    SSCommitted

    Points: 1505

    dma-669038 (4/15/2010)


    Great article but in large environments you dont get to run/profile all stored procs at one shot(many times the application will not even call some procs depending on how people are using it, or testing it). We typically go by below DMV based query - it has some limitations, only gives what is in cache since last reboot, but depending on your environment it might work well. We got it from Greg Larsen's article

    http://www.databasejournal.com/features/mssql/article.php/3687186/Monitoring-Stored-Procedure-Usage.htm

    --Stored procedure usage since last reboot

    SELECT DB_NAME(st.dbid) DBName

    ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName

    ,OBJECT_NAME(st.objectid,dbid) StoredProcedure

    ,max(cp.usecounts) Execution_count

    FROM sys.dm_exec_cached_plans cp

    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

    where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'

    group by cp.plan_handle, DB_NAME(st.dbid),

    OBJECT_SCHEMA_NAME(objectid,st.dbid),

    OBJECT_NAME(objectid,st.dbid)

    order by max(cp.usecounts)

    I tried this but received the following error when I ran it:

    Msg 102, Level 15, State 1, Line 6

    Incorrect syntax near '.'. [/b]

    Which is referencing this line: CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

    I'm still debugging it but thought I'd throw this out there.

  • bass8117

    SSChasing Mays

    Points: 649

    John Waclawski (4/19/2010)


    dma-669038 (4/15/2010)


    Great article but in large environments you dont get to run/profile all stored procs at one shot(many times the application will not even call some procs depending on how people are using it, or testing it). We typically go by below DMV based query - it has some limitations, only gives what is in cache since last reboot, but depending on your environment it might work well. We got it from Greg Larsen's article

    http://www.databasejournal.com/features/mssql/article.php/3687186/Monitoring-Stored-Procedure-Usage.htm

    --Stored procedure usage since last reboot

    SELECT DB_NAME(st.dbid) DBName

    ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName

    ,OBJECT_NAME(st.objectid,dbid) StoredProcedure

    ,max(cp.usecounts) Execution_count

    FROM sys.dm_exec_cached_plans cp

    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

    where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'

    group by cp.plan_handle, DB_NAME(st.dbid),

    OBJECT_SCHEMA_NAME(objectid,st.dbid),

    OBJECT_NAME(objectid,st.dbid)

    order by max(cp.usecounts)

    I tried this but received the following error when I ran it:

    Msg 102, Level 15, State 1, Line 6

    Incorrect syntax near '.'. [/b]

    Which is referencing this line: CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

    I'm still debugging it but thought I'd throw this out there.

    Try changing the compatibility Level on your database. I changed it on one of mine and it got rid of the syntax error.

  • Raymond0

    SSC Enthusiast

    Points: 129

    Checked out the link that dma-669038 posted and there is some good stuff at the site.

    Had to use this today as I am reverse engineering a DB and found this absolutely brilliant.

    Also thanks to Mr G Larsen for the article and the code.:cool:

  • rene.westerveld

    SSCrazy

    Points: 2701

    Is there a similar way to retreive the unused views / functions?

Viewing 12 posts - 16 through 27 (of 27 total)

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