Sql-server – STATISTICS IO difference for heap and clustered index

sql serversql server 2014sql-server-2008-r2

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).

Best Answer

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.

Related Solutions

Sql-server – Performance difference between Clustered and Non Clustered Index

Very good question as it is such a important concept. This is a big topic though and what I am going to show you is a simplification so you can understand the base concepts.

Firstly when you see clustered index think table. In SQL server if a table does not contain a clustered index it is a heap. Creating a clustered index on the table actually transforms the table into a b-tree type structure. Your clustered index IS your table it is not separate from the table

Ever wondered why you can only have one clustered index? Well if we had two clustered indexes we would need two copies of the table. It contains the data after all.

I am going to try and explain this by using a simple example.

NOTE: I created the table in this example and filled it with over 3 million random entries. Then ran the actual queries and pasted the execution plans here.

What you really need to grasp is O notation or operational efficiency. Let's assume you have the following table.

CREATE TABLE [dbo].[Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [varchar](100) NOT NULL,
[CustomerSurname] [varchar](100) NOT NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
[CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF
  , IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS  = ON
  , ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

So here we have basic table with a clustered key on CustomerID (Primary key is clustered by default). Thus the table is arranged/ordered based on the primary key CustomerID. The intermediate levels will contain the CustomerID values. The data pages will contain the whole row thus it is the table row.

We will also create a non-clustered index on the CustomerName field. The following code will do it.

CREATE NONCLUSTERED INDEX [ix_Customer_CustomerName] ON [dbo].[Customer] 
 (
[CustomerName] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF
  , SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF
  , DROP_EXISTING = OFF, ONLINE = OFF
  , ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

So in this index you would find on the data pages/leaf level nodes a pointer to the intermediate levels in the clustered index. The index is arranged/ordered around the CustomerName field. Thus the intermediate level contains the CustomerName values and the leaf level will contain the pointer (these pointer values are actually the primary key values or CustomerID column).

Right so if we execute the following query:

SELECT * FROM Customer WHERE CustomerID = 1

SQL will probably read the clustered index via a seek operation. A seek operation is a binary search which is much more efficient than a scan which is a sequential search. So in our above example the index is read and by using a binary search SQL can eliminate the data that don't match the criteria we are looking for. See attached screen shot for the query plan.

So the number of operations or O Notation for the seek operation is as follows:

Do binary search on clustered index by comparing the value searched for to the values in the intermediate level.
Return the values that match( remember since the clustered index has all the data in it can return all the columns from the index as it is the row data)

So it is two operations. However if we executed the following query:

SELECT * FROM Customer WHERE CustomerName ='John'

SQL will now use the non-clustered index on the CustomerName to do the search. However since this is a non-clustered index it does not contain the all of the data in the row.

So SQL will do the search on the intermediate levels to find the records that match then do a lookup using the values returned to do another search on the clustered index(aka the table) to retrieve the actual data. This sounds confusing I know but read on and all will become clear.

Since our non-clustered index only contains the CustomerName field(the indexed field values stored in the intermediate nodes) and the pointer to the data which is the CustomerID, the index has no record of the CustomerSurname. The CustomerSurname has to be fetched from the clustered index or table.

When running this query I get the following execution plan:

There are two important things for you to notice in the screen shot above

SQL is saying I have a missing index(the text in green). SQL is suggesting I create a index on CustomerName which includes CustomerID and CustomerSurname.
You will also see that 99% of the time of the query is spent on doing a key lookup on the primary key index/clustered index.

Why is SQL suggesting the index on CustomerName again? Well since the index contains only the CustomerID and the CustomerName SQL still has to find the CustomerSurname from the table/clustered indexes.

If we created the index and we included the CustomerSurname column in the index SQL would be able to satisfy the entire query by just reading the non-clustered index. This is why SQL is suggesting I change my non-clustered index.

Here you can see the extra operation SQL needs to do to get the CustomerSurname column from the clustered key

Thus the number of operations are as follows:

Do binary search on non-clustered index by comparing the value searched for to the values in the intermediate level
For nodes that match read the leaf level node which will contain the pointer for the data in the clustered index (the leaf level nodes will contain the primary key values by the way).
For each value returned do a read on the clustered index(the table) to get the row values out here we would read the CustomerSurname.
Return matching rows

That is 4 operations to get the values out. Twice the amount of operations needed compared to reading the clustered index. The show you that your clustered index is your most powerful index as it contains all the data.

So just to clarify one last point. Why do I say that the pointer in the non-clustered index is the primary key value? Well to demonstrate that the leaf level nodes of the non-clustered index contains the primary key value I change my query to:

SELECT CustomerID
FROM Customer
WHERE CustomerName='Jane'

In this query SQL can read the CustomerID from the non-clustered index. It does not need to do a lookup on the clustered index. This you can see by the execution plan which looks like this.

enter image description here

Notice the difference between this query and the previous query. There is no lookup. SQL can find all the data in the non-clustered index

Hopefully you can begin to understand that clustered index is the table and non-clustered indexes DON'T contain all the data. Indexing will speed up selects due to the fact that binary searches can be done but only clustered indexes contain all the data. So a search on a non-clustered index will almost always result in values being loaded from the clustered index. These extra operations make non-clustered indexes less efficient than a clustered index.

Hope this clears things up. If anything does not make sense please post a comment and I will try clarify. It is rather late here and my brain is feeling a wee bit flat. Time for a red bull.

Sql-server – Does Detach/Attach or Offline/Online Clear the Buffer Cache for a Particular Database

I initially thought you were on to something here. Working assumption was along the lines that perhaps the buffer pool wasn't immediately flushed as it requires "some work" to do so and why bother until the memory was required. But...

Your test is flawed.

What you're seeing in the buffer pool is the pages read as a result of re-attaching the database, not the remains of the previous instance of the database.

And we can see that the buffer pool was not totally blown away by the detach/attach. Seems like my buddy was wrong. Does anyone disagree or have a better argument?

Yes. You're interpreting physical reads 0 as meaning there were not any physical reads

Table 'DatabaseLog'. Scan count 1, logical reads 782, physical reads 0, read-ahead reads 768, lob logical reads 94, lob physical reads 4, lob read-ahead reads 24.

As described on Craig Freedman's blog the sequential read ahead mechanism tries to ensure that pages are in memory before they're requested by the query processor, which is why you see zero or a lower than expected physical read count reported.

When SQL Server performs a sequential scan of a large table, the storage engine initiates the read ahead mechanism to ensure that pages are in memory and ready to scan before they are needed by the query processor. The read ahead mechanism tries to stay 500 pages ahead of the scan.

None of the pages required to satisfy your query were in memory until read-ahead put them there.

As to why online/offline results in a different buffer pool profile warrants a little more idle investigation. @MarkSRasmussen might be able to help us out with that next time he visits.

Best Answer

Related Solutions

Sql-server – Performance difference between Clustered and Non Clustered Index

Sql-server – Does Detach/Attach or Offline/Online Clear the Buffer Cache for a Particular Database

Related Question