Risks Of Using Resource Governor To Set Max DOP

Joe Obbish builds an example where Resource Governor’s CPU cap can actively harm query performance:

I uploaded the query plan here if you want to look at it. This type of scenario can happen even without Resource Governor. For example, a compiled parallel query may be downgraded all the way to MAXDOP 1 if it can’t get enough parallel threads.

The query performs significantly worse than before, which hopefully is not a surprise. A single execution took 12860 ms of CPU time and 13078 ms of elapsed time. Nearly all of the query’s time is spent on the hash join for the index intersection, with a tempdb spill and the processing of additional rows both playing a role. The tempdb spill occurs because SQL Server expected the build side of the hash join to be reduced to 1213170 rows. The bitmap filtering does not occur so 8 million rows were sent to the build side instead.

Read the whole thing.

Related Posts

Indexed View Matching

Erik Darling has a series of posts on indexed views, with the latest covering query matching even when using a keyword in creation of the indexed view itself: There are a whole bunch of limitations in creating indexed views. One of them is that you can’t base the query on DISTINCT. Fair enough, but you can do […]

Read More

Testing Scalar UDF Performance In SQL Server 2019 CTP 2.1

Brian Hansen takes a look at the scalar UDF performance improvements in SQL Server 2019: In one of my sessions, Set Me Up: How to Think in Sets, I discuss a variety of performance-inhibiting query constructs, including scalar UDFs. I thought it would be interesting to take the simple scalar function that I use in the […]

Read More

Categories

October 2018
MTWTFSS
« Sep Nov »
1234567
891011121314
15161718192021
22232425262728
293031