Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

If I remove a RID lookup by replacing 2 non-clustered indices (non unique) with 2 identical clustered indicies (non-unique) where there weren't any before, why does this query slow down by a factor of 2X (.7 -> 1.8) and I lose parallelism.

select distinct  
       s1.ObjectId,
       ca.ObjectId2        
  from ##SmallTable1 s1   
 cross apply (
     select top /* pretend this could vary */ 1 L.ObjectId2
       from VeryLargeTable L

       join ##SmallTable2 s2
         on s2.ObjectId = L.ObjectId2
        and IsNull(s1.NumericValue, s2.NumericValue) >= 0.0
        and IsNull(s1.StringValue, s2.StringValue) = '1' 

      where L.ObjectId1 = s1.ObjectId   
        and L.flag1 = 1
        and L.flag2 = 1                  
    ) ca
where (s1.NumericValue is null or s1.StringValue is null)

The output of RID is the StringValue.

The indicies being changed are s1(ObjectId) and s2(ObjectId) from nonclustered to clustered.

Without the distinct, both runtimes are slow and DOP 1 (~2s) and is same answer (for this example).

When ca.ObjectId2 is not needed, replacing the cross apply with a exists makes both cases faster (clustered 500ms, non 700ms) and DOP 12. The presence of distinct does not matter then either.

In both cases the estimated/actual rows from the final nested loops is identical at 5787/3025.

With maxdop (1) on RID case execution is 2000ms.

I see claims elsewhere that global aggregates and top() may hinder parallelism but for purposes of this question I request that the change in index be the primary focus since parallelism was occurring with those elements engaged.

NonClustered (700ms)

NonClustered

Clustered (1800ms)

Clustered

Response to Comments 2013.10.25:

@Allan S. Hansen:

set statistics io on; set statistics time on;

Non-Clustered:

SQL Server parse and compile time: CPU time = 24 ms, elapsed time = 24 ms.

(3025 row(s) affected) Table '##SmallTable2'. Scan count 289784, logical reads 907981, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'VeryLargeTable'. Scan count 5189, logical reads 21795, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '##SmallTable1'. Scan count 13, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times: CPU time = 6317 ms, elapsed time = 657 ms.

Clustered:

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

(3025 row(s) affected) Table '##SmallTable2'. Scan count 289784, logical reads 618675, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'VeryLargeTable'. Scan count 5189, logical reads 21795, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '##SmallTable1'. Scan count 1, logical reads 26, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times: CPU time = 1887 ms, elapsed time = 1886 ms.

PlanTrees

@JonSeigel: The Parallelism Threshold was 5. The Estimated SubTree CPU cost on the "Select" icon is 3.6 for Clustered, 5.5 for Non-Clustered so it appears you are correct in that respect.

Now I reduced Parallelism Threshold 2.0 and re-ran. Clustered goes parallel with 265ms. And NonClustered no change, relatively slower 720ms so that expected. Great and it makes some sense.

Now see 2 remaining issues (any others?):

1) Row estimates which are way off but I don't believe SQL Server can estimate accurately due to coalescing nature of the query. This may translate to tempdb spills as top is increased or removed. With the top(x) removed, I was able verify a spill for maxdop(1) (runtime 3s). Robust Plan did not improve. With top still removed, spill occurs on the final Sort Distinct with the row estimate of 2.4k and actual of 30k. With maxdop(0) (parallel) the spill does not occur so that's great.

2) Selection of distinct columns: If I change the output to distinct on s1.ObjectId only then the last part of the plan changes from "Sort (Distinct Sort)" with parallelism to "Stream Aggregate" with loss of parallelism. Is there a way to force the former (hints acceptable)? The estimated SubTree cost is 3.5 which is above the new parallel threshold of 2.0. If I remove top 1 this appears to do it, and I assume moving the cross apply to an exists would work as well, are there any other options here?

Thanks for the comments!

share|improve this question
    
Can you set up a repro of this? –  Martin Smith Oct 25 '13 at 7:29
    
Try turning on statistics and take a look at the actual IO and CPU time and see if the result match your execution plan. I would think the issue is that when you query through clustered indexes you actually query through a lot more data causing more IO –  Allan S. Hansen Oct 25 '13 at 8:58
2  
Did the estimated cost of the second plan fall below the cost threshold for parallelism? Eliminating the RID lookup reduced the cost by ~40%. –  Jon Seigel Oct 25 '13 at 13:22
    
How big are your gaps in the histogram (last) section of DBCC SHOW_STATISTICS for the indexes? –  swasheck Oct 25 '13 at 22:36
    
@swashceck I'm not sure how you want to measure gaps but in the "L" table its not equi-spaced at least. Uploaded here filedropper.com/sohistogram . I think the IsNull() controls ultimately how many rows need to be scanned which is not part of hist. Worst case would be all which could be based on hist. But the data could might be stored such that a set of contiguous keys each links with 100 and the adjacent same sized set of keys each links with 1. Would it help to assign IDs to keep 1st derivative of histogram constant? hmm. The ID's are fairly anchored unfortunately. –  crokusek Oct 26 '13 at 1:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.