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

Query Help.. Expand / Collapse
Author
Message
Posted Wednesday, January 16, 2013 10:08 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 1:44 PM
Points: 219, Visits: 829
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...
Post #1408139
Posted Thursday, January 17, 2013 12:23 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1408180
Posted Thursday, January 17, 2013 12:27 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 21,747, Visits: 15,441
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1408183
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse