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 |