December 19, 2006 at 11:04 pm
Hey guys,
question for anybody, hot debate here is that we have a SQL statement that has 2 unions in it, fairly simple and nothing complex. What I'm asking is does sql server when running the queries;
1). Run each query on each side of the union in Parallel (if it can), then combine the results? ie each query is run serially in Parallel mode and then combine the results
or
2). Run each Query, both at the same time in parallel ( if it can) and then combine the results?
Query plans can tell you what is happening for each individual query, but not he big picture..
thoughts???
Mike
December 20, 2006 at 5:59 am
examining the query plan will give info on parallelism. I don't really understand the point of the question - if you want to control parallelism in each part of the query then add hints.
The whole plan would be evaluated but the degrees of parallelism may or may not be applied to the parts of the query, but you'll only find this out by examining the query plan either as a text output or from profiler, the graphical plan possibly won't give the granularity.
So the probable answer to 1 and 2 is yes, maybe, it depends. For those of us from back in the 6.0 days it's worth noting the sql server ( and sybase ) did and still tend to run sequential queries better than wide queries, I have instances where an eight table join performs better as three seperate queries rather than one query ( to obtain the same result set )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 20, 2006 at 10:53 am
If you do SET SHOWPLAN_ALL ON and run the query it tells you where or not the queries were executed in parallel. Generally, not.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
December 20, 2006 at 12:02 pm
As mentioned before You can use SET SHOWPLAN_ALL ON or GRAPHICAL EXECUTION PLAN to find the query is using parallalism or not...And you can force by using MAXDOP option...
But SQL server optimizer decides to use parallalism or not based on COST THREASHOLD...even though parallalism is enabled on multi processor server.
Read BOL topic "cost threshold for parallelism Option"
SQL Server creates and executes a parallel plan for a query only when the estimated cost to execute a serial plan for the same query is higher than the value set in cost threshold for parallelism. The cost refers to an estimated elapsed time in seconds required to execute the serial plan on a specific hardware configuration. Only set cost threshold for parallelism on symmetric multiprocessors (SMP).
MohammedU
Microsoft SQL Server MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply