Normalize SQL Query Text in c#

Question

I executed the following query in the SQL Server database.

SELECT * FROM Production.Product WHERE Name = 'Bearing Ball'

Then I tried to get the query text along with the statistics using the following SQL command :

    SELECT
    qs.sql_handle,
    qs.execution_count AS EXECUTION_COUNT,
    AVG_TIME = --Converted from microseconds
    (qs.total_elapsed_time/1000000) / qs.execution_count,
    qs.total_elapsed_time,
    TOTAL_TIME = --Converted from microseconds
    qs.total_elapsed_time/1000000,
    st.text AS TEXT
FROM
    sys.dm_exec_query_stats AS qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
        CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC

I get something like this

(@1 varchar(8000))SELECT * FROM [Production].[Product] WHERE [Name]=@1

But, I want to perform a string match from my c# program and get the statistics of the query text. The c# program only knows about the original query (SELECT * FROM Production.Product WHERE Name = 'Bearing Ball'). The parameters getting replaced is not the only problem, as you can see the database server added square brackets to the table names.

Is there a way to overcome this issue. How can I convert my original query(SELECT * FROM Production.Product WHERE Name = 'Bearing Ball') to normalized one((@1 varchar(8000))SELECT * FROM [Production].[Product] WHERE [Name]=@1)? Can I do this using Microsoft.Data.Schema.ScriptDom? Please note that I want to normalize my query without executing it in the database server.

Answer 1

One solution, if you simply want to be able to match them up, would be to prefix your SQL statement with a GUID in a comment and then match by that:

So in C#:

var statementMagicStringMarker = Guid.NewGuid().ToString("N");
cmd.CommandText = "--" + statementMagicStringMarker + "\n" +
"SELECT * FROM Production.Product WHERE Name = 'Bearing Ball'";
cmd.ExecuteNonQuery();

Then when you run your statistics query, look for the query that contains your statementMagicStringMarker, and you'll know you have the same statement, even though it may be cleaned-up. By using Guid.NewGuid() you know you'll get a unique marker for every query, even if running from different clients.

Answer 2

It is possible to normalize T-SQL code using the ScriptDom. You would need to strip off the prefixed parameter list before parsing though as ScriptDom only parses valid T-SQL.

Microsoft.Data.Schema.ScriptDom is however an old version, which was shipped with Visual Studio. Since SQL Server 2012 a new version is shipped with SQL Server itself. This has a namespace Microsoft.SqlServer.TransactSql.ScriptDom and is kept up to date with the most recent SQL server versions. Programming ScriptDom is however no easy task. Normalizing T-SQL code is an even more complicated task. Arvind Shyamsundar has posted a blog on the topic at msdn blogs. The method demoed by Arvind is to normalize all copies of code, then checksum the resulting statements, storing the results in a dictionary using the checksum for a key. If the checksum is the same, your T-SQL must be equivalent to one that was put in before. This idea will work for you too, although probably not all normalizations in Arvind's demo are useful for your use case; you may have to turn some off, like normalizing literal values.

Arvind has a zip attached to his blog with his demo code in it, so you can start from there easily. I have asked Arvind in a comment to publish his code on GitHub so we can add our extensions, but haven't heard from him yet. I extended -for my own purposes- on Arvind's demo to do even more normalizations like eliminating superfluous braces, normalizing boolean logic and normalizing scalar expressions. Plus I am working on even more normalizations like normalizing join statements and join clauses. But this proves to be rather complicated... I am willing to share my code, send me a message if you think you can do something with it.