January 16, 2013 at 10:08 pm
Hi guys,
I am working on task where i am gonna clean my DB. I would like to know is there any way i can find out Unnecessary Index that i don't need it. Unnecessary SP,Unnecessary Function and Unnecessary table that never used. Please guide me.
Thank You...
January 17, 2013 at 12:23 am
Unsed indexes/Sp/tables ...this task seems endless and startless for me.
Why ?
because how will and Who will decide which indexes/tables are UNUSED. how will you assure DBA, DEV or other people that the indexes you are going for drop will not be used in FUTURE.and Why you think there are burden ? space constraint ? then that would be your judgement call and what to drop (keep the future consequences in mind along with recovery time)
And if not to drop, Script out the indexes or SP and keep it in other location and for tables move them to another location.
Anyways this is what you need :
Unused Indexes
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = @dbid)
ORDER BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC
GO
For unused Stored proc http://www.sqlservercentral.com/articles/SQL+Server/69676/
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 17, 2013 at 12:27 am
I think it would be a good idea to first familiarize yourself with the data and processes before trying to assess which tables, procs and indexes are unused. As you familiarize yourself, you will start to see what is being used and not and be better able to address issues related to unuse because you know the data better.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply