SQL Stored Procedure to Delete Lots of Rows

The best way I've found to delete a monstrous amount (say millions) of rows is to create temp table containing a SARGable key value that's indexed on the table.  Join that to your target table and delete, say, 10,000 rows at a time.  Where 10,000 rows is a value that the optimizer doesn't escalate to a table scan.


Don't forget that if you're only checking one key value you may want to create a compressed nonclustered index on this value in the target table even if that column is already your clustered index.  Reason being you may not want to pull back every column in the table which the clustered index will have in its leaf level.


Anyway.  Take the SPROC for a spin and let me know what you think.  It's right here.


Stored Procedure to Restore SQL Database

Synching mirrored servers and Availability Groups can be a pain.

Head over to here or navigate to Handy System Stored Procedures under the SQL heading on the right.




Head over to! 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."


Monitoring Mirroring

...and here's one for mirroring.


Monitoring Availability Groups

Frequently our Availability Groups get behind.  I needed a quick-and-dirty way to be alerted when they were.  More here.