Indexing for Performance by Kimberly Tripp

This is a course I've been studying through Pluralsight:

SQL Server: Indexing for Performance by Kimberly L. Tripp

This course will teach you how to correctly choose indexes for your environment. You'll also learn how to understand index internals, how indexes are used, and much more.

I've placed related resources here for myself:

j learning; cd sql_server\indexing_for_performance


Two stages:

General tips (throughout course)

When is index used

When don't you want a covering index?

Filtered index: a useful example

Imagine We only query error message when status = error

So we have a filtered index on status = error and also error message column (particularly if it's "left only" comparisons, i.e. like 'x%' not like %x%)

eg where status in ('error','warning') (assuming a filtered index for status 'error' and a filtered index for status 'warning'....

So you want the filtered index to be clearly the "filtered index of choice" for this query.

Should have automated statistics updating maintenance routine at a time when system is less busy.

UPDATE STATISTICS [dbo].[Employee];

Index consolidation

Similar - may make a "super index" and find it's still useful but less space maintenance easier to cache and stay in cache etc.

Kimberly has a replacement for sp_helpindex - I think this is her latest version:

External References

See also