Developing Matt

My Technical Journal

Indexing for performance

leave a comment »

CRW_0184DevConnect1kimpaulsmall

Not to publicly admit obsessions with people, but I think I’m in love with Kimberly trip and Paul Randal.  It’s only a matter of time before I’m stalking them, secretly rummaging through their trash, grow a ponytail and say things like ‘full stop’ at the end of a driving point.

Seriously.

Ok.  Not seriously.  But the few courses I’ve attended I’ve really enjoyed and learned lots from.  If you have a chance, drive out to see them speak and buy them dinner at fancy restaurants.

Indexing for performance was taught by Kimberly tripp today and here are my notes.  They aren’t that great of notes because I was late.

More indexes are necessary for decision support.  Fewer for OLTP databases.  (makes sense, right?  your high transaction databases need to be lightweight and carefree)

There is some truth to the idea of putting the most selective column in your covering index first in order.  She went into more detail on this but I didn’t follow.

OR

She answered a question I always had on the OR statement. When you look at a query plan it always includes the very expensive SORT.  It uses sorts because it is gathering individual sets, comparing them, and removing duplicates based on the RID (row’s unique identifier) or Clustering Key.

IN is simplified series of OR conditions.  Make sure your set that you are searching in is efficiently gathered.

Depending on what you can need and what you can tolerate, consider switching to a union all.  You might increase performance (and you will see that the sort kills you).

JOINS

The key to increasing performance with joins is to find the problem child.  Find the table with the highest cost and you will see the downslide from that table is where it will kill you. 

If there is low selectivity for a range query from a monster table: look at that one first. 

the key is that i want to give sql server as many options as possible. 

Most people are missing the index on the foreign key.  LOOK THERE.  Look to see if there are indexes where the tables are joined.

If you are still having problems with it then use dta.  DTA can run on databases back to 2000.  DTA is going to bring up a workload tuning tool and its selecting the query.  Leave the options alone (exception: under advanced.  she likes the ‘generate online’ option which suggests indexes that won’t take your database offline).   For our purposes she just applied the recommended changes.  Of course, you will want to script out their suggestions and apply them carefully.

A hash match means there are temp tables involved and is an indicator that you don’t have better indexes for this query. How do you know where to start?  look at the hash key probe (member_ no in our example).  and also look at the things below and you will see where it’s located.

Add a covering index.

AGGREGATES

sql server has 2 ways to process an aggregate

  • stream aggregate
  • hash

Simple solution: take your group by and make it the key of the index and the included columns are part of the include.

Use index views will give you better performance.  USE THEM.   They are great for Aggregates

=-=-=-===-=-=-=-==-=

This class was heavily scripted and you can find these scripts and the sample CREDIT database here.  (On the scripts make sure to run ‘restore credit’ between each sql file as it alters/creates indexes)

Advertisements

Written by matt

March 25, 2009 at 4:48 am

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: