Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
JANNIEE
JANNIEE
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 25
Great article. I found it help full.

Wish something similar was there for views.
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2266 Visits: 7824
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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • brett.hawton
    brett.hawton
    Forum Newbie
    Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

    Group: General Forum Members
    Points: 8 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
    marykdba
    marykdba
    Grasshopper
    Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

    Group: General Forum Members
    Points: 11 Visits: 114
    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.
    SQLRNNR
    SQLRNNR
    SSC-Insane
    SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

    Group: General Forum Members
    Points: 21071 Visits: 18259
    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

    tony.turner
    tony.turner
    SSC Rookie
    SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

    Group: General Forum Members
    Points: 35 Visits: 180

    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
    irozenberg
    SSC Rookie
    SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

    Group: General Forum Members
    Points: 42 Visits: 145
    Well done!

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

    Cheers
    Jeff Moden
    Jeff Moden
    SSC-Forever
    SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

    Group: General Forum Members
    Points: 44983 Visits: 39870
    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.
    Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
    Although change is inevitable, change for the better is usually not.
    Just because you can do something in PowerShell, doesnt mean you should. Wink

    Helpful Links:
    How to post code problems
    How to post performance problems
    Forum FAQs
    Craig Lucas
    Craig Lucas
    Forum Newbie
    Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

    Group: General Forum Members
    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 Smile
    TomThomson
    TomThomson
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10700 Visits: 11991
    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

    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search