SQL/ SQLServerPedia/ Syndicated/ Tips and Tricks

Index-Index-Index-Index-GOOSE! (Part One)

, , 2 Comments

It’s been interesting as of late at the office, and by interesting I mean TOTALLY DEVOID OF NEW PROJECTS AND THINGS ARE RUNNING AND PEOPLE ARE NOT COMPLAINING SO TOTALLY SO NOT INTERESTING slow.  Slow is a good thing for a Database Administrator.  It gives us time to get our head above water, review documentation, learn new aspects of the technologies we support and which support us, oh and it also allows us the ability to review and correct performance issues across all 70 SQL instance and 1200 databases from 10 years of simply loading them up and moving onto the next firestorm.  Not that it is an issue I’m encountering, I’m just sayin’.

Thanks to the bounty that are the SQL Server Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) which released with SQL Server 2005, diagnosing your indexing needs and misdeeds is so much easier than in previous versions of Microsoft SQL Server.  The index-related DMVs fall into two categories statistics and missing indexes.  In this post we will look exclusively at the statistics DMVs and DMFs. 

Statistics

These are the DMVs related to how the index(es) are being read, scanned, locked, waited upon, well you get the idea.  They also provide you with information relating to the fragmentation status and phyiscal structure of the individual indexes. 

sys.dm_db_index_usage_stats    (view)

Provides results for how and index has been used to resolve queries, how many times it has been used for scans, seeks and lookups.  It also can tell you how many times the index was updated due to underlying table changes.  This information is partitioned to provide the information both when the usage and changes are the result of user direction or because of system-initiated actions.

 

sys.dm_db_index_opertational_stats    (function)

This dynamic management FUNCTION accepts the following parameters:

  • database_id – this specifies the id of the database on the instance.  You can pass a NULL value to return results for all databases, (but in doing so you must supply NULL values for all parameters.)
  • object_id – is the object id of the table or view the index is on.  A NULL value will return results for all objects in the database when specified along with a non-null database_id parameter.
  • index_id – is the index id.  A NULL value may be passed if you wish to return results for all indexes on a given table or view.
  • partition_number – you may chose to return results for only a single partition of an index or heap.  Since partitions are 1-based, supplying a 1 for a value returns results for the entire non-partitioned heap/index.  This is also the same result if you supply 0, NULL, or DEFAULT on a non-partitioned index or heap as those parameter values return results for all partitions on a particular object.

The information provided in this function is far more granular than what is afforded by the sys.dm_db_usage_stats DMV.  You’re able to obtain information on just how your indexes are being hit at the leaf and non-leaf levels (inserts, updates, and deletes), how many time locks have been requested against the index, page latch counts and waits.  Just to name but a few of the columns.  It slices and dices this information by partition as well, adding another level of granularity to the mix.

 

sys.dm_db_index_physical_stats    (function) 

Ah, my favorite DMF!  This is the one I use daily and if you’ve rolled-your-own index defragmentation process then you have too.  sys.dm_db_index_physical_stats expects the following parameters, many of which are consistent with those passed to sys.dm_db_index_operational_stats:

  • database_id – this specifies the id of the database on the instance.  You can pass a NULL value to return results for all databases, (but in doing so you must supply NULL values for all parameters.)
  • object_id – is the object id of the table or view the index is on.  A NULL value will return results for all objects in the database when specified along with a non-null database_id parameter.
  • index_id – is the index id.  A NULL value may be passed if you wish to return results for all indexes on a given table or view.
  • partition_number – you may chose to return results for only a single partition of an index or heap.  Since partitions are 1-based, supplying a 1 for a value returns results for the entire non-partitioned heap/index.  This is also the same result if you supply 0, NULL, or DEFAULT on a non-partitioned index or heap as those parameter values return results for all partitions on a particular object.
  • mode – specifies the type of scan to run against the index collection to provide the statistical results.  The allowed values are DEFAULT, NULL, LIMITED (all of which have identical results), SAMPLED or DETAILED.  The impact on server resources is greater as you move from left-to-right in this list.

DBCC SHOWCONTIG can suck it.  The results are nothing short of SQL Porn for the Database Administrator.  You will see statistics for index type, index depth, fragmentation (skewed numerous ways), and other physical index characteristics (record count, record size, and page count to name a few.)  It provides everything you need to review fragmentation.  It also affords you the ability to script ad-hoc Transact-SQL from the results to fix fragmentation when appropriate.  We will take a closer look at that in part two, but if you don’t wish to wait, go out to SQLServerPedia and look at the topic on index fragmentation there.  Michelle Uford’s scripts are just about identical to those I use.

Spoiler Alert:  Stay tuned for parts two and three in which you’ll learn that Darth Vader is Luke’s father and no level of indexing Jedi Mind Tricks will ever score you chicks.  Especially when you also include Star Wars references. you will see examples of what you can do with these three objects.