About Adhoc Optimitation on real scenario

Question

About Adhoc Optimitation on real scenario

paolobianchi

SSC-Addicted

Points: 493
More actions
July 7, 2016 at 1:26 am

#319736

Hi all,
i'm making some performance optimization on a situation like this:
Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
A single Db used by a Web Application (an ERP)
About 100 users concorrent everyday, working 80% on 10/20 foundamental tables (db have about 4000 other tables)
After about 6 months, the DB is about 35 Gb
Worked on index, single query optimitation with developers, etc. etc, and we obtain many benefits!
Now i'm thinking about activiting Optimize for Adhoc Workload.
My analysis is:
--Numbers of plan in cache
select count(*) from sys.dm_exec_cached_plans
Result: 29582
--Numbers of plans used 1 time, between 2 and 5, more than 5 times:
select count(*) from sys.dm_exec_cached_plans where usecounts=1
21866 (73% of total)
select count(*) from sys.dm_exec_cached_plans where usecounts>1 and usecounts<=5
4027 (13% of total)
select count(*) from sys.dm_exec_cached_plans where usecounts>5
3881 (13% of total)
-- Another query about this, results:
Total cache plan size (MB): 2410.78. Current memory occupied by adhoc plans only used once (MB):1652.98. Percentage of total cache plan occupied by adhoc plans only used once :68.57%
About 70% of plan is used one time, so i think that activating Adhoc Optimize is a good idea...
Any suggest about it? A good idea? Not a good idea? Risks?? Benefits?
(i have a testing db but it is on same instance so i can't activate it to "test", and i don't have another "test situation" with so many users to test...)
Thank you

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Definitely enable Optimise for Ad-hoc workloads with figures like that. It's a safe setting to turn on in general.

Are those queries parameterised? If not, it may be a good idea to work with the developers to parameterise the queries being sent to the server.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

paolobianchi SSC-Addicted Points: 493 More actions · Answer 2

Hello,

thank you for the tip, my only fear is about changing setting in prod because people almost always work on it and i don't know if changing it can, temporary, drop performance!

For query parameterised, we can't, the erp is developed with a case that generate automatically the queries and don't implement param query, sure this is a thing that i will discuss with the sector who develope the "developer tool" 😉

Thank you

Paolo

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Well, like any changes, you don't want to make it slap in the middle of the business day. Same as any changes, do them at the quietest time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

If you don't update statistics manually you could wind up with some very different query plans (both for the better and perhaps worse) when you switch this on. However, the majority of your queries are getting compiled at execution time anyway, so server load from that should be minimal with perhaps a brief spike immediately after the change.

I agree with Gail it is most often recommended to enable this setting.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

paolobianchi SSC-Addicted Points: 493 More actions · Answer 5

Hello,

thanks to everybody

Changed the setting, no problem with cpu overloading.

After a few hours, situation of cached plan changed...

Before,

CacheTypeTotal PlansTotal MBsAvg Use CountTotal MBs – USE Count 1Total Plans – USE Count 1

Adhoc292052032.31250091412.79687520607

Prepared2800217.437500200152.9921872007

Proc5728.20312593716.48437510

Check140.48437520.1875005

Trigger312.3671879130.0625001

After,

CacheTypeTotal PlansTotal MBsAvg Use CountTotal MBs – USE Count 1Total Plans – USE Count 1

Adhoc827511582.62591517766.07904070347

Prepared8173611.38281210441.9375006684

Proc192.453125750.1484372

Check20.03906260.0000000

Trigger221.6093752940.0000000

Using about 2Gb of ram, before and after, but before i had about 31k plan cached, now about 90k...

Seems better, no?

I can't evaluate immediately better performance but i think activing this option was a good idea, just like you said.

Thank you

Paolo

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

There are cases where the number of cached plans can actually become a bottleneck in that the hashing and specifically hash lookups can take too long or create their own resource contention. I can't remember the wait types that would indicate this issue at the moment, but I have seen it before in my consulting business.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service