Which is that extra page? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Which is that extra page?

Author

Topic

Sachin.Nand

2937 Posts

Posted - 2010-11-30 : 06:37:33

I have created a test table with a single clustered index on it.


CREATE DATABASE TestDB 
GO

USE TestDB
GO

create table test(id int identity(1,1) primary key clustered,
name varchar(100),dt datetime)
insert into test(name,dt)
select s.name,DATEADD(dd,number,getdate()) from master..spt_values  v
cross join sysobjects s
where v.type='p'

Now when I use dm_db_index_physical_stats and the dm_db_index_operational_stats to check on the indexes on the table they show
the page count of 3874 with dm_db_index_physical_stats and dm_db_index_operational_stats also a non leaf allocation count of 15.


select page_count from sys.dm_db_index_physical_stats(db_id('TestDB'),
OBJECT_ID('test'),null,null,null)

select leaf_allocation_count,nonleaf_allocation_count
from sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('dbo.test'),NULL, NULL) ios
	INNER JOIN sys.indexes i ON i.object_id = ios.object_id AND i.index_id = ios.index_id;

So now when I use a simple select



set statistics io on;
GO
select * from test

The statistics show the number of logical reads of 3890 which is summation of page count and non leaf allocation count.
The summation comes to 3874+15=3889.But the logical reads are 3890.

So my question what is that extra page that sql server uses?
In case of highly fragmented table to the behavior is same.The query below will fragment the table to about 80%



CREATE NONCLUSTERED INDEX [testindx1]
ON [dbo].[test] ([dt])
INCLUDE (id,name)
GO

 update test set name=REPLICATE('X',10)

PBUH

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-30 : 06:46:16

Maybe geting the pointer to the start of the index?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-30 : 06:57:02

Most likely it's the IAM.

--
Gail Shaw
SQL Server MVP

Sachin.Nand

2937 Posts

Posted - 2010-11-30 : 07:42:25

quote:
Originally posted by GilaMonster

Most likely it's the IAM.

--
Gail Shaw
SQL Server MVP

I to thought the same also when I do



select * from sys.system_internals_allocation_units

on the object id of index I find there are 3 columns total_pages,used_pages and data_pages with the value of data_pages being same as the logical scan count.
So what do those 2 other columns indicate?

PBUH

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-30 : 07:48:27

Considering that's an undocumented DMV, I doubt anyone can tell you.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources