Benchmarking SQL Server operations

Sometimes you’re stuck trying to optimize a large stored procedure and the first step in optimizations is to figure out where the time is being spent. It’s not always where you expect, and going at it haphazardly doesn’t cut it.

The answer is of course benchmarking (timing), to figure out where the time is spent, and to figure out if your “fix” improved performance or not.

First of all, declare the variables you’ll need. Do this at the top of the the procedure/script that you’re benchmarking;

declare
@totalTime datetime=GetDate(),
@partTime datetime=GetDate()

Then drop in scripts like these throughout the code you’re benchmarking;