This question was
closed
by
nidheesh.r.pillai for the following reason: The question is answered, right answer was accepted
asked
Why is the execution plan like this?
Test Scenario: Physical Table = [AutoEmployee], Column = AutoEmpName varchar(20) Data rows inserted = 4323461 Index 1 = 1 clustered unique index on AutoEmpName in ascending format Index 2 = 1 nonclustered unique index on AutoEmpName in ascending format Query SELECT AutoEmpName from [TestDatabase].[dbo].[AutoEmployee] where AutoEmpName 'AutoUser65642' Results (4323460 row(s) affected) Execution Plan ![alt text][1] Two main questions I had- 1. Why was the index seek performed with the non-clustered index, and not with the clustered index? 2. What is the significance of the other components that are additionally present? [1]: /storage/temp/2465-plan.png
The cost of using the nonclustered is less than that of the clustered - maybe the clustered index is on more pages, hence greater IO required. (When I mock this up on my machine with 1,000,000 rows, the clustered index is 3323 pages, whgereas the NCIX is 2958) The other components are due to the way that the optimizer is handling the query. It has decided to split the query into 2 parts : SELECT AutoEmpName from [TestDatabase].[dbo].[AutoEmployee] where AutoEmpName ]]] 'AutoUser65642' and then merge the 2 result sets
The problem with only looking at a picture of an execution plan vs. the *.sqlplan file is that you can't see all the details in the operators which tell you exactly why the optimizer made the choices that it did. I suspect two things from what I see. First, your table structure is more complex than the sample you provided. Otherwise, the Compute Scalar operators don't make much sense. Second, I think your statistics might be out of date or skewed. I'm not sure I can explain a nested loop operation and seeks for such a large amount of data otherwise. @Kev are you getting a similar plan on your test structure?
15 People are following this question.
