April 23, 2010 at 7:15 am
Hi,
When investigating my indices I cam across a weird thing... one of my table alledgedly has two clustered indices!
SELECT a.index_id,
CAST(name AS VARCHAR(MAX)) AS 'Index name',
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('campagnes'),
null, null, null) AS a
join sys.indexes AS b ON a.OBJECT_ID = b.OBJECT_ID and a.index_id = b.index_id;
index_id index name avg_fragmentation_in_percent
1PK_campagnes50
1PK_campagnes0
2IX_campagnes0
For all other tables this query shows only one clustered index. Could there be something wrong in a system table?
Thanks,
Raymond
April 23, 2010 at 7:19 am
You only have one clustered index on the table. two of the indexes just happened to be named with primary key nomenclature, but a clustered index doesn't even have to be a primary key. I'll get you a query momentarily to give you more information.
April 23, 2010 at 7:23 am
Actually I mispoke. I think it's a problem with your query. Try this and post results:
------------------------------ Get Stats on Indexes for a given table --------------------------------------------------------
Declare @object_ID int ,@dbid int
Select @object_ID = object_ID('campagnes')--<--------------------Change this value
Select @dbid = db_id()
select '[' + Schema_Name(st.[Schema_ID]) + '].[' + Object_Name(dmv.Object_ID) + ']' as TableName
,si.Name as Index_Name
,Avg_Fragmentation_In_Percent
,Fragment_Count
,Avg_Page_Space_Used_In_Percent
,Record_Count
,si.Allow_Page_Locks
--Select *
From sys.dm_db_index_physical_stats (
@dbid
,@object_id
,Null
,Null
,'Sampled'
) dmv
join sys.indexes si
on si.object_id = dmv.object_id
and si.Index_ID = dmv.Index_ID
join sys.tables st
on si.Object_ID = st.Object_ID
April 23, 2010 at 7:28 am
Hi,
Thanks for your swift reply..... here's the output from your code:
TableNameIndex_NameAvg_Fragmentation_In_PercentFragment_CountAvg_Page_Space_Used_In_PercentRecord_CountAllow_Page_Locks
[dbo].[campagnes]PK_campagnes77,7777777777778878,2472695824067171
[dbo].[campagnes]PK_campagnes0NULL18,0627625401532171
[dbo].[campagnes]IX_campagnes012,28564368668149171
April 23, 2010 at 7:36 am
Notice how both rows have the same index_id. There's only one index there. A row is returned for each level that the index has in the B-Tree. Here's an MSDN article.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 23, 2010 at 7:54 am
Include the alloc_unit_type_desc column from sys.dm_db_index_physical_stats in the output.
There will be one row for the normal IN_ROW data, and an extra row for either LOB_DATA (or possibly a ROW_OVERFLOW_DATA).
April 23, 2010 at 7:57 am
Grant Fritchey (4/23/2010)
Notice how both rows have the same index_id. There's only one index there. A row is returned for each level that the index has in the B-Tree. Here's an MSDN article.
True, but only index_level = 0 is reported in any mode other than DETAILED.
The original query specified NULL - so the default LIMITED mode was used.
The second query specified SAMPLED.
I bet the table has LOB allocation units.
Paul
April 23, 2010 at 8:07 am
Paul White NZ (4/23/2010)
Grant Fritchey (4/23/2010)
Notice how both rows have the same index_id. There's only one index there. A row is returned for each level that the index has in the B-Tree. Here's an MSDN article.True, but only index_level = 0 is reported in any mode other than DETAILED.
The original query specified NULL - so the default LIMITED mode was used.
The second query specified SAMPLED.
I bet the table has LOB allocation units.
Paul
Right. I always forget that one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply