There are a number of ways to work-around this issue Unfortunately if the initial parameter values are similar to the first example above, then a ‘table scan’ execution plan gets created and cached, even though most of the following queries would rather use a plan that contains the index seek. Whereas, in the second case a query execution plan using index seek would most likely be created. However, since SQL Server only caches one execution plan per query, chances are very high that in the first case the query execution plan will utilize a clustered index scan because of the ‘col1 > 1’ parameter substitution. These two identical parameterized SQL Statements would potentially create and cache very different execution plans due to the difference of the initially passed parameter values. Select * from t where col1 > 6000000 or col2 > 550 order by col1 Now, imagine the query execution plan if the initial parameter values were: = 6,000,000 and = 550.Īs before, an optimal queryplan would be created after substituting the passed parameters: Select * from t where col1 > 1 or col2 > 99 order by col1 Imagine the query execution plan created for the following initially passed parameters: 1 values would result in an optimal queryplan for the following statement using the substituted parameters: Let’s assume for simplicities sake that col1 is unique and is ever increasing in value, col distinct values and there are 10,000,000 rows in the table, and that the clustered index consists of col1, and a nonclustered index exists on col2. Select * from t where col1 > or col2 > order by col1 To make this clearer let’s look at the following example query Unfortunately, this had the unintended side effect of caching a poor execution plan for all subsequent parameter values. SQL Server compiled and cached a ‘good’ plan for the first parameter values. See more about SQL Server parameterization Best Practices here:Īn application that I work with presented me with an interesting dilemma It wanted to utilize the benefits of plan reuse but the parameter values that the application initially sends to SQL Server are not representative of the values passed in the subsequent re-execution of the statement. This technique ensures caching and reuse of existing query execution plans (instead of constantly compiling new plans), as well as avoiding SQL injection by mandating that input data be data type safe. Using parameterized queries is a well known SQL Server Best Practice.
0 Comments
Leave a Reply. |