1

The two Index Seek (highlighted in green and red) are for filtering, they did not group in Query Columns tab. But the two Object3 highlighted in red are grouped together instead. Here is the XML Plan.

enter image description here

Does anyone has idea about why PE group the two Object3 highlighted in red and leave the Object3 highlighted in green in another group. I'm sorry asking such a stupid question, because I am a newbie in SQL tuning.

Here is the "Query Columns" tab I am talking about. Plan Explorer grouped the Index Seek and Index Scan. Actually, Plan Explorer will group Index Seek/Scan and Key/RID Lookup together. In this case, I understand the purpose. It help us considering adding the lookup column as an INCLUDE column to an index.

But I don't understand the purpose behind why PE grouped the highlighted Index Seek and Index Scan together!

enter image description here

0

At first, it looks like you've modified XML, because the query does not exactly match the plan.

At second, format your query like this

SELECT o2.Column1, 
       o2.Column2, 
       o1.Column3, 
       o1.Column4, 
       o1.Column5 
  FROM Object1 as o1, 
       Object2 as o2, 
       Object3 as o3 
 WHERE o1.Column3 = o3.Column6 
   AND o2.Column1 = o3.Column7 
   AND o3.Column8 between ? and ?
   and o3.Column7 < ?

At third, instead of having separate indexes on Column7 and Column8 (green and red seeks) you better create one index on Column7 + Column8 (green first) and have included Column6. That will help you to avoid Red Scan.

At fourth, (optional) you can create index on o1.Column3 with included columns o1.Column3, o1.Column4, o1.Column5

  • Thanks for your suggestion. I did not modified the XML. It's a PE Anonymize function ;-) Please open the XML plan using latest PE version 2.8(build 10.1.94.0) and you will see the correct WHERE clause. In fact, I would like to know the reason why PE group the two Object3 (red) together in the query columns tab. What's the purpose it served to help me understand the problem in my SQL. I also asked the same question in SQLPerformance.com, but it is strange that I got no reply from them so far.... – kamingbb Jun 23 '16 at 6:11
  • Do you know definition of the "grouping" in SQL Server? There is no grouping in your plan at all. NONE. – Slava Murygin Jun 23 '16 at 11:27

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.