Query Help..

  • 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...

  • 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;-)

  • 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