Post reply

Unused Indexes in SQLServer2005

  • January 3, 2011 at 4:19 am

    #231643

    Dear All,

    What is the use if we remove the unused indexes?please

    Thanks and Regards,

    Ravi.

  • January 3, 2011 at 4:33 am

    #1268403

    If the indexes truly were completely unused then by removing them you free up some space in the database, reduce the overhead for data modifications, reduce the time for index maintenance.

    Be careful and make sure that the indexes really are completely unused and not just seldom used or you may impact performance of whatever queries did use them.

    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

  • January 3, 2011 at 5:09 am

    #1268406

    Thanks Gail.

    Can you advise me query/script to find the list of indexes with size in SQLServer2005?please

    Regards,

    Ravi.

  • January 3, 2011 at 5:34 am

    #1268412

    Query sys.dm_db_partition_stats. That gives page count per index. A page is 8kB.

    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

  • January 3, 2011 at 5:43 am

    #1268416

    DMV sys.dm_db_index_usage_stats contains the usage info you need.

    BIG REMARK from BOL:

    The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.

    So you should persist this info for a reference period, to be sure you don't drop indexes that are only use rarely or for specific purposes.

    You may first want to disable an index before dropping it !

    Check out this script at SSC:

    http://www.sqlservercentral.com/scripts/Administration/61330/

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • January 3, 2011 at 5:45 am

    #1268418

    here is another try. this query combines both sys.dm_db_index_usage_stats (for index usage) and sys.system_internals_allocation_units (for collecting pages allocated)

    select si.name IndexName, OBJECT_NAME(si.object_id) ObjectName, si.type_desc, internals.total_pages, ius.*

    from sys.indexes si

    inner join sys.dm_db_index_usage_stats ius on ius.object_id = si.object_id and ius.index_id = si.index_id

    inner join sys.partitions sp on si.object_id = sp.object_id

    inner join sys.allocation_units sa on sa.container_id = sp.hobt_id

    inner join sys.system_internals_allocation_units internals on internals.container_id = sa.container_id

    where si.type_desc <> 'HEAP'

    and ius.user_seeks = 0 and ius.user_scans = 0

    order by internals.total_pages desc

  • January 4, 2011 at 1:36 am

    #1268807

    Thank you all for your valid solutions and suggestions.

    Regards,

    Ravi.

Viewing 7 posts - 1 through 6 (of 6 total)

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