SQL Indexing – July 2009 PASS Session

Just attended the Victoria.NET – Professional Association for SQL Server (PASS) session hosted by Greg Low, with future meeting listed on the user group site: melbourne.sqlpass.org

Greg’s a very knowledgeable database specialist (don’t know his actual title). I’ve seen him present previously at RDN sessions, and really enjoy the “run throughs” using SQL query editor, and his ability to answer tricky questions raised by the audience.

I took a heap of notes, and managed to type them reasonably well on landscape mode on the iPhone, while still focusing on the presentation. I’ll try and transcribe some of it here, for it to be a somewhat useful summary.

The presentation focussed on Clustered and Non-Clustered indexes in SQL Server, with 1 key tip off the bat: index selection should be a fundamental part of the design. Combining that with the final statement of the presentation “tuning is never over” – can be interpreted as during system development indexes should not be ignored and should be re-factored as development moves forward.

General Tips:

  • Incorporating a bit in an index is a wise choice if that field has a significant selectivity impact reduction for certain queries.
  • Guid’s are where you draw the line as primary key’s anything larger such as string’s isn’t wise due to lack of efficiency, and blob like data such as varchar(1000)’s are just shooting yourself in the foot.

Another key take away was a simple list in order of priority what to look for and check when fine tuning performance:

  1. Selecting an appropriate clustering strategy
  2. Primary Key
  3. Uniqueness declared
  4. Foreign Key Indexes
  5. Then begin with usage tools

    Missing Indexes – Careful not to blindly accept all recommendations

    – Dynamic Management View (DMV)

Point 3 being a common index that offers a performance boost, but is overlooked as it’s not auto-created like those for primary keys.

The previous list can be combined with this list in columns of concern for indexes, in order of priority to improve performance:

  1. JOINS
  2. WHERE clause members
  3. ORDER BY members
  4. SELECTed columns

The final tip I walked away with is; if making use of the “Books Online” then update them every 2 months, as on average they are updated every 6 weeks, with Greg providing the example of a “Bit Field” cannot be indexed being explicitly mentioned in online documentation up until a recent correction.

The upcoming presentations of the PASS group will focus on Reporting Services and Profiling.

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 )

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