August 30, 2007 at 1:31 pm
I run a showcontig on a table, and get the following results
ObjectName ObjectId IndexName IndexId Level Pages Rows MinimumRecordSize MaximumRecordSize
T_TASCC_RESPONSE_TRACKING 581577110 0 0 70 7826 48 75
T_TASCC_RESPONSE_TRACKING 581577110 IX_RESPONSE_TRACKING 2 0 17 7826 13 13
T_TASCC_RESPONSE_TRACKING 581577110 tT_TASCC_RESPONSE_TRACKING 255 0 187 11007 79 4014
The table has one index, but shows 3 items as results in the showcontig. the indexID 255 has me puzzled. It has a name listed, like the other indexes do. but there is no corresponding index.
And since i was blindly grabbing items that needed to be reindexed, and stuffing them into a dbcc dbreindex command or dbcc defragindex, i would get errors. After digging a bit, i realized i didnt know what this was. So I ask the community to teach me.
August 30, 2007 at 2:18 pm
FROM BOL
Each table that has at least one text, ntext, or image column also has a row in sysindexes with indid = 255.
The column FirstIAM points to the chain of IAM pages that manage the text, ntext, and image pages.
August 30, 2007 at 2:28 pm
perfect. thanks for the quick response. i learned something today.
August 31, 2007 at 12:31 pm
indeed:
Index Id 0 = a heap table (table with no clustering index)
Index Id 1 = a clustering index (on index 0 possible )
Index Id 255 = lob container
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 31, 2007 at 1:59 pm
You may find this query useful for looking at index information:
use Northwind
select
[Table_Index] = left(rtrim(object_name(a.ID))+'.'+a.name,50),
a.indid,
b.index_property,
Value = isnull(indexproperty(a.id,a.name,b.index_property),-999),
[Heap] = convert(bit,case when a.indid = 0 then 1 else 0 end),
[Text_Image_Idx] = convert(bit,case when a.indid = 255 then 1 else 0 end)
--,a.*
from
sysindexes a
cross join
(
select index_property = 'IsFulltextKey', Seq = 6 union all
select index_property = 'IsAutoStatistics', Seq = 5 union all
select index_property = 'IsClustered' , Seq = 2 union all
select index_property = 'IsHypothetical', Seq = 7 union all
select index_property = 'IsStatistics', Seq = 4 union all
select index_property = 'IsUnique', Seq = 3 union all
select index_property = 'IndexDepth', Seq = 9 union all
select index_property = 'IndexFillFactor', Seq = 10 union all
select index_property = 'IndexID', Seq = 1
) b
where
object_name(a.ID) in ('Categories') and
objectproperty(a.ID,'IsUserTable') = 1
order by
object_name(a.ID),
indexproperty(id,a.name,'IndexID'),
a.name,
b.Seq
Results:
Table_Index indid index_property Value Heap Text_Image_Idx
-------------------------------------------------- ------ ---------------- ----------- ---- --------------
Categories.PK_Categories 1 IndexID 1 0 0
Categories.PK_Categories 1 IsClustered 1 0 0
Categories.PK_Categories 1 IsUnique 1 0 0
Categories.PK_Categories 1 IsStatistics 0 0 0
Categories.PK_Categories 1 IsAutoStatistics 0 0 0
Categories.PK_Categories 1 IsFulltextKey 0 0 0
Categories.PK_Categories 1 IsHypothetical 0 0 0
Categories.PK_Categories 1 IndexDepth 1 0 0
Categories.PK_Categories 1 IndexFillFactor 0 0 0
Categories.CategoryName 2 IndexID 2 0 0
Categories.CategoryName 2 IsClustered 0 0 0
Categories.CategoryName 2 IsUnique 0 0 0
Categories.CategoryName 2 IsStatistics 0 0 0
Categories.CategoryName 2 IsAutoStatistics 0 0 0
Categories.CategoryName 2 IsFulltextKey 0 0 0
Categories.CategoryName 2 IsHypothetical 0 0 0
Categories.CategoryName 2 IndexDepth 1 0 0
Categories.CategoryName 2 IndexFillFactor 0 0 0
Categories.tCategories 255 IndexID 255 0 1
Categories.tCategories 255 IsClustered 0 0 1
Categories.tCategories 255 IsUnique 1 0 1
Categories.tCategories 255 IsStatistics 0 0 1
Categories.tCategories 255 IsAutoStatistics 0 0 1
Categories.tCategories 255 IsFulltextKey 0 0 1
Categories.tCategories 255 IsHypothetical 0 0 1
Categories.tCategories 255 IndexDepth 0 0 1
Categories.tCategories 255 IndexFillFactor 0 0 1
(27 row(s) affected)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply