Experiencing high CPU the more connections there are, no blocking or long running queries on the server.
Highest wait types are: SOS_SCHEDULER_YIELD & CX_PACKET
Cache Usage %:
UsrTab 0.01 Default 0.01 Rule 0.04 Trigger 0.06 Prepared 0.64 Proc 0.7 View 2.05 Adhoc 95.65
Adhoc queries are excessive and have seemed to increase since use of ORM functionality has increased.
I would like to turn on Optimize for Adhoc Workload but want to know the pitfalls, also what are the implications of turning this on in a production environment?
Will enabling this option help reduce CPU load?