October 5, 2016 at 9:07 am
We have production issue going on , every day we run multiple instances of jobs to process reports . It starts generating reports really slow untill we restart the entire VM which magically improves the performance ( now that did not worked everytime ) . Couple of days ago we discovered resetting the max dop every morning also fixes the perfomance of the query ( 5 times faster) .
I am seeking help on which direction to go to find out why its behaving like ,
1. Why restting MAXDOP to same number fixes the problem every morning
2. What are factors I should check to confirm that the issue is related to MAXDOP setting
3. Possibility of VM incorrect configuration setup which requires maxdop to reset .
SQL 2008 SP3 , 24 Core CPU MAXDOP is 5 .
October 5, 2016 at 9:11 am
1) Changing the maxdop setting clears the plan cache.
2) It's probably got nothing to do with maxdop
3) Extremely unlikely
You've probably got something like bad parameter sniffing or similar resulting in a bad plan getting into cache. Setting maxdop clears the plan cache, so does restarting SQL. Start by looking at the plans in cache when the query is fast and when it's slow.
If you've got a Pluralsight subscription I have a course on bad parameter sniffing there that walks through cause, identification and resolution of bad parameter sniffing problems.
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
October 5, 2016 at 9:37 am
The reason I mentioned mystery , sometime one restart does not fix the issue and sometime restarting three times fixes the issue .
Same with MAXDOP , we tried resetting few times and it just magically improves the performance after once or twice resetting the value .
I didn't mentioned before , we moved from Physical to VM since when we started to experience performance issues .
Yes , I do have Plural Sight subscription .
October 5, 2016 at 12:14 pm
You've getting 'bad' plans in cache, sometimes the query gets a good plan on the first run (when the restart or maxdop 'works') and sometimes it gets a bad plan on the first run (when the restart or maxdop doesn't 'work')
Stop restarting or changing config settings, it's not fixing things, it's just that they both clear the plan cache and force a recompile as a side effect.
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
October 5, 2016 at 2:34 pm
In addition to what Gail has mentioned, have you seen any sporadic I/O issues?
October 5, 2016 at 3:18 pm
When the process are running slow , CPU goes off the roof and Disk utilization goes down . When the performance improves CPU falls downs and Disk io utilization increases .
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply