April 16, 2014 at 12:18 pm
Hi everyone,
I wanted to ask the group's personal experience with resource gov and query hint w/maxdop.
http://technet.microsoft.com/en-us/library/ms181714.aspx
They are two conflicting statements? (which is true?) The first part states it overrides sp configure and resource governor. If resource gov maxdop is lower than the query hint.
Overrides the max degree of parallelism configuration option of sp_configure and Resource Governor for the query specifying this option. The MAXDOP query hint can exceed the value configured with sp_configure.
If MAXDOP exceeds the value configured with Resource Governor, the Database Engine uses the Resource Governor MAXDOP value, described in ALTER WORKLOAD GROUP (Transact-SQL). All semantic rules used with the max degree of parallelism configuration option are applicable when you use the MAXDOP query hint. For more information, see Configure the max degree of parallelism Server Configuration Option.
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
April 16, 2014 at 12:48 pm
No conflict.
Maxdop query hint overrides the server Maxdop setting.
If resource governor is enabled and a Maxdop is set via resource governor, then that is the max value allowed. A query hint can set it to a lower value, but if a query hint specifies Maxdop above what the resource governor allows, the resource governor setting is used.
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
April 16, 2014 at 3:21 pm
GilaMonster (4/16/2014)
No conflict.Maxdop query hint overrides the server Maxdop setting.
If resource governor is enabled and a Maxdop is set via resource governor, then that is the max value allowed. A query hint can set it to a lower value, but if a query hint specifies Maxdop above what the resource governor allows, the resource governor setting is used.
Thanks Gail 🙂
The first sentence was confusing, but the last part indicated exactly what you stated. Thank you for confirmation.
The came about because ...
I had a sproc that was running with query hint maxdop 8 - then I turned on resource gov for the service acct that calls that sproc
During that same week I turned on resource governor w/maxdop 4. There was no change to the performance of the sproc. (which was good for me - based on books online I thought it was wrong)
Then later noticing some (separate issue) deadlock issues w/parallelism turned on for all sprocs under that service account I reduced it to maxdop 1 resource governor.
Then a change in the behavior occurred, the query ran slower due to maxdop I believe.
The original query must have been running faster due to cached plan.
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
April 16, 2014 at 3:31 pm
Cached plans don't contain the degree of parallelism. If there's a parallel query in cache, it's the query execution engine which decides what DOP to use or whether to discard the parallel portions and run the plan as a serial plan
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
April 16, 2014 at 4:26 pm
GilaMonster (4/16/2014)
Cached plans don't contain the degree of parallelism. If there's a parallel query in cache, it's the query execution engine which decides what DOP to use or whether to discard the parallel portions and run the plan as a serial plan
Thanks for pointing that out. I believe this is what was happening now
No resource gov => Sproc used MAXDOP 8, performance was good
Yes resource gov DOP4 => Sproc used MAXDOP 4, performance was still good (neglible difference in performance)
** this reduced the dop for query hint, but still ran well so went unnoticed.
Yes resource gov DOP1 => Sproc used MAXDOP 1, performance was not good.
More clear now after typing this out. Thanks again Gail! 🙂
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
April 16, 2014 at 4:43 pm
That makes sense.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply