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 12»»

Identifying unused tables and stored procedures since last six month Expand / Collapse
Author
Message
Posted Tuesday, February 10, 2009 4:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 29, 2012 10:00 AM
Points: 28, Visits: 126
How to know when the data of tables were viewed in mssql 2000 and mssql 2005? and also when the stored procedures were executed?

Basically I want to know since when tables and stored procedures are not used. Based on this I am going to drop the table and procedure which are not used since 6 months.

Identifying unused tables and stored procedures since last six month.

Please help me.

Post #653564
Posted Tuesday, February 10, 2009 5:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 12,905, Visits: 32,161
You can't automatically do it, some decisions have to be made. Also remember a functionality may exist that would use a table in the database, but just hasn't been used by your users YET. arbitrarily dropping tables without reviewing the code of your application is not a responsible way to tackle this kind of issue.

To at least identify potential tables to drop, though,
Here's how i would do it:
restore a database from 6 months ago.
compare the count(*) of all rows in OldDB to CurrentDB. and tables that have the same count potentially are not used (or are lookup tables that never change, like a list statuses,counties or states)

of those tables that had the same count, and also are not lookup tables,
I would examine their results from one of the many "Compare Two Databases" scripts from here on SSC.
those that don't have changes might meet the drop criteria after the application code is reviewed.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #653576
Posted Tuesday, February 10, 2009 7:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:26 AM
Points: 13,884, Visits: 28,278
This won't help you now, but another option available is to collect, and aggregate over time, the procedure calls by running a very lean server-side trace to capture queries run on the database in question. Then you can figure out which procedures are called, which queries are called, and by inference from those procs & queries, which tables are accessed.

But the data isn't built into the system, so you have to find a way to create it for yourself.

Another option for building the data would be regularly capture the information available in the cache by querying the DMV's such as sys.dm_exec_requests or sys.dm_exec_query_stats. You can then aggregate that information into a table somewhere and build up reports over time similar to what you would get from trace. However, since this information is only what is currently in the cache, you may miss queries or procedure calls depending on the frequency of your requests to the DMV and the volatility of the data in your cache.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #653695
Posted Wednesday, February 11, 2009 12:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 29, 2012 10:00 AM
Points: 28, Visits: 126
Thanks for reply.

I don't want to use profiler and create job for getting list of unused tables and stored
procedures.
Is there any other way to get list of tables and sps which are not being used since 6 months in mssql 2000 and mssql 2005?

Post #654480
Posted Wednesday, February 11, 2009 5:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:26 AM
Points: 13,884, Visits: 28,278
As Lowell and I both said... No.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #654677
Posted Monday, February 16, 2009 9:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 29, 2012 10:00 AM
Points: 28, Visits: 126
I got below mentioned query which will give list of unused table since the SQL services started.It is for MSSQL 2005. Is there any way to get list of unused stored procedures?


SELECT DISTINCT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),I.OBJECT_ID
FROM SYS.INDEXES AS I
INNER JOIN SYS.OBJECTS AS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.OBJECT_ID
NOT IN (SELECT DISTINCT I.OBJECT_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S ,SYS.INDEXES AS I
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = DB_ID(db_name()))
Post #658189
Posted Tuesday, February 17, 2009 4:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:26 AM
Points: 13,884, Visits: 28,278
You can query the cache to see what procedures are in it and compare that against the list of procedures. However, just because a procedure is not currently in cache doesn't mean it doesn't get used. It just means it hasn't been used recently, but may have just aged out of the cache prior to your query.

If you think it'll help, with very serious caveats in place, I'd try going against sys.dm_exec_procedure_stats. This will give you the object ID and it marks whether or not it's a stored procedure.



----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #658376
Posted Wednesday, February 18, 2009 8:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
One time I was faced with an old database system and had to figure out which SPs are not being used.
There is no easy and nice way of getting this information so I decided to create my own way of gathering this information.

I created log table which held the names of the stored procedures in my system, number of times used and the time last used. I also created the SP that updated this table whenever called. This SP took one parameter - the name of the SP it was called from and updated the proper record in the log table.

Then the messy part kicks in. Each SP in the database has to be modified to call the log SP. You can do it by hand if your database does not have many SPs or script all SPs and write simple code in VB or C# to modify the sripts to include the call to the log SP.

I don't know if this is going to work for you but I figured a suggestion can not hurt.

The database access was limited to SP calls only so I did not have to deal with the table usage directly.


---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #659505
Posted Wednesday, February 18, 2009 9:08 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 12:34 PM
Points: 31,181, Visits: 15,626
A trace is the best way to figure out what's being used, and that won't tell you what isn't being used, but it will help. You do need to run it over time to make sure that you get most procs.

Even then, what's the point? Are you looking to remove procedures? They don't take up much space and don't impact the system if they're not being used.

If it's cleanup, I think you would be better off spending time doing something else. If you have other reasons, you can always rename some procs and see if anything breaks. That way you'll have the code around and can "restore" them if there's an issue.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #659531
Posted Wednesday, February 18, 2009 11:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 8, 2014 2:59 AM
Points: 119, Visits: 401
I totally agree with Steve on that one. The best way is to set up a trace. About the information about the last six months, I don't think there is a way of getting this information either.
Maybe something that could be interesting for you is the "Enable Trace" option. It by default is set up to obtain DDL statements but you could add some specific events, like sp's execution.
The catch with this option is that you would have to see if you suffer any performance issues. It's not supposed to be as heavy as profiler, it's actually supposed to be a lot lighter but you would have to try it out.
Hope it helped in any way.
Post #659656
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse