0

I attempted to improve the following query that is getting executed continuously:

SELECT TOP(?) Schema1.Object1.Column1, Schema1.Object1.Column2, Schema1.Object1.Column3
FROM         Schema1.Object1 INNER JOIN
                      Schema1.Object2 ON Schema1.Object1.Column4 = Schema1.Object2.Column1 INNER JOIN
                      Schema1.Object3 ON Schema1.Object2.Column5 = Schema1.Object3.Column1 LEFT OUTER JOIN
                      Schema1.Object4 ON Schema1.Object1.Column6 = Schema1.Object4.Column7 LEFT OUTER JOIN
                      Schema1.Object5 ON Schema1.Object5.Column1 = Schema1.Object1.Column8
WHERE     (Schema1.Object1.Column3 > ?)
          ORDER BY Schema1.Object1.Column3 ASC

Object1.Column3 is of type ROWVERSION. The execution plan showed a Clustered Index Scan on Object1.Index1 to filter on Object1.Column3. -> The original Plan.

Subtree Total costs of the plan is 59. DOP = 4.

The IO stats of the original version is the following:

(50 row(s) affected) 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 'Object1'. Scan count 1, logical reads 299, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So it was obvious to create a nonclustered index using ROWVERSION. I applied the following index:

CREATE Nonclustered index index1 ON dbo.Object1 
  (Column3) INCLUDE  (Column4,Column3,Column2,Column1,Column8);  

This changed the plan as expected to perform an index seek operation as you can see in the new plan.

Subtree Total costs of the new version is 0.33 now. DOP = 1.

But what confuses me is the io statistics. Did it downgrade / get worse? The joined Object2 suddenly produces more IO than before (where for unknown reasons it was not even present in the statistics)...:

(50 row(s) affected) Table 'Object3'. Scan count 0, logical reads 162, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Object2'. Scan count 50, logical reads 248, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Object1'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

This makes a logical read total value of 413 in comparison to 299 before. What happened here? Did I improve the query or not? I guess YES, but what about the io statistics?

  • 1
    Please provide the following: an actual plan for the old query, information about any foreign key relationships, and validation that both queries return the same results. If Column3 is not unique then the two queries could definitely return different results which could impact performance. – Joe Obbish Jan 2 '17 at 18:16
  • 1
    Yeah, try removing the TOP parameter (or pass in 2 billion), this will give you an immediate clue if the two statements really do perform the same work. – Aaron Bertrand Jan 2 '17 at 20:22
  • I am definitely confused by the first query's I/O stats. I have to assume that 'Worktable' (not referenced in the query at all) is some sort of compilation of Object2, Object3, and Object4 that was built in the background. Is there any indication where the Worktable was created, and how? – Laughing Vergil Jan 3 '17 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.