Tuesday, June 13, 2006 5:32 PM
by
michael.k.campbell
Creating Covering Indexes - That much easier with SS 2005
Index Tuning is something that I _really_ enjoy - mostly because I just love
seeing performance improvements. It's always exciting to see tangible
improvements in existing systems.
Recently while looking into a query that was causing a lot of reads for one
of my clients, I determined that while the query was making very good use of
Index Seeks it was still doing some bookmarks - some expensive bookmarks that
were accounting for nearly 18% of all reads on the client's system. So, a
covering index seemed the logical choice. Figuring out what's needed for a
covering index only involves a bit of deciphering, but I've recently seen notice
that SQL Server 2005 makes it tons easier to figure out what columns need to be
accounted for.
Check out the following screen cap of the sample query I was working on from
SS 2000's Graphical Estimated Execution Plan:
It shows that the bookmark operation is taking 50% of the overall effort of
the query. And that's about it.
Now look at this screencap from SS 2005's Graphical Estimate Execution
Plan:
Spiffy eh?
Seriously, talk about a nice touch.