The Curse of Intellect

I'm always amazed at how the smallest items can have the biggest changes in SQL queries. Well, that's a lie, not amazed, pleasantly surprised. I was amazed the first few times I witnessed it, there's a certain joy that comes from fixing a bad query, or just outright generating a good query, I imagine it's what being an accomplished cook feels like (sadly a sensation that the microwaved meals I'm capable of preparing never give me).

Then again, this joy can bring about problems as well, especially when you're optimizing other people's queries. A typical conversation about a query you've optimized originally written by another contributor or coworker will go like this, "Why'd you change fn_CB_XQY_1_Do_Not_Delete yesterday?" "Well I just changed the join and factored out a subquery to make it perform a little better, it was jamming up my application every once in a while." "Yeah, but the old version ran in 3 seconds and this one runs in 3.2, you made it slower." Which brings us to the central thesis of this article, a lot of people don't understand "Real SQL Performance Metrics".

Standard logic tells you that if you run a query 10 times or so, look at how long it took to execute, then run the other 10 times or so, and one is faster, it's the better query. However, this is the lowest form of bench marking. Heck, you could set up automated bench marking to run these queries several thousand times each and still not have improved on that method much. A slight upgrade would be run them both with:

Set Statistics Time On

Now suddenly you can actually look at the amount of CPU time the query took, a much more useful metric. We're still not cooking with fire though, and I tire of leading you on, so I'll spill the beans, to get a complete look at the performance of a query, you need 3 items.

1) The CPU time and execution time of the query (Set Statistics Time On

2) The Execution plan of the query (Set Statistics Profile On)

3) The IO resources consumed by the query ( Set Statistics IO On)

If you use an environment that lets you graphically examine the plan, you're even better off. You can looks at this plan for table scans, index scans, and other non-optimal behaviors.

Of the greatest importance are the IO statistics, however. They will predict something that all of the benchmarking on the dev server in the world cannot, the queries performance on a heavily loaded server. The IO statistics will return to you scan count, logical reads, physical reads, readaheads, lob reads, etc. by table referenced.

The most important stats here are scan count and logical/physical reads. These must be minimized to have a high performance query. Also, don't invest too much in looking to get scan count down below 1, it doesn't really mean anything anymore (scan count is not as reliable as reads in newer versions of sql server, if you have a query with a scan count of 4 vs. one with a scan count of 1, you've probably made an improvement, but comparing 1 to 0 is a tougher call). Additionally, in most cases you can lump physical reads and logical reads together because whether your query has its needed data in memory or on hard disk is, for the most part, not within your control.