3

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 IN clause.

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.

Questions:

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.

  • Just brainstorming here... have you tried to replace the IN clause with an INNER JOIN to try and take advantage of cached plans? Assuming your queries are procedure based, you could test it with and without "WITH RECOMPILE"? – scsimon Apr 25 '17 at 18:26
  • @scsimon Unfortunately the list of values provided to the IN clause is essentially arbitrary, which is why I'm using it instead of a JOIN. If I could, I would :) – Donut Apr 25 '17 at 18:44
  • [this is what i meant(rextester.com/EVY47167)], which of course will be dependent on your indexes. – scsimon Apr 25 '17 at 19:10
  • In an Oracle world Imwould try to have a variable number of arguments split into some well known batches (1010 into 500+500+5+5). The splitting is most often needed anyway if the list can be large. – eckes Apr 26 '17 at 2:55
1

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)?

Paul White explained it here with examples.

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)?

You need do some collection and aggregation for this one.

  • Once you compare the performance of your rewrite as explained in Paul's article you know the difference (in io, cpu) for one call + time for recompilation.
  • Multiply that by number of calls (of the changed query) in x period.
  • Add those numbers for all the query you rewrite.
  • Another approach is to benchmark you overall workload, make change to small number of queries and compare with baseline.
  • If moving in the right direction make more changes (provided workload did not change),otherwise go back to starting point. Evaluate one by one.
  • Thanks for taking the time to answer. The blog post you linked is great; this is exactly the sort of info I was hoping to get. Thanks! – Donut Apr 25 '17 at 20:50
  • Glad that you found it useful. – SqlWorldWide Apr 25 '17 at 21:52
  • Paul whites explanation is a good read, but how is it related to IN forced parameter binding and variable parameter number? – eckes Apr 26 '17 at 2:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.