Pretty much every environment I've ever seen follows the same patterns with regard to query size distribution: lots of typical run-of-the-mill queries (whatever is typical in that environment), followed by a diminishing number of larger queries (say, an order of magnitude bigger than typical), and a handful of gigantic queries that are several orders of magnitude beyond that.
When tuning a slow SQL Server, what's the main thing I look at improving? I focus, first and foremost, on that final set, containing the biggest, most complex, most long-running things I can find. I believe this makes a lot of sense on several levels: Not only are the biggest queries the "lowest hanging fruit" -- i.e. things I tend to be able to markedly improve quickly, but they're also usually the ones dragging down the rest of the system. Bring down the monsters and all of the little guys can breathe easier.
Unfortunately -- or fortunately if you enjoy these kinds of things as much as I do -- tuning the biggest and most complex queries can be quite a challenge. I often need to try to figure out what the query optimizer is "thinking," figure out which query hints I can or cannot leverage, and come up with interesting ways to re-write things to improve speed while maintaining the same logic. Dealing with big queries tends to be equal parts art (creativity) and science (raw application of logic). And the really interesting thing I've noted after tuning dozens of systems is that it doesn't matter whether my "big" query is bigger than your "big" query: It's the relative size of these beasts, as compared to whatever is standard, that makes them equally challenging and rewarding to tackle.
Recently I was looking for a new topic on which to write a seminar, and I decided that this topic would be perfect. Query tuning is one of my favorite pastimes in the SQL Server world, virtually every environment can use some tuning, and virtually everyone has to deal with these kinds of big queries.
So all that said, I'm proud to announce the first two deliveries of Tuning Your Biggest Queries, a full-day seminar I've put together for SQL Saturdays and conferences.
The two dates are:
Click through for abstract, pricing, and registration details.
If you can't make those dates, don't worry; more are forthcoming. Stay tuned.
Questions? Comments? Leave me a message below if you need clarification regarding the content, deliveries, or future dates.
Hope to see you there!