« Stored Procedure to Restore SQL Database | Main | Monitoring Mirroring »

Head over to SQLSkills.com! They're giving away training!

Those amazing people at SQL Skills are at it again. They've offered one lucky DBA a spot in the Chicago training coming up in October. Haven't posted in quite a while so this seemed like a great excuse to do so.

I attended the IE2 course a few years ago and it was easily the best training I've ever been to for anything. The attention to detail was a big part of that. No questions went unanswered and the PowerPoint slides were packed with the full content rather than high-level tag lines. So when you got home and opened the course materials again you knew what they referred to.

I swore I'd be back for IE1 some day, so crossing my fingers this is my chance. If, by some miracle, I beat out the rest of the competition I'll definitely have to blog more frequently.

My favorite tuning challenge is when a developer comes to me with a SPROC that's running slow. It's a little reminder as to what DBA's can do beyond just mindlessly running scripts. It's also a good chance to take the headphones off and collaborate.

Anyway. Over the years I've gotten my repertoire down pretty well. There's already a lot of great materials out there. This Aaron Bertrand/Kevin Kline PASS Session is one of my favorites.

Running the SPROC.

You've gotta see the problem, right?

First thing to do is get a valid set of parameters to run the SPROC with. Then I find an environment where I can safely run it.

When running the SPROC I like to examine the plan using SQLSentry's Plan Explorer. Get that here. I find a lot of developers don't know about this tool and end up loving it.

SET STATISTICS IO is also often underused by developers and quantifies the amount of reads against certain tables or reveals performance-degrading dumps to TEMPDB.

Choosing Indexes

A lot of times there's opportunities for indexes. Management Studio or Plan Explorer may even call them out.

Sometimes existing indexes are insufficient. If I'm running Enterprise Edition I check to see that we're using compression. A couple of times I've created nonclustered indexes on the clustering keys of the table so SQL has less data to push into memory.

I need a top-down look at all the existing indexes on the table before I can start. Running sp_rcb_whatcolumns indexed against affected tables helps with that. (Though you'll need to USE the database you're running it against before it will work.)

The Usual Suspects

Good developers clean up after themselves. There's no reason to drop temp tables when you're done with them. So remove any unecessary drops of temp tables.

If a CTE is one of my bottlenecks then I typically rewrite it to leverage a temp table and compare run times. I immediately am suspicious of any user-defined functions in the SPROC whose activities will evade my Plan Explorer. Often there's a few sorely needed indexes or other touch ups that can be done.

Code Review

I always get a developer to do a thorough code review with multiple use cases across a myriad of parameters. 

Before I even begin I always say "I can make anything you've written faster." Then, as their eyes begin to widen at that brash statement, I continue "...maybe just not equivalent."

PrintView Printer Friendly Version

EmailEmail Article to Friend

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>