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?