October 13, 2015 at 12:17 pm
After carefully analyzing the situation for almost a month, I pulled the trigger and made an exception at work; we did enable trace flag 2371. We have some tables with billion of rows and outdated statistics causing horrible plans. Tried several methods to update those, but did not solve the problem or was too CPU intensive, causing other issues.
Anyway, one of the side effects I am seeing so far is average vCPU went down by almost 40%. Nothing out of usual (besides the flag) has been enabled or was executed. So my assumption is, CPU hungry plans are now gone or reduced. My question is: Has someone who enabled this trace flag already experienced same thing and if he does, can you please confirm?
Please do not reply posting the negatives or this flag, I am fully aware of it, not the reason of my thread. I am just trying to understand this CPU behaviour and if it is a positive side effect of this flag or not.
--EDIT--
Not sure if this may clarify further, but I have SQL Standard edition, not Enterprise, in case someone wonder why this is so painful to me. So I can't do table partition or take advantage of extra RAM, etc.
October 13, 2015 at 2:14 pm
I'm not sure about SQL Server 2012, but with versions prior, old stats on tables with 1 billion+ rows would cause shocking CPU consumption during compilation.
Once stats get updated, CPU consumption would drop on those queries dramatically, even though we were sampling .0017% in order to get the scan to complete.
If you keep more current stats on those big tables, you should see some CPU perf improvement.
-Eddie
Eddie Wuerch
MCM: SQL
October 13, 2015 at 2:31 pm
Eddie Wuerch (10/13/2015)
I'm not sure about SQL Server 2012, but with versions prior, old stats on tables with 1 billion+ rows would cause shocking CPU consumption during compilation.Once stats get updated, CPU consumption would drop on those queries dramatically, even though we were sampling .0017% in order to get the scan to complete.
If you keep more current stats on those big tables, you should see some CPU perf improvement.
-Eddie
Thanks for reply.
That's exactly what I was thinking. In fact, I can see those small CPU spikes after I deployed. I use RedGate though, so I will be able to monitor over time.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply