Unused Procedures and tables

Question

Post reply

Unused Procedures and tables

jchandramouli

SSCommitted

Points: 1772
More actions
April 6, 2009 at 5:34 am

#93452

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

Viewing 6 posts - 1 through 5 (of 5 total)

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

Florian Reischl SSC-Dedicated Points: 37299 More actions · Answer 1

Florian Reischl

SSC-Dedicated

Points: 37299

April 6, 2009 at 5:36 am

#972433

Hi

Could you explain "unused tables"?

Greets

Flo

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

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/

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software