SQL server CPU 100% - Turn on Optimize for Adhoc Workload Experiencing high CPU the more connections there are, no blocking or long running queries on the server. 

Highest wait types are:
SOS_SCHEDULER_YIELD &amp; 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?
You can turn on "Optimize for Adhoc Workloads" with a high confidence.

The only potential effect this has, is when a plan is compiled the second time, it takes a few microseconds longer. This is because the first time a plan is cached, it only records the stub.

After that, the full plan is already cached, and you'll see the benefits.



Aside: if you're seeing high CPU as a result of a new OR/M, I'd look at something called implicit conversion.

What happens is that on string data types (CHAR, VARCHAR, NCHAR, NVARCHAR), you might be seeing Unicode vs ASCII conversions between the database and your application code.

The best way to look for this is to make sure your ORM matches the string data types correctly. If it's VARCHAR in the database, make sure the ORM doesn't submit parameters as NVARCHAR.

More reading here: Convert Implicit and the related performance issues with SQL Server.