SQL Server Planet

When optimizing procedures often times you need to rewrite the procedure in order to speed it up. However, when you’re finished how can you be sure that the result set is still the same? This article will show you how to run a thorough test to make sure. We’ll execute the old and new stored procedure then compare their result sets by using a checksum aggregate. We’ll also compare rowcounts and elapsed execution time. The benefit of this test harness is that it will allow you to loop through and execute the procedures with as many dynamic parameter values as you wish, then compare the result set of the old procedure vs the new procedure for each execution.

First, we need to do a couple things to set up our environment.

1. Setup a local linked server
2. Create a wrapper procedure

We need the local linked server in order to dynamically retrieve the result set of the stored procedure using OPENQUERY. We could skip this step and create the temp table explicitly if we knew all the columns and data types being returned, but sometimes this is a hassle because there could be many columns that are returned and explicitly creating a temp table for each procedure we wanted to test is a pain. And that’s probably why you’re here in the first place isn’t it?

So to create a local linked server, do the following:

EXEC sp_addlinkedserver
@server='LOCALSERVER',
@srvproduct='SQLSERVER',
@provider='SQLNCLI',
@datasrc='Your_Server_Name_Here'

For this example, the server name must be LOCALSERVER

Next, we need to create a wrapper procedure that will take in the stored procedure along with dynamically passed parameters, and predefined temp table name, in order to populate the temp table. The reason we need this wrapper is because the OPENQUERY command does not allow concatenated strings, thus we need to use dynamic SQL to build and execute the openquery command within the wrapper procedure. To build the wrapper proc, execute the following code.

CREATE PROCEDURE [dbo].[proc_to_table]
(
     @temp_table_name nvarchar(100)
    ,@sp_and_parameters nvarchar(max)
    ,@elapsed_ms int OUTPUT
    ,@row_count   int OUTPUT
)
AS

BEGIN
    DECLARE @Driver nvarchar(20);
    DECLARE @SQL nvarchar(max);
    DECLARE @RowsetSQL nvarchar(max);
    DECLARE @now datetime;
    DECLARE @local_server_name varchar(100) = 'LOCALSERVER';

    SET @RowsetSQL = '''' +'SET FMTONLY OFF ' + '' + @sp_and_parameters + '' + '''';

    SET @SQL = 'INSERT INTO #' + @temp_table_name + ' SELECT * FROM OPENQUERY(' + @local_server_name;

    SET @SQL = @SQL + ',' + @RowsetSQL + ')';

    SET @now = GETDATE();

    EXEC (@SQL);

    SET @row_count = @@ROWCOUNT;

    SET @elapsed_ms = DATEDIFF(ms, @now, GETDATE());
END

Ok, so now that we have the requirements done, let’s go through a simple example of it’s use, then we’ll look at a more complete example.

Simple Example

-- Create shell of temp table
SELECT TOP 0 *
INTO #holding_table
FROM OPENQUERY
(
      LOCALSERVER
      ,'set fmtonly off exec your_db.dbo.spr_your_procedure 1' -- hard code proc and any params
);

-- form procedure execution and parameters into a string
SET @sp_with_params = 'exec your_db.dbo.spr_your_procedure '
      + CAST(@employee_id AS VARCHAR(50));

-- pass in temp table name & procedure string
EXEC proc_to_table 'holdingtable'
      ,@sp_with_params
      ,@elapsed_ms OUTPUT
      ,@row_count OUTPUT;

-- Display
SELECT *
FROM #holding_table;

You won’t see the benefit from this simple example until we actually loop it, however this is the crux of the work. Now let’s look at a more functional example.
This example will retrieve a specified number of parameters, then loop through and test the old and new procedure with this parameter. You can just as well have multiple parameters also.

Complete Example

DECLARE @rows int = 1000; -- number of records to test

--drop table if exists
IF OBJECT_ID('tempdb..#employee_ids') IS NOT NULL
BEGIN
      DROP TABLE #employee_ids
END

-- retrieve the parameters we want to pass to the procedures
SELECT TOP (@rows)
      employee_id
      ,rownum = ROW_NUMBER() OVER (ORDER BY e.employee_id DESC)
INTO #employee_ids
FROM dbo.employee e
WHERE e.employee_type_id IN (1,2,3)

DECLARE @employee_id int;
DECLARE @sp_with_params varchar(max)
DECLARE @elapsed_ms int;
DECLARE @row_count int;
DECLARE @chksum int;
DECLARE @chksum_old int;
DECLARE @iter int = (SELECT COUNT(1) FROM #employee_ids);

WHILE @iter >= 1
BEGIN

      /*************************************************
      * first procedure run
      **************************************************/
      --drop table
      IF OBJECT_ID('tempdb..#holdingtable') IS NOT NULL
      BEGIN
            DROP TABLE #holdingtable
      END

      -- select one of the parameters to test with
      SET @employee_id = (SELECT employee_id FROM #employee_ids WHERE rownum = @iter);

      -- create empty temp table
      SELECT TOP 0 *
      INTO #holdingtable
      FROM OPENQUERY(LOCALSERVER,'set fmtonly off exec yourdb.dbo.spr_your_procedure 1');

      -- set the parameters of the procedure
      SET @sp_with_params = 'exec yourdb.dbo.spr_your_procedure ' + CAST(@employee_id AS VARCHAR(50))

      -- execute the procedure
      exec proc_to_table 'holdingtable', @sp_with_params, @elapsed_ms OUTPUT, @row_count OUTPUT

      -- calculate checksum for entire table
      SELECT @chksum = CHECKSUM_AGG(BINARY_CHECKSUM(*))
      FROM #holdingtable

      -- insert results
      INSERT INTO tbl_test_spr_your_procedure
      (
            ID,
            employee_id,
            new_elapsed_ms,
            new_chksum,
            new_rowcount,
            datetime_inserted
      )
      SELECT
            @iter,
            @employee_id,
            @elapsed_ms,
            @chksum,
            @row_count,
            GETDATE()   

      /*************************************************
      * second procedure run
      **************************************************/
      IF OBJECT_ID('tempdb..#holdingtable2') IS NOT NULL
      BEGIN
            DROP TABLE #holdingtable2
      END

      -- create temp table
      SELECT TOP 0 *
      INTO #holdingtable2
      FROM OPENQUERY(LOCALSERVER,'set fmtonly off exec yourdb.dbo.spr_your_procedure 1');

      -- set the parameters of the procedure
      SET @sp_with_params = 'exec yourdb.dbo.spr_your_procedure ' + CAST(@employee_id AS VARCHAR(50))

      -- execute the procedure
      exec proc_to_table 'holdingtable2', @sp_with_params, @elapsed_ms OUTPUT, @row_count OUTPUT

      -- calculate checksum for entire table
      SELECT @chksum_old = CHECKSUM_AGG(BINARY_CHECKSUM(*))
      FROM #holdingtable2

      -- update results
      UPDATE tbl_test_spr_your_procedure
      SET
            old_elapsed_ms = @elapsed_ms,
            old_chksum = @chksum_old,
            old_rowcount = @row_count
      WHERE ID = @iter  

      SET @iter -= 1;

END

/**********************************************************
*     compare the results
***********************************************************/
SELECT *
FROM tbl_test_spr_your_procedure
WHERE
(
      new_chksum != old_chksum
      OR
      new_rowcount != old_rowcount
)

SELECT AVG(new_elapsed_ms), AVG(old_elapsed_ms)
FROM tbl_test_spr_your_procedure

That’s it! I’m sure this code can also be extended to be more generic, if you have any other ideas or enhancements please comment about them below.