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)
13 Comments. Leave new
Without having WorldWide Importers installed, I assume this is a partitioned view.
They are for sure not partitioned view.
I mentioned that it is a partitioned view but forgot to mention that only one table shows up in the statistics because the optimizer eliminates the unused tables. The execution plan still shows them, but if you look at the details you will see they are being unused because the query only asks for the customerid.
SECURITY POLICY [Application].[FilterCustomersBySalesTerritoryRole]
Would you please expand a bit over it.
Pinal,
This exact problem was covered in another blog post, probably your’s or maybe a Brent Ozar one. Anyway, the moral of the story is that Security Policies are like hydras in that they can solve a security issue, but create two more issues they can’t be solved as easily as setting a blanket Security Policy. My advice is that less in more at the database level and securing data in an abstraction layer (think application later) will improve transparency and maintainability (and specifically not kill query performance).
Dustin (ThePaidGeek)
Hi Pinal
The execution plan starts of at the cities table because of the primary key cityID that’s linked to the foreign key deliveryCityID on the customers table. It then joins to the stateprovinces table to get the stateprovinceid.It then finds the top 1 customer ID by bringing back the deliverycityid.
The other question is that why top 1 is customer number 841 and thats because it brings back DeliveryCityid 15 which is the first deliverycityid in the customers table if you order by deliverycityid.
Hence the execution plan is a join of multiple table to get back the result. With this design its easy to find out where this customer lives.
This customer with ID 841 lives in Abbottsburg North Carolina.
Well sorry for double guessing but my next guest is a computer column referencing a scalar user defined function (clues I am basing my guess off of is the clustered primary keys of other tables and no parallelism in the plan).
Never mind what I suggested. I am commenting without being in front of a computer to test. I believe the the execution plan would not even show a computed column with a scalar function without that field being selected. After looking at Krzysztof’s suggestion I believe he is correct. Rowlevel security has been enabled through a security policy.
Pinal, could you please give us a correct answer for this interesting problem?
Because Row Level Security :)
A security policy named [Application].[FilterCustomersBySalesTerritoryRole] is defined with FILETER and BLOCK predicates using the function [Application].[DetermineCustomerAccess] and mapped to the table [Sales].[Customers].
The function [Application].[DetermineCustomerAccess] accesses both [Application].Cities and [Application].StateProvinces for evaluation.
Can we please give us correct answer..
It’s selecting from a view