Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2005-12-31 : 12:08:06
|
The icons in this plan on my remote server are new to me... These icons are more familiar and appear for the same query and data on my local server. So, what's different about the "clustered index seek" on pagehits in the first image? |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2005-12-31 : 13:46:24
|
| Essentially, what's happening in the first execution plan is that the result of the Modules seek is being used to create multiple hash* tables, one for each parallel thread, and then the seek to pagehits is being executed by each parallel thread (I'm not sure how the threads decide amongst themselves which thread gets which leaf pages of the index) and each value is looked up in that thread's hash table.This generates a stream of results from each thread that needs to be either Gathered together for further non-parallel operations or Repartitioned for further parallel operations. See BOL under "Parallelism" for details.You should be able to see on the actual execution plan (though not the estimated plan) a difference in the number of rows going into the Parallelism/Broadcast operator and coming out the other side. There should also be a corresponding number on the "Number of executes" detail on the Hash Match/Inner Join operator showing what degree of parallelism was used.You can force the maximum degree of parallelism by using theOPTION (MAXDOP n) on the end of the query, so with n set to 1 this will produce a non-parallelized plan.* If the seek on the build side of a hash join is non-parallel, why does it create multiple identical hash tables? Paul? |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2006-01-01 : 10:47:42
|
| Thanks and Happy New Year Arnold!A few questions...What's a parallel thread? Something to do with multi-cpu or hyperthreading?Is this parallel thread plan better than the 2nd (more familiar) plan and why? |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2006-01-01 : 10:50:13
|
http://www.sql-server-performance.com/jc_parallel_execution_plans.asp quote:
There is no rational reason to believe that having two or more threads separately process portions of the index seek or hash match operations reduces the total CPU-cycles in processing the entire operation, except under unusual circumstances. In fact, the parallel operation should be more expensive taking into account the cost of merging the partial results from each thread.
|
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2006-01-01 : 10:59:44
|
| I'm sure I have a single-CPU Xeon processor which support hyper-threading. I wonder why I haven't noticed these parallel execution plans earlier? Parallel plans have been around since SQL 7.Some configuration option must have been set to support this on the server perhaps? |
|
|
|
|
|
|
execution plan differences