SQL Server 2005 Indexes

[sendba]

All

Can we use DBCC SHOWCONTIG on the SQL SERVER 2005 instance.
In Sql server 2000 we used to look at the logical scan and extent scan fragmentation and determine whether it needs to be rebuild or not.

Can anyone please let me know whether the same rule apply for SQL Server 2005.if not can you let me know which parameter is same as logical scan and extent scan in 2005.

Thanks
Sen

 

[ptheriault]

Showcontig is still supported in 2005. But this view is better.

SELECT o.name as 'Table'
      ,Index_id
      ,index_type_desc
      ,avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(db_id('[b]yourdb[/b]'), null, null, null, null) i
     JOIN sys.objects o
     ON o.object_id = i.object_id 
ORDER BY o.name
        ,index_id

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[sendba]

But if a table is fragmented how can i get the information from this view

Is there any rule for it

thanks ptheriault.

 

[ptheriault]

avg_fragmentation_in_percent. I try to keep that number as low as possible, For me, if it's above 20% I'll run a ALTER INDEX in the evening.

- Paul
- If at first you don't succeed, find out if the loser gets anything.