How many logical pages?

Question

Post reply

How many logical pages?

Jackie Lowery

SSCommitted

Points: 1889
More actions
June 29, 2019 at 2:56 pm

#3658643
I'm using the StackOverflow 2010 database Posts table for this query. Why would it require 6 logical reads?
I understand why it takes 2 scans, but can someone explain the technical reason it requires 6 logical reads instead of just 2?
The ix_postid index is a unique index on the Id field. The Id field is an Identity type field so it's already sorted ascending.
```
select min(Id) min_id
, max(id) max_id
from Posts with(index=ix_postid)
```

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jonathan AC Roberts SSCoach Points: 18340 More actions · Answer 2

Maybe to navigate through the B-tree?

For example, if the b-tree looked like this you would need to read 6 pages with 2 scans to get the min and max values (A, Z):

Capture

Grant Fritchey SSC Guru Points: 398878 More actions · Answer 3

Take a look at what the execution plan is doing. That query hint may be resulting in a scan instead of a seek. That would explain an increase in the number of reads.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jackie Lowery SSCommitted Points: 1889 More actions · Answer 4

Here is the exec plan. I just figured that since a page is 8K, a single integer would easily fit on one page and since the plan is a seek from each end of the index it would only end up pulling 2 pages.

This reply was modified 5 years, 4 months ago by Jackie Lowery.

Attachments:

You must be logged in to view attached files.

Jonathan AC Roberts SSCoach Points: 18340 More actions · Answer 5

Jackie Lowery wrote:

Here is the exec plan. I just figured that since a page is 8K, a single integer would easily fit on one page and since the plan is a seek from each end of the index it would only end up pulling 2 pages.

But the index is organised as a B-tree, so it needs to search the nodes to get to the min and max values. So depending on the depth of the tree it might need to read more pages to get to the min/max values.

Steve Jones - SSC Editor SSC Guru Points: 736129 More actions · Answer 6

It's a good question. My guess with the SO database, that like Jonathan referenced, there are intermediate pages that need to be read to find the leaf pages.

Grant Fritchey SSC Guru Points: 398878 More actions · Answer 7

The file is coming down as a binary, so I can't look at it. Sorry. However, it's likely that it's what everyone else says. Also, you may see more reads if it's stored on mixed extents, if there's been page splits, etc.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jackie Lowery SSCommitted Points: 1889 More actions · Answer 8

Jonathan AC Roberts wrote:

But the index is organised as a B-tree, so it needs to search the nodes to get to the min and max values. So depending on the depth of the tree it might need to read more pages to get to the min/max values.

I think you've hit the nail on the head here. I was looking through the index properties fragmentation info and noticed the depth of the index is 3, so the 3 reads per scan is to get to the first leaf level to get the data.