3

I created a sample table as below

CREATE TABLE [dbo].[StatisticsDemo](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL
   ) ON [PRIMARY]

Then I inserted below data as below:

SELECT NAME,COUNT(*) AS COUNT 
FROM StatisticsDemo 
GROUP BY NAME

NAME    COUNT
-------------
AABBCC  59999
XXYYZZ  1

Then I created below Non Clustered Index :

CREATE NONCLUSTERED INDEX [NCI_STATISTICSDEMO_NAME] ON [dbo].[StatisticsDemo]
(
    [Name] ASC
)

Now I ran the below query:

SELECT NAME FROM [dbo].[StatisticsDemo] 
WHERE NAME = 'AABBCC'

As expected it is returning 59999 rows but it is doing Index Seek on Non Clustered Index. But as per my knowledge, it should do Index Scan as 99.99% of data satisfied the filter criteria mentioned in the select query.

Can some please tell me why it is doing Index Seek instead of Index Scan?

The purpose of the entire activity was to prove(as I am about to give presentation on statistics) that SQL Server looks into statistics to identify the number of records which matches the filter criteria of query before preparing the execution plan and based on the % of records matches out of total records in table, it will either decide to do a SCAN or SEEK. If % of records matches is approximately equal to total number of records in table, it should do SCAN. But that is not happening. Same is the case when I am using AdventureWorks2016 Database and running below query:

select * from [Sales].[SalesOrderHeader] WHERE SalesOrderID >= 43659 AND 
SalesOrderID <= 73659

The above query returning 30001 records out of 31465. But it is still doing Clustered Index Seek.

I am getting terribly confused and it is shaking my concepts. :( Can some please help.

PS: I cleaned Plan cache as well but no luck. SQL Server version is 2016.

  • Is there a reason why you would expect/prefer an index scan from the partial scan the seek operation does? 100% is more than 99.9% so I don't see why you would want that. – ypercubeᵀᴹ May 23 at 9:16
6

The purpose of the entire activity was to prove(as I am about to give presentation on statistics) that SQL Server looks into statistics to identify the number of records which matches the filter criteria of query before preparing the execution plan and based on the % of records matches out of total records in table, it will either decide to do a SCAN or SEEK. If % of records matches is approximately equal to total number of records in table, it should do SCAN.

this is incorrect so explains why you aren't seeing it. The BETWEEN 43659 AND 73659 range seek is doing a partial scan. It is just able to use the B-tree to seek into the point where to begin the scan (so avoid reading anything lower than 43659 ) and potentially exit early if there are rows with values greater than 73659.

For the rows that are in the range it just reads the pages and follows the linked list to the next leaf page in exactly the same way as an index ordered scan does.

There is no reason to want a scan here. At best it saves a handful of logical reads for navigating from the root to the leaf to find the start point but at the expense of reading additional rows outside the range seeked.

  • Thanks I got the point now. But shouldn't there be a tipping point for SQL Server as well on when to display/decide it to be a INDEX SEEK or SCAN? – sachin-SQLServernewbiee May 23 at 8:28
  • @sachin-SQLServernewbiee - No. There is a tipping point between it deciding to do a seek on a non covering index vs a scan on a (different) covering index but there is no such tipping point between scan and seek on the same index. If the predicate is seekable always expect to see a seek – Martin Smith May 23 at 8:31
  • @MartinSmith is there something in the plan that indicates that this seek is actually a seek+partial scan? (I mean that it doesn't read just one row but many) – ypercubeᵀᴹ May 23 at 9:11
  • 1
    The only case where it isn't a seek + partial scan would be an equality seek on a unique index. I don't think this is differentiated in the plan but it shows up in STATISTICS IO output (scan count would be 0 for this case) – Martin Smith May 23 at 9:23
5

Your understanding is incorrect, and I believe you are confusing your case with the tipping point issue, which is an issue worth reading up on and understanding well.

With SQL Server, a seek is an operation that uses the B-tree to start or stop reading rows at specific values. Theoretically, you can have a seek read every row in the exact order a scan would.

For Oracle, and maybe other RDMS's, this operation (reading multiple rows at defined start and stop values) is called a range scan.

Here's a quick demo of a seek that reads every single row

DROP TABLE IF EXISTS dbo.SeekTest
GO

CREATE TABLE dbo.SeekTest(
ID int PRIMARY KEY CLUSTERED,
filler char(8000) --to get 1 row per page
)

INSERT dbo.SeekTest (ID)
VALUES (1),(2),(3),(4),(5)
GO

SET STATISTICS IO ON

--this one scans
SELECT *
FROM dbo.SeekTest

--this one seeks
SELECT *
FROM dbo.SeekTest
WHERE ID > 0

Remember, a seek can read multiple rows. In this demo, it reads exactly as many as the scan.

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.