I have recently created a filtered index in one of my databases but it would neither be used nor accepted in an index hint in any query unless I added the option(recompile) hint in it.

it turned out that changing the database parameterization from Forced to Simple as described on this article:

SQL Server Simple and Forced Parameterization

and in this and in this answers made my filtered index work fine.


What should I watch out in my LIVE database to be able to take the decision to change it to simple parameterization or to remain in forced parameterization?

I am wondering why former DBAs have chosen to go for the latter rather than keeping it simple.

  • 1
    If you have a lot of queries that are ad-hoc (ex: SELECT * FROM Table WHERE col1 = '1324') then forced parameterization would let the engine reuse the execution plan for a similar query but with a different literal value. With simple parameterization, the engine considers each one to be a different query. CPU will increase, execution plan cache will take more memory. – Jonathan Fite Nov 16 '18 at 14:54

Your Answer

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

Browse other questions tagged or ask your own question.