October 6, 2010 at 9:12 am
Hi
If I run sp_spaceused against a table with a clustered index and 2 non-clustered indexes I was expecting to see that the data column would be similar to the index_size column in terms of the amount of KB used, as the clustered index is the actual table.
However what I see is that the data column is about 6 times the size, could someone please explain why this is to me?
Thanks
October 6, 2010 at 11:08 am
Have you ran DBCC UPDATEUSAGE lately?
October 6, 2010 at 11:41 am
I think that when it comes to clustered index, it treats the leaf level different then none leaf level. The leaf level’s space is considered as the data’s space, but none leaf’s space is considered as index’s space. The code bellow shows that. Notice that in my example sp_spaceused shows 2 extra pages. My guess is that one of them is the IAM page, but have to admit that this is only a guess.
use tempdb
go
create table test (i int not null constraint pk_test primary key clustered, filler char(500) default ('a'))
declare @i int
set @i = 1
while @i < 80000
begin
insert into test (i) values (@i)
set @i = @i + 1
end
DBCC UPDATEUSAGE ('tempdb','test');
--index_size is 176KB
exec sp_spaceused test
--pages count for noneleaf level is 20 which is 160 KB
select * from sys.dm_db_index_physical_stats (db_id(), object_id('test'),1,null,'DETAILED')
go
drop table test
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 7, 2010 at 3:58 am
Hi there and thanks for the reply.
Using your code I get 8KB extra which equates to one extra page when adding up the pages from all levels except the leaf level.
Hmm I am unsure where this extra 8KB comes from in terms of the index size.
I must thankyou for clearing up how the index sizing works though.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply