Understanding parameterization

We briefly introduced autoparameterization in Chapter 2, Troubleshooting Queries, while covering the query_hash and plan_hash values. To understand how SQL Server caches a plan, along with the different mechanisms by which a plan can be reused, you need to understand parameterization in more detail. Parameterization allows an execution plan to be reused by automatically replacing literal values in statements with parameters. Let’s examine those queries again, but this time using the sys.dm_exec_cached_plans DMV, which you can use to return each query plan currently cached by SQL Server. One particular column, usecounts, will be useful because it returns the number of times a specific cache object has been looked up in the plan cache, basically indicating the number of times the plan has been reused. The cacheobjtype and objtype columns, which were introduced in the previous section, will be used as well.

Let’s look at the following query...