BT

Tips on query normalization with SQL Server 2005

| by James Vastbinder Follow 0 Followers on Nov 20, 2006. Estimated reading time: less than one minute |

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 Stage
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.

Tell us what you think

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

Email me replies to any of my messages in this thread
Community comments

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

Email me replies to any of my messages in this thread

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

Email me replies to any of my messages in this thread

Discuss

Login to InfoQ to interact with what matters most to you.


Recover your password...

Follow

Follow your favorite topics and editors

Quick overview of most important highlights in the industry and on the site.

Like

More signal, less noise

Build your own feed by choosing topics you want to read about and editors you want to hear from.

Notifications

Stay up-to-date

Set up your notifications and don't miss out on content that matters to you

BT