Index Recommendations | SansSQL

Monday, January 25, 2010

Index Recommendations

This Query when executed gives the recomendation to your indexes whether it has to reindexed or defraged.
This query also gives the fragmentation level of your indexes.

Select db_name(DB_ID()) as DBName,SS.name as SchemaName,SO.name as TableName, SI.name as Indexname,index_type_desc as IndexType ,avg_fragmentation_in_percent as FragmentationPercentage,
(case when avg_fragmentation_in_percent between 10 and 30 then 'Defrag'
when avg_fragmentation_in_percent > 30 Then 'Reindex'
Else 'Can be Ignored Currently'
End) as Recomendation
from sys.dm_db_index_physical_stats(DB_ID(DB_NAME()), null, null, null, 'DETAILED') IPS ,
sys.indexes SI ,sys.objects SO , sys.schemas SS
where IPS.index_id=SI.index_id and IPS.object_id=SI.object_id and
SI.object_id=SO.object_id and SO.schema_id=SS.schema_id and
IPS.index_type_desc in ('NONCLUSTERED INDEX', 'CLUSTERED INDEX')
Order by Recomendation desc

2 comments:

manoj said...

Select db_name(DB_ID()) as DBName,SS.name as SchemaName,SI.name as TableName, SI.name as Indexname

Hi,

In the above query to list the table names in a database, we cant use 'SI.name', plz tell me the correct word.

Sandesh Segu said...

Hi Manoj,

That was a typo. It has been corrected now.
SO.name will give the Table Name.
Please check the updated query.

Post a Comment