Forced and Simple Parameterization in SQL Server 2005

  • [アーティクル]

As the optimizer gets more and more sophisticated, cost of optimization naturally increases. When that is the case; simple and forced parameterization comes to the rescue (much like you neighborhood plumber). These features simply allow the reuse of execution plans from similar queries thus minimizing the parse & compile time. The benefits vary from workload to workload but applications with simpler-but-chatty query workloads that spend relatively large percentage of the execution time in compilation (parse and compile time outputed from STATISTICS TIME output) will benefit greatly from these features in SQL Server 2005.

Many of you are probably familiar with auto or simple parameterization so I’ll jump to the new forced option right away. This is a new database level option in SQL Server 2005. It simply lets you parameterize a much wider range of queries. The following queries for example would not get parameterize in SQL Server 2000 or 2005 so they would by default be parsed and complied twice.

   "select top 1 c1 from t1 where c2=0" & "select top 1 c1 from t1 where c2=1"

However force parameterization can help turn this query into a parameterized form that gets reused on the second execution.

   "(@0 int)select top 1 c1 from t1 where c2 = @0"

Forced Parameterization comes with a warning however: sometimes you want similar queries to recompile and use distinct execution plans. With forced parameterization queries may end up sharing plans inappropriately. I highly recommend you digest the information on the book online page and test before you decide to enable this in your environment.

What I’d like to do for the rest of the text is to talk about what you will oserve in SQL Server 2005 execution plan cache with parameterization options, as it may seem a little unusual once you start digging in.

First, if you look into your good old syscacheobjects or the new sys.dm_exec_cached_plans views, you will still see the individual statements with the literals in the query text in your execution plan cache (both in simple and forced parameterization). However if you observe closely you will see that we do not have full blown execution plans for these objects with literals (sys.dm_exec_query_plan) and naturally we do not keep query statistics (dm_exec_query_stats) because they never really get executed. Their purpose in life is mainly to serve as pointers back to the actual parameterized plan. In fact the sys.dm_exec_cached_plans.refcounts of the parameterized plan reflects the count of these empty objects in the cache. The presence of these objects does not mean simple or forced parameterization is not doing its job. As long as the parameterized version is in the cache, you are in good shape. You could use something like the following query to see the query text and the plans of the cached queries:

select c.text,a.refcounts,a.usecounts,d.query_plan from sys.dm_exec_cached_plans a outer apply sys.dm_exec_plan_attributes(a.plan_handle) b outer apply sys.dm_exec_sql_text(cast(b.value as varbinary)) c outer apply sys.dm_exec_query_plan(a.plan_handle) d where b.attribute='sql_handle' and a.cacheobjtype = 'Compiled Plan' order by a.usecounts desc

I also have seen a few people trip over this: when looking at compilations, performance monitor (perfmon) counters can be deceptive. You may see a none-zero SQL Compilations/Sec value even after enabling forced parameterization. Don’t panic… We still report compilation with simple/forced-parameterized queries. If SQL Server 2005 is not reporting Failed or Unsafe Auto-Params/Sec, then we are doing all we can to reuse execution plans.

Happy Testing…

Cihangir Biyikoglu

Comments

  • Anonymous
    April 30, 2006
    Is it my imagination or is 2005 running slower?My first impresson with SQL Server 2005 came...
  • Anonymous
    April 30, 2006
    Is it my imagination or is 2005 running slower?My first impresson with SQL Server 2005 came...
  • Anonymous
    July 23, 2007
    You may be aware using SP_WHO or SP_WHO2 statements you can get information about a session id and statement