title: How can I tell *IF* AND *WHAT* partitions are being accessed - sql server 2008 db engine
body: <p>Setup</p>
  
  <ul>
  <li>Cost of Threshold for Parallelism : 5</li>
  <li>Max Degree of Parallelism : 4</li>
  <li>Number of Processors : 8</li>
  <li>SQL Server 2008 10.0.2.2757</li>
  <li>I have a query with many joins, many records. </li>
  <li>The design is a star. ( Central table with fks to the reference tables )</li>
  <li>The central table is partitioned on the relevant date column. </li>
  <li>The partition schema is split by days</li>
  <li>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</li>
  <li>Queries involved have the predicate set over the partitioned column. such as ( cs.dte >= @min_date and cs.dte &lt; @max_date )</li>
  <li>The values of the date parameters are a day apart @ midnight so, 2010-02-01, 2010-02-02</li>
  <li>The estimated query plan shows no parallelism </li>
  </ul>
  
  <p>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". </p>
  
  <p>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? * What I was trying to say here was - just b/c I don't see parallelism in a query plan, that doesn't guarantee the query isn't hitting multiple partitions - right?   Or - is there a solid relationship between parallelism and # partitions accessed?</p>
  
  <p>c) suggestions? Is there more information that I need to provide?</p>
  
  <p>d) how can I tell if a query is processing in parallel w/o looking @ the actual query plan?  * I'm really only interested in this if it is helpful in pinning down what partitions are being used.</p>
  
  <hr>
  
  <p>Added Nov 10</p>
  
  <p>Try this:</p>
  
  <ul>
  <li>Create querys that <em>should</em> hit 1, 3, and all your partitions</li>
  <li>Open an SSMS query window, and run <code>SET SHOWPLAN_XML ON</code></li>
  <li>Run each query one by one in that window</li>
  <li>Each run will kick out a chunk of XML</li>
  <li>Compare these XML results (I use a text diff tool, “CompareIt”, but any similar tool would do)</li>
  </ul>
  
  <p>You should see that the execution plans are significantly different. In my “3” and “All” querys, there’s a chunk of text tagged as “ConstantScan” that has an entry for (respectively) 3 and All partitions in the table, and that section is not present for the “1 partition” query. I use this to infer that yes indeed, SQL doing what it says it will do, to wit: only read as much of the table as it believes it needs to in order to resovle the query.</p>