Deleting unused objects (tables, SP, UDF etc) not in use

Question

Deleting unused objects (tables, SP, UDF etc) not in use

kalpit_yellow

Mr or Mrs. 500

Points: 580
More actions
February 26, 2013 at 12:24 pm

#274573

I have task in hand to clean up production database by deleting unused objects. i am thinking to use DMV's to figure out what are the objects in cache and delete remaining object with further analysis.
My question is: do anyone have any script to see all unused objects present on SQL server or when it was executed last?
I am looking for one script for all the objects,
Any suggestion will help,
This query i developed
---Below Query will give all the objects in cache memory
SELECT @@SERVERNAME, 'staging' as DatabaseName, s3.name as ObjectName, s3.type, MAX(s1.max_rows) as rowscount,
CONVERT(DATETIME,GETDATE()),s1.execution_count, s1.total_elapsed_time
FROM sys.dm_exec_query_stats s1
cross apply sys.dm_exec_sql_text(s1.sql_handle) as s2
join sys.objects s3 on ( s2.objectid = s3.object_id )
join sys.indexes i on (s3.object_id = i.object_id)
join sys.schemas sch on(s3.schema_id = sch.schema_id)
WHERE s3.type = 'U'
GROUP BY s3.name, s3.type,s1.execution_count, s1.total_elapsed_time
--Below Query will give all objects including objects not in chache memory
SELECT @@SERVERNAME, 'staging' as DatabaseName, s3.name as ObjectName, s3.type, MAX(s1.max_rows) as rowscount,
CONVERT(DATETIME,GETDATE()),s1.execution_count, s1.total_elapsed_time
FROM sys.dm_exec_query_stats s1
cross apply sys.dm_exec_sql_text(s1.sql_handle) as s2
FULL OUTER JOIN sys.objects s3 on ( s2.objectid = s3.object_id )
join sys.indexes i on (s3.object_id = i.object_id)
join sys.schemas sch on(s3.schema_id = sch.schema_id)
WHERE s3.type = 'U'
GROUP BY s3.name, s3.type,s1.execution_count, s1.total_elapsed_time
Any suggestion???

Viewing 4 posts - 1 through 3 (of 3 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

I suggest you reconsider those queries. 🙂 While it shows all the queries currently in cache, not all plans are cached and not all plans will stay in cache.

Try something like extended events or SQL Audit, monitor for at least a month (longer if some processes run less frequently than that), that will give you a list of the objects that are used. Then you have to consider carefully whether the objects not on that list are not used or just not used often. This isn't going to be a quick process.

Delete with care, make sure you have a backup first.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kalpit_yellow Mr or Mrs. 500 Points: 580 More actions · Answer 2

Excellent advice, since we have some monthly executing queries, i am atleast planning to observe cache for 2 months, and carefully delating, may be renaming object name will help before deleting

Thanks

Lowell SSC Guru Points: 323518 More actions · Answer 3

another thing to consider is to search any sourcecode for object names;

we have plenty of "functionality" that exists in various application, but noone ever uses the functions in real life;

As a result, objects must exist "just in case" someone uses that function, but it might not ever be actually used.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!