January 25, 2010 at 12:52 pm
Hi,
I ran the below query and I have question as below:
select database_id, object_id, index_id, partion_number, index_type_desc, alloc_unit,type_desc from sys.dm_db_index_physical_stats ( NULL,NULL,NULL,NULL,NULL)
Results:
database_id object_id index_id partition_numbe index_type_des alloc_unit,type_desc
1 1099150961 0 1 HEAP IN_ROW_DATA
1 1115151018 1 1 CLUSTRED INDEX IN_ROW_DATA
1 1115151018 2 1 NONCLUSTERED INDEX IN_ROW_DATA
From BOL,
sys.dm_db_index_physical_stats
Returns size and fragmentation information for the data and indexes of the specified table or view. For an index, one row is returned for each level of the B-tree in each partition. For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition
Question:
So here, the bold text lines, even if the index type is clustered & non clustered, the alloc unit showing as IN_ROW_DATA
Is that correct? because it show IN_ROW_DATA for only heap right? what it has to show for cluster & non cluster indexes?
thanks
January 25, 2010 at 1:06 pm
gmamata7 (1/25/2010)
So here, the bold text lines, even if the index type is clustered & non clustered, the alloc unit showing as IN_ROW_DATA
Correct
because it show IN_ROW_DATA for only heap right? what it has to show for cluster & non cluster indexes?
No, not at all.
The 3 types for alloc units are In Row Data, Lob and Row Overflow. Lob are large objects (text, ntext, image, the MAX data types and XML). Row overflow is for varchar/nvarchar that are too big to fit onto a single page and hence have overflowed. Everything else is in row data. It's the norm and the one that all indexes will have.
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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply