Identifying Unused Objects in a Database

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    Comments posted to this topic are about the content posted at temp

  • Frances L

    SSCarpal Tunnel

    Points: 4727

    I used

    select name, id from sysobjects where name = 'get_emp_list_FY06'

    to get my objid. then I run this

    select bucketid, cacheobjtype, objtype, objid, dbid     

     from master.dbo.SYSCACHEOBJECTS

     where  objid = 1718297181

    it return

    bucketid    cacheobjtype      objtype  objid       dbid  

    ----------- ----------------- -------- ----------- ------

    (0 row(s) affected)

     

    Why ? Thanks.

     

  • SuperDBA-207096

    SSCrazy Eights

    Points: 8176

    This one scares me a little bit; if you have some procs that are run once a quarter or once a month would they show up in the cache?

  • sheepoo

    SSC Eights!

    Points: 959

    Exactly what I was looking for

  • WILLIAM MITCHELL

    SSChampion

    Points: 13710

    I wouldn't trust this post either. Now that the ASP and .net junkies can reach in and do stuff via the CLR, it's anybody's guess. And it is still so vexing that MS did not see fit to add attributes like "last modified" and "last accessed" in SS 2005 - Windows has had that capability for a long time.

    AFAIK the only sure way to track when an sp is invoked would be to add tracking code e.g. write to a table when it is executed.

  • Robert Davis

    One Orange Chip

    Points: 28027

    Because it isn't cached.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Majbou-204206

    Grasshopper

    Points: 19

    "select name, id from sysobjects where name = 'get_emp_list_FY06'"

    calls the sysobjects table, so your query puts in cache the SYSOBJECTS table not your sp/fn.

  • WILLIAM MITCHELL

    SSChampion

    Points: 13710

    One of my pet peeves is when somebody will read a question and either they don't understand it, or they don't know the answer -- does that stop them? Nope - they just reply to a different question that they do know the answer to. Human nature, I guess. End of rant.

    If you re-read Leo's original article, he said:

    "In my company the task becomes a little bit easier by knowing the fact that any database can be accessed only via call to a stored procedure. E.g. I have to split the task to the 2 sequential subtasks: ..."

    Lucky Leo. In his case, he could very easily add some kind of logging feature that records when an sp is run, and then (over the course of time) use a process of elimination to find the sp's that never get called.

    Considering that SQL Server is notorious for omitting relevant object info such as "last modified" and "last accessed" dates (which Windows itself has had for ages) we're just a little bit stuck. Even perusing the SQL-DMO library does not reveal anything that I can find relating to the topic at hand. It really gets tiring when you have to drop-and-create procedures to track when changes happen.

    The issue becomes even more complex when you consider that an external application can be allowed to touch objects directly, so even the internal SQL tracking mechanisms such as dependencies don't help a bit. I can think of one customer's web app that uses SQL Server, however there is also an Access database that connects in there, along with Excel gurus who use the Data tools to run their own specialized analyses. And let's not forget an e-commerce link to both their physical and online stores.

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    You forgot actually run the function or proc first before test it in cache memory

    select name, id from sysobjects where name = 'get_emp_list_FY06'

    exec get_emp_list_FY06

    Then:

    select bucketid, cacheobjtype, objtype, objid, dbid     

     from master.dbo.SYSCACHEOBJECTS

     where  objid = 1718297181

     

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    Yes if you get cache in a short period time after they run. I was running tests every 2-3-5 minutes for 2 month. As I said you will have just the report of objects that MOST likely not used. Then additional analysis may required.

  • SuperDBA-207096

    SSCrazy Eights

    Points: 8176

    Right.. I guess you'd have to look real close at each object and make really sure it's not being used.  It could point you in the right direction I guesss...  You could identify for sure that an object is used, but not be 100% certain an object isn't used...

     

     

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    Thanks, that you get attention to my wording (not many people captured it)

    There are several tricks. When you define the suspicious objects that may not be used, you can add simple trigger for select, insert, update with simple insert to log table with info about the table beeng accessed, date, and user. In addition, I am lucky because we adapt the policy and management supporting me with it that all calls (even simple reports with 1 SQL statement) only by stored procedures.

    )

  • Frances L

    SSCarpal Tunnel

    Points: 4727

    Yesterday I run

    select bucketid, cacheobjtype, objtype, objid, dbid     

     from master.dbo.SYSCACHEOBJECTS

     where  objid = 1718297181

    at 10:30 am.

    I do not understand why it keep lock the other process.

     

  • Frances L

    SSCarpal Tunnel

    Points: 4727

    I do not understand I run the process

    select bucketid, cacheobjtype, objtype, objid, dbid     

     from master.dbo.SYSCACHEOBJECTS

     where  objid = 1718297181.

     

    It still lock the  process. My other scheduled task aplication in the server could not run due to the lock. Thanks.

  • Robert Davis

    One Orange Chip

    Points: 28027

    Try running this instead:

    select bucketid, cacheobjtype, objtype, objid, dbid     

     from master.dbo.SYSCACHEOBJECTS with(nolock)

     where  objid = 1718297181


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

Viewing 15 posts - 1 through 15 (of 21 total)

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