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?

  • What happened when you enabled it in a lower environment and ran a load test? Random people with no insight into your application are likely in a poor position to guess at what the impact of changing a parameter would be for your application. Based on the minimal information you've provided, it sounds reasonable but sounding reasonable to someone on the internet realistically only means that it makes sense to do it in dev and test it out not that you should run out and change the production system. – Justin Cave Nov 14 '16 at 18:29
  • Tested it in a couple of non production environments and cant see any noticeable impact but then again its not production so has fraction of the users. – davey Nov 14 '16 at 18:32
  • Can you run a load test in one of those lower environments? Obviously, if you're looking for an impact in an environment where there is no load and CPU never goes over 1%, no parameter change is going to do anything detectable. If you get the QA department to spin up 5, 50, 500, or 5000 simultaneous virtual users, though, you'll probably see some impact. – Justin Cave Nov 14 '16 at 18:35
  • haha QA department :).. I wish ..i could run some scripted SQL load tests but nothing will be close to the random requests made by end users/ virtual users – davey Nov 14 '16 at 19:09
  • 1
    Since optimize for adhoc workload affects only storing / reuse of plans, it most likely isn't going to help you. Have you looked at plan cache for the queries using most worker time? There could be something easy to spot from there like huge sorts, UDFs etc. – James Z Nov 14 '16 at 19:11

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.

  • Thanks will looks into the conversion types i think in most cases the Devs match them. I did read that ORMs can also have a tendacy to increase adhoc requests as well, and the servers SQL compilations per batch requests is a round 40% not ideal , this figure should be closer to 10%. So nothing seems to be getting reused from cache. Queries seem to be very quick with no blocking, just high CPU – davey Nov 14 '16 at 19:18
  • 1
    Yeah, that's ORMs for you. You could also try adding Forced Parameterization, but that you'll have to test solidly to see if it helps. It might, it might not. Try get the developers to use stored procedures and see how compilations drop! – Randolph West Nov 14 '16 at 19:19
  • Yep bit skeptical about turning on forced Parameterizationj ust yet...looking for a quickish fix hence adhoc workload feature haha, yep sprocs are the way to go but ORMs are easier for the devs :( – davey Nov 14 '16 at 19:26
  • I've added both with little effect. I would have thought Forced Parameterization would have helped here but still getting loads of adhoc queries in cache 100,000 + in the space of 30mins – davey Nov 16 '16 at 8:07
  • Yeah, I have a customer with that problem too, for the same reason. You can clear them out using a scheduled process in the SQL Agent. – Randolph West Nov 17 '16 at 1:59

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.