SQLServerCentral / Discuss Content Posted by Leo Peysakhovich / Article Discussions / Article Discussions by Author / Identifying Unused Objects in a Database / Latest Posts

RE: Identifying Unused Objects in a Database

Leo Peysakhovich — Sun, 02 Aug 2009 13:26:01 GMT

[quote][b]BobMcC (7/30/2009)[/b][hr]Sorry to resurrect this old thread, but I"m in the middle of a project to upgrade our sql 2000 dbs to sql 2008. I'd like to also use this as an opportunity to get rid of any procedures that aren't being used. For the most part, I like the solution. I'm curious to hear everyone's input though about the frequency he ran the update code. Every 15 minutes? Ouch! Can somoene explain to me why this couldn't just as easily be run every hour or every 3 or 4 hours. If I were to run that code on my dbs every 15 minutes for a month, and have a whole other issue to contend with!:w00t:[/quote]There is no harm to output to stand along table with identity column primary key in another database every 15 minute.Even it will be millions of rows identity will always pack the rows and place it at the end of the page. I did it for 2 months and run solution every 2-3 minutes without any issues.

RE: Identifying Unused Objects in a Database

BobMcC — Thu, 30 Jul 2009 13:54:09 GMT

Sorry to resurrect this old thread, but I"m in the middle of a project to upgrade our sql 2000 dbs to sql 2008. I'd like to also use this as an opportunity to get rid of any procedures that aren't being used. For the most part, I like the solution. I'm curious to hear everyone's input though about the frequency he ran the update code. Every 15 minutes? Ouch! Can somoene explain to me why this couldn't just as easily be run every hour or every 3 or 4 hours. If I were to run that code on my dbs every 15 minutes for a month, and have a whole other issue to contend with!:w00t:

RE: Identifying Unused Objects in a Database

gfinzer — Wed, 17 Sep 2008 09:15:56 GMT

I saw this tool that finds Unused Stored Procedures:[url=http://www.kellermansoftware.com/p-35-unused-stored-procedures.aspx][/url]

RE: Identifying Unused Objects in a Database

Tim Brown-465757 — Wed, 23 Jul 2008 10:47:56 GMT

Okay, this works for SS2005. What exists to do the same kind of thing in SS2000?

RE: Identifying Unused Objects in a Database

Kevin Fairchild-479614 — Tue, 11 Sep 2007 09:28:00 GMT

I did a slight alteration to the way this works in order to better do what I wanted... Rather than doing the insert each time and then relying on the query at the end to determine if it had been in the cache at one point, I insert up front and then have the job delete from the table any references to that object if it's currently in the cache. This way, whatever is in the table at the end of my monitoring period are things that weren't in the cache at some point during the period.Granted, it isn't fool-proof going off the cache, but it at least helps narrow things down.--Kevin Fairchild

RE: Identifying Unused Objects in a Database

Leo Peysakhovich — Tue, 12 Sep 2006 16:07:00 GMT

Please try to read article carefully before trying.

#1. You can use "nolock" hint

#2. I did exclude where clause from the tests I was doing. Insert all rows to the temporary table and then get the objectid you are looking for. I show it for one object as an example at the beginning of the article. But in reality I never done it on the object by object bases.

Always all records from syscache table and then make an analysis

RE: Identifying Unused Objects in a Database

Robert Davis — Tue, 12 Sep 2006 11:37:00 GMT

Try running this instead:

select bucketid, cacheobjtype, objtype, objid, dbid from master.dbo.SYSCACHEOBJECTS with(nolock) where objid = 1718297181

RE: Identifying Unused Objects in a Database

Frances L — Tue, 12 Sep 2006 05:59:00 GMT

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.

RE: Identifying Unused Objects in a Database

Frances L — Tue, 12 Sep 2006 05:53:00 GMT

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.

RE: Identifying Unused Objects in a Database

Leo Peysakhovich — Mon, 11 Sep 2006 18:42:00 GMT

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.

)

RE: Identifying Unused Objects in a Database

SuperDBA-207096 — Mon, 11 Sep 2006 18:40:00 GMT

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...

RE: Identifying Unused Objects in a Database

Leo Peysakhovich — Mon, 11 Sep 2006 18:30:00 GMT

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.

RE: Identifying Unused Objects in a Database

Leo Peysakhovich — Mon, 11 Sep 2006 18:27:00 GMT

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

RE: Identifying Unused Objects in a Database

WILLIAM MITCHELL — Mon, 11 Sep 2006 10:31:00 GMT

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.

RE: Identifying Unused Objects in a Database

Majbou-204206 — Mon, 11 Sep 2006 10:07:00 GMT

"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.

RE: Identifying Unused Objects in a Database

Robert Davis — Mon, 11 Sep 2006 09:54:00 GMT

Because it isn't cached.

RE: Identifying Unused Objects in a Database

sheepoo — Mon, 11 Sep 2006 07:27:00 GMT

Exactly what I was looking for

RE: Identifying Unused Objects in a Database

WILLIAM MITCHELL — Mon, 11 Sep 2006 07:27:00 GMT

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.

RE: Identifying Unused Objects in a Database

SuperDBA-207096 — Mon, 11 Sep 2006 07:07:00 GMT

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?

RE: Identifying Unused Objects in a Database

Frances L — Mon, 11 Sep 2006 05:42:00 GMT

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.

Identifying Unused Objects in a Database

Leo Peysakhovich — Tue, 29 Aug 2006 10:41:00 GMT

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