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


Identifying Unused Objects in a Database


Identifying Unused Objects in a Database

Author
Message
Leo Peysakhovich
Leo Peysakhovich
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 341
Comments posted to this topic are about the content posted at temp



Frances L
Frances L
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 299

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
SuperDBA-207096
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1581 Visits: 711
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?
WILLIAM MITCHELL
WILLIAM MITCHELL
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1732 Visits: 3039

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.


sheepoo
sheepoo
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 34
Exactly what I was looking for
Robert Davis
Robert Davis
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1904 Visits: 1623
Because it isn't cached.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
Majbou-204206
Majbou-204206
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 524
"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
WILLIAM MITCHELL
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1732 Visits: 3039

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
Leo Peysakhovich
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 341

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
Leo Peysakhovich
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 341
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.



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