Category: Performance Tuning

 Index Fragmentation Info  –All Indexes select  object_schema_name(ps.object_id) as ObjectSchema,  object_name (ps.object_id) as ObjectName,  ps.object_id ObjectId,  i.name as IndexName,  ps.avg_fragmentation_in_percent,  ps.page_count from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) ps inner join sys.indexes i  on i.object_id = ps.object_id and   i.index_id = ps.index_id where  avg_fragmentation_in_percent > 10   and ps.index_id > 0 and page_count >100 order by page_count desc –Specific Index select  object_schema_name(ps.object_id) as ObjectSchema,  object_name (ps.object_id) as ObjectName,  ps.object_id ObjectId,  i.name as IndexName,  ps.avg_fragmentation_in_percent,  ps.page_count from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) ps inner join sys.indexes i  on i.object_id = ps.object_id and   i.index_id = ps.index_id where    ps.index_id > 0 and i.name like ‘Index_Name‘ order by 5 desc —————————————————————- ————————————–> use Database_Name ————————————– Declare @TBname nvarchar(255),         @schema nvarchar(255), @DB_NAME nvarchar(255),         @SQL nvarchar(max) DECLARE @db_id SMALLINT; DECLARE @object_id INT; ————————————–> SET @DB_NAME =…

Read More