Developing Matt

My Technical Journal

Index Internals and usage by Kimberly Tripp

leave a comment »

If you have heard Kimberly speak, you know she loves to talk about indexes.  So this was a class where she could let loose with her love of indexes and run with it.  And it was great!  Every class i take from her is great.  She is passionate about her topics, and that passion is transferred over to the crowd.  I learned a lot and have much to learn!  There are some major points that she hit below and my notes are here: 01_index-internals-and-usagkimberlytrip.  This class might be difficult to read through.  It’s a good class to sit through and listen to her explan as she goes through the slides, but hopefully it will help.

The point of optimization is not to find a good plan….but to find a good plan fast.  This sometimes means that the best plan is yet to be discovered, so modifying and tweaking and testing your query is a must.

Optimization is about selectivity and what determines selectivity?  Statistics.  In sql 2000 you had to watch the number of statistics but in 2005 the more stats the better. 

Sql server has to know how much data is going to be processed before it goes to the data effeciently. 

Stats on Stats 

  • Stats are stored in sys.stats
  • Stats are not valid between releases (use sp_updatestats)
  • Turn on auto create stats and auto update stats
  • There is a dmv called indexusage
  • Consider adding more statistics using sp_createstats
  • Also recommended: sp_createstats ‘indexonly’,’fullscan’


  • Each page is 8K (8096 if you take away the header(96 bytes)).  There are 400 bytes per row which equates to 20 rows per page
  • How do you see the page count?  SELECT * FROM SYS.DM_DB_INDEX_PHYSICAL_STATS TO VIEW INDEX  
  • Sp_helpindex to view indexes
  • A non clustered index is effectively a mini-table, which means they have a lot of interesting uses (which is why an index on last name, first name, middle initial allows you to search for all three or a combination of)
  • Sometimes wider indexes are better than narrower.  If you are seeking highly selective index names then the narrow index is selective enough.  But it’s useless ot have narrow indexes when your queries are returning 1,2,3% of the data. 
  • What performance tools are available?  She mentioned performance dashboard, the dta utility, and umv’s.

Key points

“Always start with a good clustered index key.  If the non-clustered indexes have to look up the data based on the clustereing key that means the clustering key is in every single non-cluster.  So make sure it’s narrow.   if it’s wide it’s going to make all of your non-clusters unnecessarily wide.  And if it’s not static then it has to go to all these places to keep it up to date.  If it’s not unique, sql server ‘uniquifies’ which is highly costly (on insert, updates, times, and space).  Start with a minimal number of non-clusters.  if pk is not the clustered then make the pk the next index. then create your nonclustered unique keys.  Then manually create indexes on your foreign keys.  Then start adding highly selective search arguments.  And then STOP.”

“Narrow indexes have very few uses, so use wider indexes.”



Written by matt

June 4, 2008 at 7:05 am

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: