SQL Performance Refreshers, Tips, and Tricks

Lately, I’ve been doing quite a bit of work fine tuning and enhancing the SQL used on my current project. So, I decided to do a little reading to refresh my memory and see if I couldn’t find any new tips or tricks. Along the way, I found 4 very good articles that would be helpful to anyone trying to get the most out of their database design.

This first link is by Jonathan Lewis. After reading it, it really reminds me that careful consideration should be given to the structure of the queries you design. Subtle changes to the order in which tables are accessed affect which clustered and non-clustered indexes the query optimizer picks, and how many reads are needed to accomplish the job, which could go a long way in improving query performance.

Designing Efficient SQL: A Visual Approach

The 2nd link is by Kathi Kellenberger. It is an interesting breakdown of the winning solution to a SQL performance contest. I think I came reasonably close in my solution, which she alludes to near the end (I used two subqueries and a join on the year+month to populate a temp table with the new and cancel counts, and then updated the temp table with the totals). But, there are definitely a few tips and tricks that I didn’t think of doing (specifically using the unpivot, which kills the need to read the table twice, and leveraging the CTE + Output). Also, the datediff trick can prove really handy (and is one I’ve had in my toolbox for a while now).

Writing Efficient SQL: Set-based Speed Phreakery

The 3rd link is by Josef Richberg. It is a nice, simple refresher on how to use the tools we’ve been given (STATISTICS IO and Execution Plans) to tune our queries properly, and choose appropriate clustered and non-clustered indexes.

Simple Query Tuning With STATISTICS IO And Execution Plans

And, the last is by Laerte Junior. It is a nice breakdown of some of the more recent SQL performance tools we’ve been given, specifically the “Missing Indexes” DMVs. He shows it to be quite helpful in the SQL tuning process.

Reducing IO With The Missing Indexes DMVs

Enjoy the reading.

Rss Feed