4

Assume we have two tables with data:

create table heap (value int);
create table clust (value int primary key);
insert into heap values (1);
insert into clust values (1);

By inspecting their storage statistics

select obj.name, st.alloc_unit_type_desc, st.index_level, st.page_count
from (values ('heap'), ('clust')) obj(name)
    join sys.indexes ix on ix.object_id = object_id(obj.name)
    cross apply sys.dm_db_index_physical_stats(
        db_id(), ix.object_id, ix.index_id, NULL, 'DETAILED') st;

select obj.name, au.total_pages, au.used_pages, au.data_pages
from (values ('heap'), ('clust')) obj(name)
    join sys.indexes ix on ix.object_id = object_id(obj.name)
    join sys.partitions p on p.object_id = ix.object_id and p.index_id = ix.index_id
    join sys.allocation_units au on au.container_id = p.partition_id;

one can see that both occupy the same number of pages:

name  alloc_unit_type_desc index_level page_count
----- -------------------- ----------- ----------
heap  IN_ROW_DATA          0           1
clust IN_ROW_DATA          0           1

name  total_pages  used_pages  data_pages
----- ------------ ----------- -----------
heap  2            2           1
clust 2            2           1

In this case why statistics io

set nocount on;
set statistics io on;
declare @cnt int;
select @cnt = count(1) from heap;
select @cnt = count(1) from clust;
set statistics io off;

show that scanning the clustered index compared to scanning the heap takes one extra logical read?

Table 'heap'. Scan count 1, logical reads 1...
Table 'clust'. Scan count 1, logical reads 2...

What this extra logical read is?

P.S.
My question is not about "clustered index vs heap performance" or query tuning. I'm trying to better understand things involved into STATISTICS IO reported for clustered index scan.


The example is for SqlServer 2014

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)

I also tried it on SqlServer 2008 R2

Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64)

and result is the same (though one can not use sys.dm_db_index_physical_stats on 2008 R2 this way).

5
  • From Thomas Kejser: "In the clustered case, an insert will only touch the leaf row of the last page. In the heap case, both the index and the heap has to be updated. This results in the heap needing one more logical read and one more write than the cluster." – Aaron Bertrand Oct 20 '16 at 11:44
  • 4
    @AaronBertrand But i-one is asking about extra logical read on clustered index. Wouldn't that come from tree structure of it (even with only one row inside)? – Marcin S. Oct 20 '16 at 11:49
  • @MarcinS, as far as I understand, in this case the "tree structure" is just a single page - the root and the leaf at the same time (this is what is reported by index physical stats DMV). Thank you for the link, not exactly the same issue, but I think that the cause of extra read can be same or similar (though from comments on Connect item it is not clear for me whether extra read happens really, or is it just statistics io wrongly reports additional read). – i-one Oct 20 '16 at 20:56
  • 1
    @MarcinS. - That connect item was posted by someone trying to answer my question here stackoverflow.com/a/5901585/73226. The explanation in that case was provided by Paul White as the read ahead mechanism. Disabling read ahead doesn't alter anything in the one page case here. – Martin Smith Oct 21 '16 at 18:51
  • @i-one I don't think read ahead is the answer here though. When I tried DBCC TRACEON (652); it still reported 2 reads. I imagine this is a minor bug that it over reports by 1 for some reason. – Martin Smith Oct 22 '16 at 22:32
5

Accessing either a Clustered Index or Non-Clustered Index requires traversing that b-tree structure. For some reason, on scan operations there seems to be one extra logical read. In your case you have a single page, which is the 2 logical reads. If you had enough rows to fill up enough data pages that would in turn require another level within the b-tree index structure, then you would see an additional logical read.

Heaps, by definition, have no index (b-tree) structure. Since you have one data page, the operations only need that one logical read. In such a simplistic example it would appear to be less work than the Clustered Index approach, but as soon as you get a few more data pages then you will start to see a difference since the b-tree structure will allow for going directly to appropriate data pages while the Heap still has to check all of the pages.

For example, I have a test table with a structure of:

CREATE TABLE [dbo].[GuidPkAsUI](
    [ID] [uniqueidentifier] NOT NULL CONSTRAINT [PK_GuidPkAsUI] PRIMARY KEY CLUSTERED,
    [InsertTime] [datetime] NOT NULL
                            CONSTRAINT [DF_GuidPkAsUI_InsertTime]  DEFAULT (getdate()),
);

It has 767,968 rows in it via:

INSERT INTO [dbo].[GuidPkAsUI] ([ID])
  SELECT TOP (767968) NEWID()
  FROM   master.sys.all_columns ac1
  CROSS JOIN master.sys.objects so1;

I copied it to a new table that is the same structure and data, but missing the two constraints (i.e. no Clustered Index) using the following:

SELECT *
INTO dbo.GuidPkAsUIheap
FROM dbo.GuidPkAsUI;

The following queries:

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.GuidPkAsUIheap'),
                                             0, NULL, 'DETAILED');

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.GuidPkAsUI'),
                                             1, NULL, 'DETAILED');

show that GuidPkAsUIheap has 1 level (with 3135 data pages) and GuidPkAsUI has 3 levels (with 3135 data pages, 20 index pages on one level -- intermediate, and 1 index page on another level -- root, totaling 3156 pages).

The following queries:

SET STATISTICS IO ON;
SELECT COUNT(1) FROM dbo.GuidPkAsUIheap;
SET STATISTICS IO OFF;
-- 3135

SET STATISTICS IO ON;
SELECT COUNT(1) FROM dbo.GuidPkAsUI;
SET STATISTICS IO OFF;
-- 3157

shows that GuidPkAsUIheap requires 3135 logical reads (the number of data pages) while GuidPkAsUI requires 3157 logical reads (the number of data and index pages plus one). So here the logical reads for the Clustered Index are still higher than for the Heap.

I then rebuilt the tables via:

ALTER TABLE [dbo].[GuidPkAsUIheap] REBUILD;
ALTER TABLE [dbo].[GuidPkAsUI] REBUILD WITH (FILLFACTOR = 100);

Running the SELECT * FROM sys.dm_db_index_physical_stats queries above again shows the Heap to be the same but the Clustered Index now has only 10 intermediate index pages instead of 20.

Running the SELECT COUNT(1) queries above again shows the same 3135 logical reads for the Heap and 3147 logical reads for the Clustered Index (all data and index pages plus one).

Now, let's find one specific row:

SET STATISTICS IO ON;
SELECT * FROM dbo.GuidPkAsUIheap WHERE [ID] = '93359759-193F-4CBF-B9F6-738475F8488E';
SET STATISTICS IO OFF;
-- 3135


SET STATISTICS IO ON;
SELECT * FROM dbo.GuidPkAsUI WHERE [ID] = '93359759-193F-4CBF-B9F6-738475F8488E';
SET STATISTICS IO OFF;
-- 3

The Heap still takes 3135 logical reads. But the Clustered Index takes a mere 3 logical reads: 1 for the root index page, 1 for the next level index page, and 1 for the leaf level / data page.

Now let's force a scan as we look for a single row:

SET STATISTICS IO ON;
SELECT * FROM dbo.GuidPkAsUIheap WHERE CONVERT(CHAR(36), [ID]) = '98331062-8BF3-4FAE-98B4-204D0DE06FE1';
SET STATISTICS IO OFF;
-- 3135


SET STATISTICS IO ON;
SELECT * FROM dbo.GuidPkAsUI WHERE CONVERT(CHAR(36), [ID]) = '98331062-8BF3-4FAE-98B4-204D0DE06FE1';
SET STATISTICS IO OFF;
-- 3147

Here we get the same logical reads that the COUNT(1) queries get.

6
  • So in your results the logical reads for GuidPkAsUI were higher by 451 than reading every data page and every index page? – Martin Smith Oct 20 '16 at 17:35
  • @MartinSmith Well, I had thought that I had rebuilt the clustered index a while back but it appears that I had not. I updated the number of pages and logical reads. However, the clustered index logical reads is still 303 more than the combined data and index pages. I thought it would just jump from data page to data page after getting to the first one, so (datapages + 2), but it seems to read the intermediate pages a certain number of times. – Solomon Rutzky Oct 20 '16 at 18:27
  • Hmm, I don't see that i.stack.imgur.com/XGMe8.png. What version? – Martin Smith Oct 20 '16 at 19:21
  • 1
    "In your case you have a single index page and a single data page, which is the 2 logical reads." I don't think so. AFAIK, indexes in SqlServer are "B+" trees, and in the simplest case (like the one in the question) clustered index has just a single node (page), which is the root and leaf at the same time. This is what sys.dm_db_index_physical_stats shows in DETAILED mode, my clustered table has no other levels except "root-leaf" (index_level = 0). – i-one Oct 20 '16 at 19:40
  • @MartinSmith Actually, I got the same results as you on 2014, SP1. I have updated my answer with more details. I guess 2012 has "issues" with reporting reads. And I think this has come up before, possibly in a question that Max posted. – Solomon Rutzky Oct 20 '16 at 21:44
2

So this comes down to the fact that by default when you create a primary key it creates a clustered index. The difference is coming from the way SQL server uses indexes.

If you look at the actual execution plans for both select queries shown here. The query is using the index for the clust table.

Execution plans

When you make a request to read the index you first need to read the root node of the clustered index and then are directed to the leaf node which contains the data, this means that you would need to do two reads for the index lookup where on the heap you will only need to do one as there is no redirect.

Additional resources: Clustered Index Structures, Heap Structures, and Nonclustered Index Structures

5
  • Can you try your suggestion with more than just 1 record something pointed in connect item posted by Marcin S. – Shanky Oct 20 '16 at 14:04
  • Shanky, I don't believe that connect Item applies to this scenario. When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index. The primary key column cannot allow NULL values. The connect Item has "When seeking for values in a clustered non-unique index, an extra logical read is performed." – James Rhoat Oct 20 '16 at 14:19
  • 1
    In short, the connect item is looking for "clustered non-unique index" The primary key is a "Clustered unique index" – James Rhoat Oct 20 '16 at 14:21
  • And fortunately, this b-tree traversal actually makes many queries a lot faster, even though in this very simple example there are twice as many reads. In a table with a ton of pages, the clustered index will make a huge positive impact on query performance by allow SQL Server to do a seek or at least a range scan for appropriate queries whereas a heap offers no such benefit. – Hannah Vernon Oct 20 '16 at 17:13
  • You right in general case, but in this particular case there are no nodes beyond the root, which is also the leaf at the same time (this is what index physical stats DMV shows). So, clustered index don't need to be traversed for scanning. – i-one Oct 21 '16 at 6:32

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.