Stored Procedure to Benchmark Performance of two SQL Statements or SP

Many a times we feel a need to quickly compare two SQL queries or Stored Procedure performance to judge which one of the two is performing better.

The longer (better) way is to properly analyze the execution plan and manually fine-tune it. But that takes a lot of time. The shorter way is to just run those two queries and compare the time taken. But after running them for the first time, the result is not always accurate because the results are displayed from cache whenever possible (i.e. second time onwards).

The following stored procedure can be used to compare the performance of two queries/SPs.

CREATE PROCEDURE BenchMarkPerformance
	@Query1 nvarchar(MAX),
	@Query2 nvarchar(MAX)
AS
	SET NOCOUNT ON

	DECLARE @started datetime;
	DECLARE @stopped datetime;
	DECLARE @TotalTime1 datetime;
	DECLARE @TotalTime2 datetime;
	DECLARE @NumTime varchar(100);
	DECLARE @StrTime varchar(100);
	declare @counter int;

	set @TotalTime1 = 0;
	set @TotalTime2 = 0;
	set @counter = 0;
	while @counter < 5
	begin
		set @counter = @counter + 1;

		-- ********** QUERY 1 ********
		dbcc freeproccache WITH NO_INFOMSGS ;
		dbcc dropcleanbuffers WITH NO_INFOMSGS ;
		set @started = getdate();
		EXECUTE sp_executesql @Query1;
		set @stopped = getdate();
		set @TotalTime1 = @TotalTime1 + @stopped - @started;
		set @StrTime = ' Query 1: ' + CONVERT(VARCHAR(26), @stopped - @started, 114);
		--***********************************

		-- ********** QUERY 2 ********
		dbcc freeproccache WITH NO_INFOMSGS ;
		dbcc dropcleanbuffers WITH NO_INFOMSGS ;
		set @started = getdate();
		EXECUTE sp_executesql @Query2;
		set @stopped = getdate();
		set @TotalTime2 = @TotalTime2 + @stopped - @started;
		set @NumTime = ' Query 2: ' + CONVERT(VARCHAR(26), @stopped - @started, 114);
		--***********************************

		print Cast(@counter as varchar) + @StrTime + @NumTime;
		waitfor delay '00:00:01'
	end 
	print ''
	print 'Total Time:'
	print '  Query 1: ' + CONVERT(VARCHAR(26), @TotalTime1, 114) + 
	       ' Query 2: ' + CONVERT(VARCHAR(26), @TotalTime2, 114);

	print ''
	print 'Average Time:'
	print '  Query 1: ' + CONVERT(VARCHAR(26), cast(cast(@TotalTime1 as float)/5 as datetime), 114) +
	       ' Query 2: ' + CONVERT(VARCHAR(26), cast(cast(@TotalTime2 as float)/5 as datetime), 114);

It takes two SQL statements as input and executes them 5 times and prints the time taken to execute them each time.

Sample Usage:

DECLARE @Query1 nvarchar(MAX)
DECLARE @Query2 nvarchar(MAX)

-- with queries
SET @Query1 = 'select * from Table1' -- <-- your first SQL Statement
SET @Query2 = 'select * from Table2' -- <-- your second SQL Statement
EXECUTE BenchMarkPerformance @Query1, @Query2

-- with stored procedures
SET @Query1 = 'EXEC StoredProc1 @Param1=Value1, @Param2=Value2' -- <-- your first Stored Procedure
SET @Query2 = 'EXEC StoredProc2 @Param1=Value1, @Param2=Value2' -- <-- your second Stored Procedure
EXECUTE BenchMarkPerformance @Query1, @Query2

Updated June30, 2011:

Added Total and Average execution time. This way we can see the final result in a glance and don’t need to compare each result manually.

Advertisements

One Response to “Stored Procedure to Benchmark Performance of two SQL Statements or SP”

  1. Arun Says:

    Super … Thanks so much .. Helped me a lot


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: