October 29, 2010 at 11:36 am
Setup
Cost of Threshold for Parallelism : 5
Max Degree of Parallelism : 4
Number of Processors : 8
SQL Server 2008 10.0.2.2757
I have a query with many joins, many records.
The design is a star. ( Central table with fks to the reference tables )
The central table is partitioned on the relevant date column.
The partition schema is split by days
The data is very well split across the partition schema - as judged by comparing the sizes of the files in the filegroups assigned to the partition schema
Queries involved have the predicate set over the partitioned column. such as ( cs.dte >= @min_date and cs.dte < @max_date )
The values of the date parameters are a day apart @ midnight so, 2010-02-01, 2010-03-01
The estimated query plan shows no parallelism
a) This question is in regards to Sql Server 2008 Database Engine. When a query in the OLTP engine is running, I would like to see / have the sort of insight one gets when profiling an SSAS Query using Progress End event - where one sees something like "Done reading PartititionXYZ".
b) if the estimated query plan or the actual query plan shows no parallel processing does that mean that all partitions will be / were checked / read?
c) suggestions? Is there more information that I need to provide?
d) how can I tell if a query is processing in parallel w/o looking @ the actual query plan?
October 29, 2010 at 12:35 pm
I know you can trace to see what degree of parallelism is being used; it is an event. As far as which partition is used, I thin the event data field associated with the lock-aquired event will show you the partition_id involved with the lock; you can cross reference that with sys.partitions to see which one is used.
Try that out; let me know if that helps.
October 29, 2010 at 12:55 pm
Great answer.
Comments :
a) yes - I see the event for parallelism. Thank you! I'll have to work with it to see what I can't get out of it.
b) lock acquired - seems it will take a whole lot of effort to work with / filter down. If you or someone else has specific instructions on how to configure the trace that would be appreciated. I'm working on it right now.
Thank you! I'll post back if / when I come up with some detailed instructions.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply