You Can Tune Your Own SQL Code

This talk is geared towards developers who maybe aren’t so familiar or comfortable with Query Plans, IO Statistics, SQL Profiler or the concepts of tuning queries. We cover some basics (Indexing, SARGable Queries, Statistics, Some “Worst Practices”, some general concepts for performance minded development) and then play with some sample queries. The purpose of the demos and the presentation is to build confidence in using the tools and show how to pick apart some basic queries. I also enjoy showing a demo with a user defined Scalar Function used in the select list (watching it in profiler with SP:StatementCompleted quickly leads to an “aha!” moment).

This talk has been delivered at:

• SQL Saturday #34 in Waltham, MA
• SQL Saturday #33 in Charlotte, NC

Some Related Links/Blog Posts

• Inside SQL Server 2008 – T-SQL Querying. This is probably the best book that I can recommend for understanding the how and why questions in the query engine. If I could, I would make this required reading for every developer at my company. Itzik and his great authors go into details of what happens when you run a query. They cover set based logic and the steps of query processing so you can have an eye to writing efficient queries. The 2005 version is also still available.
• Grant Fritchey’s book, SQL Server 2008 Query Performance Tuning Distilled was also mentioned in the talk. Great resource that talks about a lot of the same concepts we discussed in the presentation and expands in more areas.
• Implicit Conversions – We talked about the impact of implicit conversions, I blogged a reminder about this concept awhile back.
• User Defined Function Impact – Gail Shaw has a great post about this topic on her blog. Her post is a great reminder about the impact an improper understanding of the impact of UDFs can cause.
• Empirical Evidence – A pet peeve of mine, I talked about using it in the presentation and I blogged about using it here.
• Troubleshooting Methodology – While we didn’t talk specifically about this, it is important to have a repeatable troubleshooting process to find and solve performance issues.
• Testing With Representative Volumes – We talked about this a few times: “It worked fine in dev” is not a great answer. Here are some thoughts on why.
• Query Plans Aren’t Scary – Looking at the thought of a developer that would say, “just add a join hint!” we dissect some of the same tips we talked about in this presentation.

The Presentation

(Version 1.5 as presented in Charlotte to SQL Saturday 34 on 3/6/2010)

Questions/Comments?

If you have any questions from the presentation, feel free to add comments or send me an email (it’s mike at the same domain of this blog) and I will answer you in the comments, e-mail and perhaps add the question and answer to this post and update the presentation if necessary)

Did you attend this presentation? How did I do? Check out my profile on Speaker Rate and let me know so I can make any changes for the future.

Want To Know More?

Check out the about me page to learn more about StraightPath Solutions, Mike Walsh or this blog. Also feel free to subscribe to the blog for content updates.