August 11, 2014 at 3:55 am
For some of the table we are changing Clustered indexes to non clustered index (we can avoid here "why to CLUS to Non CLus"? discussion )
Strange thing is index size got increased (for every table) when we convert "clus" index into NON clu index ?
See Code
SET NOCOUNT ON
--DROP TABLE bk2
go
CREATE TABLE BK2 (stub ut_stub )
GO
INSERT INTO bk2 SELECT NEWID()
go 10000
EXEC sys.sp_spaceused 'BK2',@updateusage = N'TRUE';
GO
CREATE clustered INDEX idx1 ON bk2(stub)
GO
EXEC sys.sp_spaceused 'BK2',@updateusage = N'TRUE';
GO
drop index idx1 on bk2
GO
EXEC sys.sp_spaceused 'BK2',@updateusage = N'TRUE';
GO
CREATE INDEX idx ON bk2(stub)
GO
EXEC sys.sp_spaceused 'BK2',@updateusage = N'TRUE';
GO
namerowsreserveddataindex_sizeunused
heap
BK210000344 KB264 KB24 KB56 KB
CLU
BK210000328 KB248 KB16 KB64 KB
heap
BK210000264 KB248 KB8 KB8 KB
NC
BK210000656 KB248 KB328 KB80 KB
index size got increased from 16 to 328 KB
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 11, 2014 at 4:04 am
Bhuvnesh
A clustered index isn't the same as other indexes - it forms the actual rows of the table and therefore doesn't contribute to the "index" size. When you create a non clustered index, you are creating new data pages to hold the index information.
John
August 11, 2014 at 4:10 am
I was wondering that myself John, whether or not a clustered index is considered in the 'index' part of sp_spaceused.
It's also worth noting a nonclustered index will need to include the Row Identifier (RID) as part of the index key, whereas the clustered index will just be the column it's defined on.
August 11, 2014 at 4:15 am
John Mitchell-245523 (8/11/2014)
BhuvneshA clustered index isn't the same as other indexes - it forms the actual rows of the table and therefore doesn't contribute to the "index" size. When you create a non clustered index, you are creating new data pages to hold the index information.
John
ques1: doesn't it mean that size of table equal to clus index size. ? because Clus index contains actual data
ques 2: and also we know that clus index itself contains actual data then why data is 248 kb and index is 16 KB?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 11, 2014 at 4:22 am
(1) Yes.
(2) If you're asking why index size isn't zero when there is no non-clustered index, I don't know. I imagine there's some small overhead for the possibility that you may have indexes. Try running sp_spaceused with the updateusage option and see whether you get slightly different results.
John
August 11, 2014 at 4:27 am
August 11, 2014 at 4:32 am
Gazareth (8/11/2014)
I was wondering that myself John, whether or not a clustered index is considered in the 'index' part of sp_spaceused.
I believe the non-leaf levels are, but the leaf levels are data because they are the table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2014 at 4:56 am
Thanks Gail, that could be why it's 16KB, can't see a table that small needing 2 IAM pages?
August 11, 2014 at 5:42 am
thanks to all for information
Another Question : can we say "clustered index size" = " original table size" ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 11, 2014 at 5:43 am
Gazareth (8/11/2014)
Thanks Gail, that could be why it's 16KB, can't see a table that small needing 2 IAM pages?
Indeed, that's probably the index root page and the IAM.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply