Analysing Indexes Summary

·         I’ve put the work I’ve done on using the dmvs to analyse indexes onto my web site – the links to the pages are underneath. Trying to post large documents to the blog is very difficult, and probably not what a blog was intended for!

·         I need to do more work on the analysis of the operational stats and I will publish this in due course. The basic principles that apply to the analysis of sys.dm_db_index_usage_stats can be applied to sys.dm_db_index_operational_stats .

·         There have been a number of posts and articles indicating how easy it is to find unused indexes using the dmvs – what I would say is to be very careful, for a production system you must capture dmv data into local tables and remember that the data is cumulative.

·         What I’ve found in large production systems is that you cannot guarantee that data on every index is held in the dmvs at all times, I cannot reproduce this in test environments, but in some ways this is understandable, see section 20

·         Make sure that you consider very very carefully your work cycle, remember that if you have month or quarter ends then you need to capture data over these periods too.

·         In my tests a full update statistics ( not sp_updatestats ) was most likely to force index data into the views, in the databases I was monitoring I was unable to rebuild every index every day and where systems are close to 7 x 24 and databases getting  to terabyte size you can perhaps understand why.

·         In my case I started the analysis because the system I was attempting to tune made extensive use of client side cursors, had no stored procedures and was very busy.

·         Part 3 finishes the work on the operational stats and there are some useful queries for you to use in the whole set – laterly I expanded some of the analysis to cover partitioned tables. It was pointed out to me that I’d not allowed for schema’s in my work or xml indexes, I will try to remedy this in due course.

I’ve published the pages as mhtml but if you’d like word versions then drop me an email.

·         Analysing Indexes Part 1  - Introduction to the index dmvs

·         Analysing Indexes part 2 – Collecting index data, examining statistics, empty tables

·         Analysing Indexes part 3 – Expanding upon parts 2 and 3, looking at index size

·         Analysing Indexes part 4 – This considers the sizing of indexes and possible selection of clustered index. Did you know for example that an index on a nullable column adds an overhead of 3 bytes per index entry?

Published Monday, February 18, 2008 7:53 PM by GrumpyOldDBA

Comments

# re: Analysing Indexes Summary

Monday, February 18, 2008 8:57 PM by JasonMassie

I cannot get the pages to load. I can get to the domain and navigate to http://www.grumpyolddba.co.uk/sql2005/sql2005.htm but links no worky.

# re: Analysing Indexes Summary

Monday, February 18, 2008 9:30 PM by JasonMassie

Nevermind. I got it. Looks like good stuff.

# re: Analysing Indexes Summary

Tuesday, February 19, 2008 12:12 PM by GrumpyOldDBA

sorry it takes a bit longer to load the mhtml pages than std html.