We have a process that creates a new sequence using dynamic sql. We then use dynamic sql to get the next value from the sequence. We have a lot of these dynamic sequences, but don't call any individual one a lot, but we call a lot of them.
In the plan cache it looks something like this:
(@new int OUTPUT)SELECT @new = Next Value for dbo.[UniqueSequenceName1] (@new int OUTPUT)SELECT @new = Next Value for dbo.[UniqueSequenceName2] --etc
These are eating up a lot of our plan cache, and tend to only be called once before they're pushed out. I have Optimize for Ad Hoc Workload turned on, but that hasn't changed the behavior. Would turning on forced parametarization help reduce the load on the plan cache? Is there another setting that might help?