Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

How to find unused Stored Procedures in SQL Server 2005/2008 Expand / Collapse
Author
Message
Posted Thursday, April 15, 2010 8:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 19, 2011 3:30 AM
Points: 1, Visits: 25
Great article. I found it help full.

Wish something similar was there for views.
Post #904104
Posted Thursday, April 15, 2010 9:10 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:49 PM
Points: 1,796, Visits: 5,797
Thanks for the interesting Article!

I wonder did you ever take a look at this blog post about using DMVs to analyse store proc usage ?
Five DMV Queries That Will Make You A Superhero!

The first example in that post shows how to query the dmvs for a list of the most frequently used stored procs.... it seems quite interesting.

Edit: corrected url


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #904120
    Posted Thursday, April 15, 2010 9:53 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Tuesday, December 7, 2010 2:06 PM
    Points: 4, Visits: 56
    I, as some of the other posts here have suggested, would go for repeated calls to the DMV over a long period of time (a few weeks at the least to find SP's called once weekly or once monthly).

    If you are going to use trace then at many sites the RPC:Starting event should also be included as well as the SP:starting event you chose.

    Nice article!

    Brett Hawton
    Idera Product Architect
    Post #904163
    Posted Thursday, April 15, 2010 11:19 AM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Monday, June 23, 2014 8:03 AM
    Points: 11, Visits: 106
    I was excited when I saw the title of the article, I thought perhaps SQL Server was keeping it's own data on when a stored procedure was last invoked, buried deep in the system area. In my case this wouldn't be easy to implement as we have several databases at my company which may use each other's stored procedures, so running one application won't do it.

    What I have done in the past is a bit tedious, involving the source code for all the applications we have and all the reports. My stored procedure naming conventions make it easy to identify these objects, so I just have a "spider" program scanning everything and spitting out the name of the stored procedures. Then, everything that is not in the result list gets renamed temporarily with a chosen prefix until it is determined that indeed, it is no longer being used. Then it is deleted.

    Your solution would work well for small applications but in a highly complex database environment it could be tricky.
    Post #904240
    Posted Thursday, April 15, 2010 12:40 PM


    SSCoach

    SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

    Group: General Forum Members
    Last Login: Today @ 3:24 PM
    Points: 17,812, Visits: 15,737
    I would rather rely on the DMVs to track proc usage. A trace for 30 minutes would be inadequate in every environment I have been in. We also need to throw out some caution concerning objects that only get used once a month or once a year.

    Granted the DMVs only contain information since last reboot - that would cover a lot more ground and have less holes in regard to finding unused procs. I could see using a query against the DMVs in a view that you could query from excel - that would be highly helpful.




    Jason AKA CirqueDeSQLeil
    I have given a name to my pain...
    MCM SQL Server, MVP


    SQL RNNR

    Posting Performance Based Questions - Gail Shaw
    Post #904308
    Posted Thursday, April 15, 2010 1:03 PM
    SSC Rookie

    SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

    Group: General Forum Members
    Last Login: Friday, May 30, 2014 4:15 AM
    Points: 25, Visits: 170

    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

    Post #904325
    Posted Thursday, April 15, 2010 6:38 PM
    SSC Rookie

    SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

    Group: General Forum Members
    Last Login: Wednesday, September 12, 2012 8:44 PM
    Points: 30, Visits: 145
    Well done!

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

    Cheers
    Post #904516
    Posted Thursday, April 15, 2010 8:08 PM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 11:30 PM
    Points: 35,363, Visits: 31,900
    First up I start with the SQL Server Profiler with a slightly changed version of the "SP_Counts" template. After my modifications I have based the template on the "SP:Starting" event with the following columns:

    ObjectName (name of the Stored Procedure)
    Duration (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)
    EndTime (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."

    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #904529
    Posted Friday, April 16, 2010 9:57 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Tuesday, February 7, 2012 12:31 PM
    Points: 6, Visits: 103
    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 :)
    Post #905011
    Posted Saturday, April 17, 2010 4:37 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: 2 days ago @ 11:12 AM
    Points: 7,791, Visits: 9,545
    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
    Post #905489
    « Prev Topic | Next Topic »

    Add to briefcase ««123»»

    Permissions Expand / Collapse