BT

InfoQ Homepage News Tips on query normalization with SQL Server 2005

Tips on query normalization with SQL Server 2005

Bookmarks

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.

Rate this Article

Adoption
Style

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Community comments

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

BT

Is your profile up-to-date? Please take a moment to review and update.

Note: If updating/changing your email, a validation request will be sent

Company name:
Company role:
Company size:
Country/Zone:
State/Province/Region:
You will be sent an email to validate the new email address. This pop-up will close itself in a few moments.