Transcript Slide Deck
Analysing Indexes SQLBits 6th October 2007 © Colin Leversuch-Roberts Kelem Consulting Limited September 2007 Who am I ? Colin Leversuch-Roberts aka Grumpy Old DBA Kelem Consulting Limited www.kelemconsulting.co.uk http://sqlblogcasts.com/blogs/grumpyolddba/default.aspx © Colin Leversuch-Roberts Kelem Consulting Limited September 2007 Analysing Indexes • How to gather the statistical information about database indexes • What to do with it • How to use it to your advantage • How big is that index • Missing Indexes 3 © Colin Leversuch-Roberts Kelem Consulting Limited September 2007 Analysing Indexes WHY ? •Documentation •Controlled environments •Change Requests •Change Advisory Board Meetings •Urban Myths and would be DBA’s 4 © Colin Leversuch-Roberts Kelem Consulting Limited September 2007 Analysing Indexes • No real demo’s • My laptop can’t process a 400 million row table live • Background • ERP System , nearly 8000 indexes • Would you believe how this application works? 5 © Colin Leversuch-Roberts Kelem Consulting Limited September 2007 Analysing Indexes Some typical tables 6 © Colin Leversuch-Roberts Kelem Consulting Limited September 2007 Analysing Indexes Some typical indexes All unique ? I think not ! 7 © Colin Leversuch-Roberts Kelem Consulting Limited September 2007 Where to start • System views • Data not persisted • We must capture and store this data Operational cost on indexes ( assuming no RFI ) ** ( No partitioning ) ** 8 © Colin Leversuch-Roberts Kelem Consulting Limited September 2007 Tools of the trade dbo.sysindexes This is the SQL 2000 system table taken forward as a view. This table may not be supported going forward. sys.dm_db_index_physical_stats This is actually a function and replaces dbcc showcontig sys.indexes 2005 system table contains property information only for each index. sys.dm_db_index_usage_stats System view which records the access usage of every index in the database We can view the number of times this index has been used, the type of access and the last access date sys.dm_db_index_operational_stats System function which records the operational cost of access to the index We can view information such as the number of pages, locks, latches and waits. sys.objects Contains information on database objects sys.dm_db_missing_index_details System view which stores information on indexes the optimiser considers are missing. sys.dm_db_missing_index_group_stats System view which stores usage and access details for the missing indexes similar to sys.dm_db_index_usage_stats sys.dm_db_missing_index_groups Presumably this view will be of more use in SQL2008 sys.dm_db_missing_index_columns A system function which returns the columns for a missing index We can also view the improvement that the index is computed to make, this is essentially the same type of output as shown by the Tuning Advisor. Requires the index handle as a parameter 9 © Colin Leversuch-Roberts Kelem Consulting Limited September 2007 sys.dm_db_index_usage_stats • Tells us how the index is used • Counts scans, seeks, lookups and writes • Values are cumulative, so not so useful • Contains last date of of each however 10 © Colin Leversuch-Roberts Kelem Consulting Limited September 2007 sys.dm_db_index_operational_stats • Contains a wealth of information • Page splits, waits, access at each index level • Data is not maintained • Not a view • Need to snapshot the data 11 © Colin Leversuch-Roberts Kelem Consulting Limited September 2007 Some sample outputs © Colin Leversuch-Roberts Kelem Consulting Limited September 2007 sys.dm_db_index_physical_stats • Actually a function • Find the size of your index • Use with care on large tables • Sadly not table valued so difficult to join • Roll your own 13 © Colin Leversuch-Roberts Kelem Consulting Limited September 2007 All about granularity • Finding unused indexes • Dealing with period end processes • How often do I collect data and how much • Little used indexes • Seeks and lookups • Making sense of it all 14 © Colin Leversuch-Roberts Kelem Consulting Limited September 2007 Index size • A secondary index adds the clustered index • Wide clustered indexes make for wide secondary indexes • Wide indexes take space • Wide indexes produce more io • Wide indexes may be considered less cost effective by the optimiser 15 © Colin Leversuch-Roberts Kelem Consulting Limited September 2007 Missing Indexes • Also available through the performance dashboard • Lots of information • Much as you receive from the DTA • Simple query to extract basic information • Don’t believe it all 16 © Colin Leversuch-Roberts Kelem Consulting Limited September 2007 And to finish • Queries to check for duplicated indexes coming in part 5 on my blog • Query / Report to show top indexes in cache • Query / report to analyse plans in cache 17 © Colin Leversuch-Roberts Kelem Consulting Limited September 2007 More information • [email protected] • http://sqlblogcasts.com/blogs/grumpyolddba/default.aspx • Coming soon www.grumpyolddba.co.uk • www.sqlserverfaq.com • Available for consultancy Thankyou for listening 18 © Colin Leversuch-Roberts Kelem Consulting Limited September 2007