Digiguru.co.uk

How to compare sql queries to analyse real time run speed

07 Oct 2010

Reading time: 2 minutes

Once a month we find the 3 slowest running queries over the last month in our database and see if we can squeeze more performance out of them. We used to have to manually run the queries in query analyzer and watch how long they took, but after sometime, the differences in performance speed were so fractional, you couldn't trust the query speed by just performing an Exec Statement.
More recently I devised a SQL script that could compare two queries and slow the percentage increase (or decrease) in running speed. See the query as follows...
DECLARE @LoopTimes INT, @MaxLoopTimes INT, @DateStart DATETIME, @TestQueryTime1 BIGINT, @TestQueryTime2 BIGINT
SELECT @MaxLoopTimes = 2
SELECT @LoopTimes = @MaxLoopTimes, @DateStart = Getdate()
WHILE @LoopTimes > 0 BEGIN <INSERT YOUR FIRST STAEMENT HERE>
SELECT @LoopTimes = @LoopTimes - 1 END
SELECT @TestQueryTime1 = Datediff(millisecond, @DateStart, Getdate())
SELECT @LoopTimes = @MaxLoopTimes, @DateStart = Getdate()
WHILE @LoopTimes > 0 BEGIN <INSERT SECOND STATEMENT HERE> SELECT @LoopTimes = @LoopTimes - 1 END
SELECT @TestQueryTime2 = Datediff(millisecond, @DateStart, Getdate())
SELECT 'First' AS procname, @TestQueryTime1 + @TestQueryTime2 AS totalruntime, @TestQueryTime1 AS totalprocspeed, @TestQueryTime1 / @MaxLoopTimes AS averageprocspeed, ( CAST(@TestQueryTime1 AS DECIMAL) / ( @TestQueryTime1 + @TestQueryTime2 ) ) * 100 AS procpercentage UNION ALL SELECT 'Second', @TestQueryTime1 + @TestQueryTime2, @TestQueryTime2, @TestQueryTime2 / @MaxLoopTimes, ( CAST(@TestQueryTime2 AS DECIMAL) / ( @TestQueryTime1 + @TestQueryTime2 ) ) * 100 AS procpercentage
Run that around two different queries, and you will be output with all the query results, then finally a table...
ProcName TotalRunTime TotalProcSpeed AverageProcSpeed ProcPercentage
1 First 17006 1420 710 8.3499941197224509
2 Second 17006 15586 7793 91.6500058802775491
You can increase the amount of repetitions by amending the property @MaxLoopTimes to a higher number.