April 6, 2009 at 5:34 am
Hi,
I have two questions.
1. Is there any way that we can get the list of unused procedures and tables in a database.
2. Is there an impact of having unused tables and procedures in the DB.
Thanks in advance
"I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin
April 6, 2009 at 5:36 am
Hi
Could you explain "unused tables"?
Greets
Flo
April 6, 2009 at 6:26 am
If by unused you mean that no one activates the procedures and no one runs any DML statements on the tables (including select statements), then the impact would be very low. Depending on their size, unused tables might have an impact on disk space and on maintenance jobs that you run on the database, but that’s about it.
You can try and find out tables that are not being used by querying SYS.DM_DB_INDEX_USAGE_STATS view. Bare in mind that the data in this view is cleared each time that the server restarts, so you should make sure to check the usage using this view at least a month after the server was rebooted.
I don’t know of a way to find out about stored procedures that are not being used.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 6, 2009 at 7:29 am
There's no 100% sure way to verify that things are not used in your database. As others have outlined, it's possible to see whether or not things have been accessed recently, but what about tables or procs that are used once a month or once a year. Unless you're checking the cache immediately after they get used, you're unlikely to see them.
To check for stored proc usage, you can query the sys.dm_exec_procedure_stats dmv. It will show what's currently in cache. Check it two or three times a day (depending on how volatile your procedure cache is) for a couple of weeks. You'll know which procedures are being accessed and which are not. But that doesn't mean the ones that are not are NEVER accessed. You could also try running a server side trace for a couple of weeks. You'll get a lot of data, so be sure you know how to do that before you start. Then check for procedure calls to determine which ones are not being used.
Either way, you have to account for the caveat I opened with.
Procedures sitting on the server aren't inherently harmful. However, they could be considered a security threat if they do things or access data that 's not supposed to be accessed. Tables... as noted earlier, storage & maintenance on stuff that isn't used adds cost to the db. Also, if something is really old, not used, not maintained and users access it, they might get bad data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 6, 2009 at 10:13 pm
Thanks everyone.
"I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin
April 8, 2009 at 10:38 am
The best way to find that is verifying with Consumers who are using your database.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply