It has been awhile since I asked any puzzle on SQLAuthority.com, Let us see a very simple puzzle today which involves execution plan as well.
To simulate the puzzle, you will need to install WideWorldImporters sample database in your system. You can find the complete instructions to install sample database over SQL SERVER 2016 – How to Import New Sample Database WideWorldImporters.
Once you install WideWorldImporters sample database, run following T-SQL query which just returns only a single row of the data.
SELECT TOP 1 [CustomerID] FROM [WideWorldImporters].[Sales].[Customers]
Here is the screenshot of the image:
If you enable SET STATISTICS IO ON, you will see the following message, which demonstrates that SQL Server only reads a few pages.
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 ‘Customers’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
However, to the surprise, when you see the execution plan, you will be surprised that it is using many different types of indexes and also the execution plan is very complex.
Click on the image above to see the larger image.
Here is the puzzling question to you: Why Such a Complicated Execution Plan for a Single Row, Single Table Query?
If you get the answer of this question correct, I will mention your name in the next class of Practical Real World Performance Tuning.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
