Inquiridor

Forced parameterization

Pergunta

In one of our servers, we find high "plan count" values in the activity monitor for a number of ad-hoc queries. Do these high ﻿"plan count" values indicate that enabling forced parameterization for the database would help improve the performance as
plans would be reused?

One of our servers is experiencing performance problems, queries are running very slow with high io and cpu, high pageiolatch waits etc. We are at a point where we can't add more memory with the server.

I found that the server currently has around 30000 ad hoc plans in the plan cache (optimize for ad hoc workloads option is enabled). Total ad hoc plans with use count = 1 is around 13000. We cannot do much to change the queries sent by third party applications.
So I am checking the forced parameterization option, if it would help improve the performance. Any thoughts on whether we should enable the option?