Any query for selecting unwanted indexes ?

  • Hi all,

    Am working on my company product .

    In our data base from long back my seniors created many indexes on many tables.

    IS there any query find out un wanted index and duplicate index and un used index ?

    And i wanted to know size of index , b'coz it should not cross 900 bytes.

    so that i can clean my data base. and i can boost the performance !!!!

    Please help me regarding this.

    Regards,

    Ravi.

  • Try looking at sys.dm_db_index_usage_stats

    Although a word of warning - although an index may not be used 'frequently', it doesnt mean that it's not valid!!

    Do you have the data model of your database? This might be a good start to identifying where indexing is required, and for what purpose.

    _____________________________________________________________________
    Disclaimer - The opinions expressed by the mouth are not necessarily those of the brain

  • Cleaning up unused indexes is an OK use for your time, but, you need to be aware, sys.dm_db_index_usage_stats only records information since the last time the server was restarted (and several other events) so while it will show you indexes that have not been used, you need to be very aware of the time frame under which it's operating. You might see an index that hasn't been accessed for three weeks, but that's because it's an index for the monthly report that the boss runs or something along those lines. You can't simply run a script, identify & drop indexes. You need to test each drop to validate it doesn't adversely affect the system. You need to have monitoring in place on the system so you can see if dropping an index has adversely affected the system.

    Also, if you're really concerned with excessive indexes, you should be monitoring your inserts & updates to see if they are slowing down because of indexes. Reads won't be affected by having too many indexes to choose from.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Reply to your first que to find out unused index.

    found from Technet library

    you can try this

    SELECT SCH.name + '.' + OBJ.name AS ObjectName

    ,OBJ.type_desc AS ObjectType

    ,IDX.name AS IndexName

    ,IDX.type_desc AS IndexType

    FROM sys.indexes AS IDX

    LEFT JOIN sys.dm_db_index_usage_stats AS IUS

    ON IUS.index_id = IDX.index_id

    AND IUS.object_id = IDX.object_id

    INNER JOIN sys.objects AS OBJ

    ON IDX.object_id = OBJ.object_id

    INNER JOIN sys.schemas AS SCH

    ON OBJ.schema_id = SCH.schema_id

    WHERE OBJ.is_ms_shipped = 0

    AND OBJ.type IN ('U', 'V')

    AND IDX.type > 0

    AND IDX.is_disabled = 0

    AND IDX.is_primary_key = 0

    AND IDX.is_unique = 0

    AND IUS.object_id IS NULL

    ORDER BY ObjectName

    ,IndexName;

  • To know the size of Index you can try this for particular Database

    SELECT

    i.name AS IndexName,

    SUM(page_count * 8) AS IndexSizeKB

    FROM sys.dm_db_index_physical_stats(

    db_id(), object_id('dbo.TableName'), NULL, NULL, 'DETAILED') AS s

    JOIN sys.indexes AS i

    ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id

    GROUP BY i.name

    ORDER BY i.name

  • Thank you very much all for your valuble suggestions and solutions 🙂 .

    Regards,

    Ravi.

  • Be carefull using the example above which usese the "detailed" option. As this looks at each leaf node perfromance can be incredibly slow. Changing this to simple whilst less accurate will return results much faster and use less resources.

    MCITP SQL 2005, MCSA SQL 2012

  • Hope this helps:

    http://blog.sqlauthority.com/2008/02/11/sql-server-2005-find-unused-indexes-of-current-database/

    http://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • Sapen (12/16/2011)


    Hope this helps:

    http://blog.sqlauthority.com/2008/02/11/sql-server-2005-find-unused-indexes-of-current-database/

    http://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/%5B/quote%5D

    This is far more complex than just running scripts, please watch the 3 vids I posted.

  • Okay

    Thank you very much

    Ravi.

Viewing 11 posts - 1 through 10 (of 10 total)

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