Tips on query normalization with SQL Server 2005

Posted by James Vastbinder on Nov 20, 2006

Sections

Development,

Operations & Infrastructure

Topics

.NET ,

SQL Server

Tags

SQL Server 2005 ,

Database

The common problem situation faced by SQL Server developers is a desire to normalize their queries.  The advantages of using a normalized query provides the developer with a way to track executions of a query with different parameters and over time monitor execution performance.  To normalize a query is to replace the literals in the query with parameter markers like this example from Ken's blog post.

SELECT * FROM foo WHERE id=4

Here, the parameter is “4”, so, parameterized, the query would look something like this:

SELECT * FROM foo WHERE id=@p1 

Often, in seeking to simplify workload, developers will fall into common pitfalls, like trimming a substring query to its bare essentials.  This approach is prone to error since you cannot accurately define a substring length such that it will always normalize a query.

The best path for developers involves utilizing sp_get_query_template which takes the text of a query as a parameter and returns both the normalized text and the parameter list.  There are some limitations to this approach which Ken outlines in his blog, but when seeking to tune your SQL Server database indexes and queries this method provides the richest set of performance aggregation information.

• Older >