TSQLTester for SQL Ninja

Hi, today I would like to share with you idea of testing tool for T-SQL developers who want to not only replace stored procedures with fasters stored procedures but also want to be sure that old code on SQL Server produces exactly the same results as a new optimized and tuned up one. I created this tool as a command line to easily integrate with possible testing automation, and TSQLTester.exe has 4 parameters. All of those parameters are the localization of files with different contents. The first one contains a connection string to the database server we want to measure execution time and test our solution. The second file contains T-SQL tests, for example, a bunch of invocation of stored procedures or a bunch of SELECT statements without GO statements inside. The third file contains a new version of our code that we tuned up. It can be a stored procedure DROP CREATE file or even a file with a bunch of that stored procedures. The fourth file contains old code, for example, a stored procedure DROP CREATE file or other code that brings back the old version of code to SQL Server, and it is used for testing as well as a base code that produces good results.

To create my tool I used C# 4.0 and Visual Studio 2012. And I referenced 3 following libraries:

• Microsoft.SqlServer.ConnectionInfo,
• Microsoft.SqlServer.Management.Sdk.Sfc,
• Microsoft.SqlServer.Smo.

Because I want to use SMO. And to have them available, please remember to install them with SQL Server “Client Tools SDK” feature. And to compile this solution, you must have SQL Server installed on your development machine. Also very important thing is to have backward compatibility with this tool, so I need to specify in the “app.config” as a C# project item with the following content.

<?xml version="1.0"?>
<configuration>
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5"/>
    <requiredRuntime version="v4.0" />
  </startup>
</configuration>

And here is code of TSQLTester that contains less than 100 lines of C# code and helps a lot with testing not only execution time and measures it in milliseconds but also check exactly content of produced results by testing code with previously run new and old one T-SQL code.

namespace TSQLTester
{
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Diagnostics;
    using System.IO;
    using Microsoft.SqlServer.Management.Common;
    using Microsoft.SqlServer.Management.Smo;
    class Program
    {
        static void Main(string[] args)
        {
            if (args.Length != 4)
            {
                Console.WriteLine(
                "Usage: TSQLTester.exe connectionStringFile, testCodeFile, newCodeFile, oldCodeFile");
                return;
            }
            var connectionStringFile = args[0];
            var testCodeFile = args[1];
            var newCodeFile = args[2];
            var oldCodeFile = args[3];
            RunTest(connectionStringFile, testCodeFile, newCodeFile, oldCodeFile);
        }
        private static string GetContent(string filePath)
        {
            var file = new FileInfo(filePath);
            return file.OpenText().ReadToEnd();
        }
        private static bool CompareDataSets(DataSet fSet, DataSet sSet)
        {
            if (fSet.Tables.Count != sSet.Tables.Count)
                return false;
            for (int t = 0; t < fSet.Tables.Count; ++t)
            {
                var t1 = fSet.Tables[t];
                var t2 = sSet.Tables[t];
                if (t1.Rows.Count != t2.Rows.Count ||
                    t1.Columns.Count != t2.Columns.Count)
                    return false;
                for (int r = 0; r < t1.Rows.Count; r++)
                {
                    for (int c = 0; c < t1.Columns.Count; c++)
                    {
                        if (t1.Rows[r][c].ToString() !=
                            t2.Rows[r][c].ToString())
                            return false;
                    }
                }
            }
            return true;
        }
        private static void RunTest(
            string connectionStringFile, string testCodeFile,
            string newCodeFile, string oldCodeFile)
        {
            using (var connection = new SqlConnection(GetContent(connectionStringFile)))
            {
                var server = new Server(new ServerConnection(connection));
                var newTestAdapter = new SqlDataAdapter(
                GetContent(testCodeFile), connection);
                var oldTestAdapter = new SqlDataAdapter(
                GetContent(testCodeFile), connection);
                server.ConnectionContext.ExecuteNonQuery(GetContent(newCodeFile));
                var newDataSet = new DataSet();
                var newTime = Stopwatch.StartNew();
                var newCount = newTestAdapter.Fill(newDataSet);
                newTime.Stop();
                server.ConnectionContext.ExecuteNonQuery(GetContent(oldCodeFile));
                var oldDataSet = new DataSet();
                var oldTime = Stopwatch.StartNew();
                var oldCount = oldTestAdapter.Fill(oldDataSet);
                oldTime.Stop();
                Console.WriteLine("RESULT: {0}, OLD_TIME: {1}ms, NEW_TIME: {2}ms.",
                (newCount == oldCount && CompareDataSets(newDataSet, oldDataSet)
                ? "PASS" : "FAIL"),
                oldTime.ElapsedMilliseconds,
                newTime.ElapsedMilliseconds);
            }
        }
    }
}

So, now you maybe wonder how to use this very small but powerful tool? I have in my mind at least two scenarios. First is that you have stored the procedure on a copy of the production database and want to tune up this procedure. But, at the same moment, you want to be sure that the new procedure produces the same output(s) as the old one. So, you can use your application and SQL Profiler tool to record all invocation of procedure(s) you want to tune up, and you may collect all that uses examples in your testCodeFile.sql file. You may also put the first line of this file, “DBCC DROPCLEANBUFFERS” if you want to clean buffers on a database to have the same results. Now you can create newCodeFile.sql with DROP CREATE code for your new optimized stored procedure(s) and oldCodeFile.sql with DROP CREATE code for your old well-tested stored procedure(s). Of course, you can open testCodeFile.sql and newCodeFile.sql in SQL Management Studio and play with optimization, analyze execution plans, find bottlenecks, and from time to time, you can use TSQLTester to check if the new code is faster and create exactly the same output as old one. When some of your change impact to results and TSQLTester FAIL test, you may revert back to your last change and do something else.

the Second scenario I can give you as an example of usage TSQLTester is a stored procedure that calculates the statuses of rows in a table. Because of some reason, it is slow because it has a cursor and other mistakes (see source code at the end of entry). And you have some ideas on eliminating this cursor and other issues, but you want to be sure that statuses will be calculated exactly the same on the new code. To achieve that, you may create testCodeFile.sql with clean buffers of T-SQL, invocating the update procedure, and then put a series of select statements that will select calculated statuses. Of course, you may have many executions of update store procedures and many selects to produce output to compare the old and new procedures.

At the end I want to tell you why this TSQLTester is so cool? First, it is because the huge SQL Management Studio has no such feature, and I do not know why it takes less than 100 lines of code to write. And it solves INSERT EXEC issues that you cannot solve with tSQLt, for example :). Second, it is cool because you can very fast test the results of your optimizations. You may even have several folders with different connection strings and tests code and use BAT or PowerShell to invoke all-suite of tests on many copies of production databases. And Third reason why TSQLTester is so cool is that it decreases the risk of your impact on production T-SQL code, so your managers will be happy that you take the quality of your work by testing both performance and content or results seriously. If you would like you may download source code of TSQLTester (3909 downloads). Enjoy!

P ;).