Redgate’s SQL in the City events are imminent, first in London (Oct 16) and then in Seattle (Oct 26). I’m speaking at both events, on the topic of “Uncovering SQL Server Query Problems with Execution Plans”.
I’ve often heard developers confess, sheepishly, that they very rarely look at execution plans. They haven’t the time for the initiation into the art of reading them. They care about database performance, and about the SQL code they produce, but if the code is too slow, their inclination is to just rip it up and start again! They have other priorities and preoccupations than to delve into the ‘implementation details’ of the database.
However, when database performance suffers, you need to know why, and merely examining the query won’t necessarily tell you. It will tell you what data set is coming back, but not what actions SQL Server performed, and when, in order to get that data. This, in short, is precisely what the execution plan reveals. It will show which tables and indexes a query accessed, in which order, how they were accessed, what types of joins were used, how much data was retrieved, at what point filtering and sorting occurred, and a whole lot more. These details can and often will highlight the likely source of any performance problem, and for some common problems, you often don’t need to scratch too deep into the plan to find the likely root cause.
My session will demonstrate the following common query problems, and how to uncover the root cause in the plan:
- Inefficient indexing – from an application developer’s perspective, the best solution is always one that doesn’t involve rewriting code!
- An imperative, “row-by-row” approach to writing T-SQL code that forces SQL Server to do far more work than necessary, and sends your I/O through the roof
- Data type conversions that result in scan rather than seek operations
- Generic, ‘reusable’ SQL code that confuses the optimizer and leads to wildly inaccurate estimations of the number of rows being returned, and very inefficient plans
I’ll show some useful tools and scripts for retrieving the plan and hope to prove that even with a modest investment of time, developers can begin to spot ‘low hanging fruit’ in these plans, which can often lead to drastic gains in query performance.
If you’re inclined to view the database as something more than just a black box at which you throw queries, then a basic understanding of how to read a SQL Server execution plan will reap rewards. I’d love to hear feedback from any Simple-Talk readers who are able to attend. If you’re in town, sign up!