Microsoft's documentation on Caching Mechanisms contains a description of Autoparameterization. As I understand it, Autoparameterization allows SQL Server (under certain circumstances) to use a previously cached execution plan for a query even if the specific parameters for that instance of the query are unique.
However, the documentation on that page states that "there are many query constructs that normally disallow autoparameterization". One of these constructs is the
I have several queries in my application that use the
IN clause. Each of these queries could have an arbitrary number of values in the
IN clause. If I understand the documentation referenced above, then the execution plans for these queries will not be cached.
1) Is there a recommended way to write these sorts of queries in order to bypass this limitation and take advantage of cached execution plans (other than using forced parameterization at a database level)?
2) How can I evaluate the impact that these queries are having on the overall performance of the database (e.g., how can I answer quantitatively whether or not it is worth my time to worry about the lack of cached execution plans)?
Thank you in advance for any insight you can provide.