February 22, 2011 at 10:47 am
Hello. Has anyone seen where the max degree of parallelism setting under sp_configure is ignored? We set ours to 4 recently; restarted that SQL instance (which I'm reading isn't necessary) and have seen over 20 instances of the same SPID in there since.
We're still on SQL 2008 SP1; affinity masks and cost threshold for parallelism are set at their defaults. This SQL instance is 1 half of an active-active cluster where the other side doesn't have it's MAXDOP set but I don't think that shouldn't matter.
Thanks,
Ken
February 22, 2011 at 11:16 am
MAXDOP set at the server is the default, not an override.
How are the problem queries being called, do you know the specific calls causing the issue (DBCC INPUTBUFFER), what are the waitstates on the multiple SPID instances, and how are you finding your multiple SPID instances?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 22, 2011 at 11:20 am
I don't think you can draw anything conclusive about DOP based on how many threads you see executing. Even with One CPU a query can be handled with multiple SQL Server threads.
The probability of survival is inversely proportional to the angle of arrival.
February 22, 2011 at 12:01 pm
Select * From vExecutiveSummary, not that I'd ever do that :-D. Looks like the waittypes are CXPACKET. I'll get a better idea when some heavy user reports are run.
select s.spid, COUNT(*) from sys.sysprocesses s where s.spid > 50 group by s.spid having count(*) > 1 order by s.spid.
Assumption here is that none should exceed your MAXDOP server setting.
Ken
February 22, 2011 at 12:26 pm
ken.trock (2/22/2011)
Select * From vExecutiveSummary, not that I'd ever do that :-D. Looks like the waittypes are CXPACKET. I'll get a better idea when some heavy user reports are run.select s.spid, COUNT(*) from sys.sysprocesses s where s.spid > 50 group by s.spid having count(*) > 1 order by s.spid.
Assumption here is that none should exceed your MAXDOP server setting.
Ken
That's an inaccurate assumption. You should never exceed the MAXDOP setting on the connection or on the query call itself (see: WITH OPTION( MAXDOP x)).
I'll have to think on this one. I assume SELECT * FROM view is coming in as a dynamic process from an app?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 22, 2011 at 2:22 pm
Most of the time it's users running reports out of our Crystal Enterprise environment.
February 22, 2011 at 3:49 pm
The degree of parallelism setting applies to individual operators in a query plan, eg. a hash-match operator could itself be spreading its work over several processors. Since a query plan consists of many such operators, you can possibly end up seeing multiple such threads, many more than the MAXDOP setting itself.
In that sense, it is not too surprising that you are seeing many more threads than what you would expect from the MAXDOP setting.
Examine the query and look for possible missing indexes in the query plan XML.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 22, 2011 at 11:21 pm
ken.trock (2/22/2011)
Hello. Has anyone seen where the max degree of parallelism setting under sp_configure is ignored? We set ours to 4 recently; restarted that SQL instance (which I'm reading isn't necessary) and have seen over 20 instances of the same SPID in there since.
Hi Ken,
Yes that is normal, and expected.
When MAXDOP = 1, the whole query runs as 1 task (sys.dm_os_tasks). A task runs on a worker thread (sys_dm_os_workers, sys.dm_os_threads) to completion. A worker thread runs on a single scheduler. There is one (online and visible) scheduler (sys.dm_os_schedulers) for each logical processor available to SQL Server. Each task executes within an execution context, which for a serial query is always execution context 0 (ecid 0).
A parallel query is broken into plan regions, which are separated by Parallelism operators. The three types of Parallelism operator are Distribute Streams, Repartition Streams, and Gather Streams. Each region, is either serial or parallel.
A serial region runs exactly as described above (1 task, 1 worker thread, 1 scheduler, 1 execution context). Each parallel region runs using n tasks, n worker threads, inside n execution contexts - where n = the runtime degree of parallelism (DOP), as decided by the Query Executor just before execution starts.
Runtime DOP enforces how many schedulers are available to the query overall. There can be many more worker threads, tasks, and execution contexts. (Note SQL Server 2000 had a bug which meant runtime DOP was not enforced correctly - fixed from 2005 onward).
A plan might have two parallel regions and a serial region. If runtime DOP = 4 there will be 9 tasks, 9 worker threads, 9 execution contexts (9 = 2 regions * 4 DOP + 1 serial region) but the 9 worker threads will be assigned to one of 4 schedulers. This ensures that only 4 threads are active on a logical processor at once.
So, you will often see more tasks, workers, threads, and execution contexts than the MAXDOP setting - but no more than DOP will be actively running on a processor at the same instant.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 23, 2011 at 1:08 am
Thanks Paul, I was worried I was barking up the wrong tree. Apparently I was in the wrong forest. 🙂
I'm going to need to read that a few times to get it completely, but thanks again for the clarification.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 23, 2011 at 4:46 am
Craig Farrell (2/23/2011)
Thanks Paul, I was worried I was barking up the wrong tree. Apparently I was in the wrong forest. 🙂I'm going to need to read that a few times to get it completely, but thanks again for the clarification.
There's a useful Microsoft Technical Paper on this:
http://technet.microsoft.com/en-us/library/gg415714.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 23, 2011 at 9:32 am
Thanks everyone. There's obviously more to this than meets the eye.
February 23, 2011 at 9:38 am
SQLkiwi (2/23/2011)
Craig Farrell (2/23/2011)
Thanks Paul, I was worried I was barking up the wrong tree. Apparently I was in the wrong forest. 🙂I'm going to need to read that a few times to get it completely, but thanks again for the clarification.
There's a useful Microsoft Technical Paper on this:
Thank you for the valuable info and the link, much appreciated.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply