2

I'm using parametrized SQL queries in SQL Server 2012 SP1.

Refer to sys.dm_exec_cached_plans and sys.dm_exec_query_stats, it seems that the optimizer creates same plans multiple times instead reusing one already existed.

The server have lots of cached plans with the same query_hash and query_plan_hash, (exactly 4700 plans at the moment and increasing).

My questions are:

  1. Is this situation normal?
  2. Why optimizer create a new plan again and again which is already exists?
  3. Is there a good way to force the optimizer reuse query plans in terms of parametrized SQL?

migrated from serverfault.com Sep 12 '14 at 6:59

This question came from our site for system and network administrators.

  • What is your setting for parameterization, simple or forced? Do you have optimize for ad hoc workloads enabled? Are you absolutely sure that both of those values are exactly identical across all 4,700 rows? – Aaron Bertrand Sep 12 '14 at 7:28
  • Thank you for your comment. Could you tell me how to determine the parameterization setting? Actually, I'll check with DBA. – Minkyu Shim Sep 18 '14 at 12:19
10

You've got a few different questions in here:

Q: Why optimizer create a new plan again and again which is already exists?

These attributes all affect plan reuse:

  • Different databases each get their own plans
  • Different parameter lengths get their own plans (especially with this NHibernate bug, but similar issues can occur with other ORMs as well)
  • Different connection settings like ANSI NULL can get their own plans
  • Different spacing or comments can get their own plans (like if your code is dynamically adding comments to indicate where the query came from)

Q: Is there a good way to force the optimizer reuse query plans in terms of parameterized SQL?

Yes - think about the above things that will impact plan reuse. Obviously if you have a Software-as-a-Service type model where every client gets their own database, you can't really help things there, but you can help the other two factors. Be consistent with your application's connection settings, don't inject different spacing or comments dynamically into queries, and make sure your datatypes in the application match the datatypes in the database.

Q: Is this situation normal?

It's not unusual to see out in the field, especially with older NHibernate/EF versions that used different parameter lengths based on whatever parameter you were passing in (as discussed in that link.)

The performance symptoms are higher CPU use (due to increased query compilations) and higher plan cache memory use, but they're not usually the largest problem on a SQL Server.

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.