Sql-server – Fetching index avg fragmentation for each database

index-tuningsql server

i am beginner and writing a query to fetch avg fragmentation in percent to rebuild indexex for db i used cursor but i m able to bring for one database so how to include for all database using outer cursor excluding master database … please help me to put outer cursor to bring for all database exlcuding master

my query is like this

 DECLARE @TableName VARCHAR(255)
DECLARE @schema VARCHAR(50)
DECLARE @indexName VARCHAR(255)
Declare @avgfrag int
DECLARE @sql NVARCHAR(500)

DECLARE TableCursor CURSOR FOR

SELECT schema_name(t.schema_id)                           AS [Schema],
       object_name(ps.object_id)                          AS [Table],
       i.name                                             AS [Index],
       --ps.Index_type_desc                                 AS IndexType,
       convert(TINYINT,ps.avg_fragmentation_in_percent)   AS [AvgFrag%]
       --convert(TINYINT,ps.avg_page_space_used_in_percent) AS [AvgSpaceUsed%],
       --ps.record_count                                    AS RecordCnt,
       --ps.fragment_count                                  AS FragmentCnt
FROM     sys.dm_db_index_physical_stats(db_id(db_name()),NULL,NULL,NULL,'DETAILED') ps -- Faster option: SAMPLED
         INNER JOIN sys.indexes i
           ON ps.object_id = i.object_id
              AND ps.index_id = i.index_id
         INNER JOIN sys.tables t 
           ON ps.object_id = t.object_id

WHERE  ps.avg_fragmentation_in_percent >=1

ORDER BY ps.avg_fragmentation_in_percent desc




OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @schema,@TableName,@indexname,@avgfrag
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX  '+ @indexname + ' ON ' + @Schema  +'.'+ @tablename + ' REBUILD ' --+' with (online=on)'
print @SQL +'   '+ convert(varchar,@avgfrag)

EXEC (@sql)

FETCH NEXT FROM TableCursor INTO @schema,@TableName,@indexname,@avgfrag
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

Best Answer

This script will loop through all databases and execute the selective reindex script.

SET QUOTED_IDENTIFIER ON
GO

SET NOCOUNT ON;

declare @dbname nvarchar(100)
declare @reindexcommand varchar(max)

DECLARE dbreindex CURSOR
FOR SELECT quotename([name]) from sys.databases
WHERE database_id <>2
AND is_read_only = 0


open dbreindex
while(1=1)
    begin
        FETCH NEXT FROM dbreindex        
        INTO @dbname

        IF @@FETCH_STATUS < 0 
        BREAK  
        SELECT 'Rebuilding/Reorganizing indexes on '+@dbname+' Starting a t'+convert(varchar(50),getdate())+''
        SELECT ''

        set @reindexcommand = '
USE '+ @dbname + ';
SET NOCOUNT ON   
DECLARE @reorg_frag_thresh   float   SET @reorg_frag_thresh   = 10.0
DECLARE @rebuild_frag_thresh float   SET @rebuild_frag_thresh = 30.0
DECLARE @fill_factor         tinyint SET @fill_factor         = 80
DECLARE @report_only         bit     SET @report_only         = 0 
DECLARE @objectid       int
DECLARE @indexid        int
DECLARE @dbid           int
DECLARE @partitioncount bigint
DECLARE @databasename   nvarchar(130)
DECLARE @schemaname     nvarchar(130) 
DECLARE @objectname     nvarchar(130) 
DECLARE @indexname      nvarchar(130) 
DECLARE @partitionnum   bigint
DECLARE @partitions     bigint
DECLARE @frag           float
DECLARE @command        nvarchar(4000)
DECLARE @intentions     nvarchar(4000)
DECLARE @table_var      
TABLE(objectid     int,
indexid      int,
partitionnum int,
frag         float) 
SET @databasename = '''+@dbname+'''
SET @dbid = DB_ID()
INSERT INTO    @table_var
SELECT    [object_id] AS objectid,
[index_id] AS indexid,
[partition_number] AS partitionnum, 
[avg_fragmentation_in_percent] AS frag
FROM    sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, ''LIMITED'')
WHERE    [avg_fragmentation_in_percent] > @reorg_frag_thresh 
AND    index_id > 0 


DECLARE partitions CURSOR 
FOR    SELECT * FROM @table_var 
OPEN partitions 
WHILE (1=1) 
BEGIN    
        FETCH NEXT FROM partitions        
        INTO @objectid, @indexid, @partitionnum, @frag     
        IF @@FETCH_STATUS < 0 
        BREAK     
        SELECT      @objectname = QUOTENAME(o.[name]),        
                    @schemaname = QUOTENAME(s.[name])   
        FROM        sys.objects AS o WITH (NOLOCK)            
        JOIN sys.schemas as s WITH (NOLOCK)                
        ON s.[schema_id] = o.[schema_id]    
        WHERE       o.[object_id] = @objectid     
        SELECT      @indexname = QUOTENAME([name])    
        FROM        sys.indexes WITH (NOLOCK)    
        WHERE       [object_id] = @objectid 
        AND         [index_id] = @indexid     
        SELECT      @partitioncount = count (*)    
        FROM        sys.partitions WITH (NOLOCK)    
        WHERE       [object_id] = @objectid 
        AND         [index_id] = @indexid     
        SET @intentions = @databasename + N''.'' + @schemaname + N''.'' + @objectname + N''.'' + @indexname + N'':'' 
        SET @intentions = ''====='' + @intentions       
        SET @intentions = @intentions + N'' FRAGMENTATION: '' + CAST(@frag AS nvarchar) + N''%''      
        IF @frag < @rebuild_frag_thresh 
            BEGIN        
                SET @intentions = @intentions + N'' OPERATION: REORGANIZE''         
                SET @command = N''ALTER INDEX '' + @indexname + N'' ON ''+ @databasename + N''.''+@schemaname + N''.'' + @objectname + N'' REORGANIZE''    
            END    
        IF @frag >= @rebuild_frag_thresh 
            BEGIN        
                SET @intentions = @intentions + N'' OPERATION: REBUILD''        
                SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @databasename + N''.''+ @schemaname + N''.'' + @objectname + N'' REBUILD''    
            END    
        IF @partitioncount > 1 
            BEGIN        
                SET @intentions = @intentions + N'' PARTITION: '' + CAST(@partitionnum AS nvarchar(10))       
                SET @command = @command + N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10))    
            END    
        IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100 
            BEGIN        
                SET @intentions = @intentions + N'' FILL FACTOR: '' + CAST(@fill_factor AS nvarchar)       
                SET @command = @command + N'' WITH (FILLFACTOR = '' + CAST(@fill_factor AS nvarchar) + '')''    
            END     
        IF @report_only = 0 
            BEGIN        
                SET @intentions = @intentions + N'' EXECUTING: '' + @command        
                PRINT @intentions        
                EXEC (@command)    
            END 
        ELSE 
            BEGIN        
                PRINT @intentions    
            END  
        END 
CLOSE partitions
DEALLOCATE partitions
'
    --print @reindexcommand
    EXEC (@reindexcommand) 
    SELECT'***************************************************************'


    end

close dbreindex
deallocate dbreindex
go
Related Question