CODEWITHSUNDEEP
sql-serverperformanceindexing
user3657661
user3657661

Reputation: 416

SQL Server Index Scan and Index Seek having the same performance

I've got four queries typed up right now trying to select a count from a very large table.

The queries are essentially this.

SELECT count(PrimaryKeyIntColumn) from dbo.TableName

SELECT count(PrimaryKeyIntColumn) from dbo.TableName where PrimaryKeyIntColumn >= 0

SELECT count(PrimaryKeyIntColumn) from dbo.TableName where PrimaryKeyIntColumn IS NOT NULL

SELECT count(PrimaryKeyIntColumn) from dbo.TableName where PrimaryKeyIntColumn BETWEEN 1 and 2147483647

When I go to display the estimated execution plan, I see the first one is an NonClustered Index Scan, the second one is a Clustered Index Seek, the third is an NonClustered Index Scan, and the fourth is an Clustered Index Seek. This is more or less expected (is not null would normally be SARGable except a primary key is already not nullable so the query optimizer probably just throws it out)

The issue is that each one of these queries is taking up 25% query cost relative to the batch of 4, with each one having it's Index Scan or Index Seek taking up 95% of the cost. Basically, as far as I can tell, there is no real performance difference between an Index Scan and an Index Seek in this particular scenario, even though there should be.

The exact execution plan is SELECT 0% -> Compute Scalar 0% -> Stream Aggregate 5% -> Index (Scan|Seek) 95%

I'm not really sure what the problem is, but the seek does not appear to be even a little faster than the scan. Running each of these spins for minutes before I get impatient and cancel the query.

While I know I can get the counts some other way, that's not really the end goal. I'm trying to get the performance down for some other queries and I'm not sure why turning a scan into a seek doesn't do anything. I figure if I can find out why this is happening, I might be able to get to the actual root of the problem.

Any help would be appreciated. This is a very large table with over 100 million rows of data.

There is a similar question here: Poor clustered index seek performance? but it doesn't appear to be applicable to me.

Upvotes: 1

Views: 473

Answers (1)

Antonín Lejsek
Antonín Lejsek

Reputation: 6103

It is not really a seek. You just seek the beginning - and do a range scan from there. As Your range is (almost) identical to the whole table, there is no real difference. The count has to go through 100M of records, either in clustered or in nonclustered index. You can not expect it would be fast. And no, there is no rowcount stored for the table You can easily read.

Upvotes: 2

Related Questions