SQL Server is caching query plans with the same query when using parameterized SQL 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:


Is this situation normal?
Why optimizer create a new plan again and again which is already exists?
Is there a good way to force the optimizer reuse query plans in terms of parametrized SQL?
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.