Hi Ali

thx for the reply. However, I posted this topic after reading that article 🙂

What I'm confused about is that there's more plans in the cache and more compilations occurring while CPU usage decreased than when using parameterization=simple.

I'll try to explain what's happening in more detail...

There's an ad-hoc UPDATE statement being run about 4000 times per minute. Almost all 4000 statements are different in terms of parameter values and WHERE condition hence about 4000 cached plans. I was seeing about 100 compiles per second (not re-compiles) and rather high CPU usage which I attributed to the compiles.

When I set PARAMETERIZATION=FORCED on the database, CPU usage dropped significantly and procedure cache hit increased by 10%. That was a good thing and was as I expected.

However, I would have thought that the number of cached plans would have dropped significantly as well because exactly the same statements but with different parameter values would use the same plan. Instead the number of cached plans increased (x3). Also, the number of compiles trippled. Vary odd because I thought having that much compiles/sec would just increase CPU usage even more.

So, I'm still puzzled...

Regards

Thierry