MS SQL de indexlerin önemini ve özelliklerini ilerde zaman bulunca uzunca yazip anlatacagim ama simdilik bu meslektaslarin isine yarayabilir.
MS SQL server tüm veritabanlarindaki, ya da bizim istedigimiz veritabanlarindaki indexleri belirli zaman araliklarinda güncellemesine yarayan SQL Script kodlari asagidaki gibidir; 

Belirli zaman araliklarini elbette SQL server üzerinde SQL Agent > Jobs > New Job altinda zamanlayabilirsiniz;


DECLARE @DatabaseName VARCHAR(100)
DECLARE @strSQL VARCHAR(8000)

SELECT Name
INTO #tblDatabases
FROM sys.databases
WHERE name NOT IN ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')

SET @strSQL = ''

WHILE (0 < (SELECT COUNT(*) FROM #tblDatabases))
BEGIN
    SELECT TOP 1 @DatabaseName = Name FROM #tblDatabases ORDER BY Name
    IF ISNULL(@DatabaseName,'') = '' BEGIN
        BREAK
    END
    
    SET @strSQL = @strSQL + '
    USE '+@DatabaseName+'
    DECLARE @tableName'+@DatabaseName+' VARCHAR(100);
    DECLARE @indexAction'+@DatabaseName+' VARCHAR(100);
    DECLARE @sqlAction'+@DatabaseName+' VARCHAR(1000);
    DECLARE @db_id'+@DatabaseName+' SMALLINT;
    SET @db_id'+@DatabaseName+' = DB_ID(N'''+@DatabaseName+''');
    SELECT DISTINCT TableName,IndexAction
    INTO #tmpIndexes'+@DatabaseName+'
    FROM (
        SELECT OBJECT_NAME(dt.object_id) as TableName
            ,CASE WHEN dt.page_count > 100 AND dt.avg_fragmentation_in_percent > 5 AND dt.avg_fragmentation_in_percent <= 30 THEN ''REORGANIZE''
                WHEN dt.page_count > 100 AND  dt.avg_fragmentation_in_percent > 30 THEN ''REBUILD''
                ELSE ''SKIP''
            END AS IndexAction
        FROM (
            SELECT object_id, index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent, page_count
            FROM sys.dm_db_index_physical_stats(@db_id'+@DatabaseName+', NULL, NULL, NULL,''DETAILED'')
            WHERE index_id <> 0
        ) as dt
        INNER JOIN sys.indexes si ON si.object_id = dt.object_id AND si.index_id = dt.index_id
    ) AS TBL
    WHERE IndexAction <> ''SKIP''
    ORDER BY TableName,IndexAction
    
    WHILE (0 < (SELECT COUNT(*) FROM #tmpIndexes'+@DatabaseName+'))
    BEGIN
        SELECT TOP 1 @tableName'+@DatabaseName+' = TableName,@indexAction'+@DatabaseName+' = IndexAction FROM #tmpIndexes'+@DatabaseName+' ORDER BY TableName,IndexAction
        IF ISNULL(@tableName'+@DatabaseName+','''') = '''' BEGIN
            BREAK
        END
        IF @indexAction'+@DatabaseName+' = ''REBUILD'' BEGIN
            SET @sqlAction'+@DatabaseName+' = ''ALTER INDEX ALL ON [dbo].[''+@tableName'+@DatabaseName+'+''] REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )''
        END
        ELSE IF @indexAction'+@DatabaseName+' = ''REORGANIZE'' BEGIN
            SET @sqlAction'+@DatabaseName+' = ''ALTER INDEX ALL ON [dbo].[''+@tableName'+@DatabaseName+'+''] REORGANIZE WITH ( LOB_COMPACTION = ON )''
        END
        
        EXEC(@sqlAction'+@DatabaseName+');
        
        DELETE FROM #tmpIndexes'+@DatabaseName+' WHERE TableName = @tableName'+@DatabaseName+'
        SET @sqlAction'+@DatabaseName+' = NULL
        SET @tableName'+@DatabaseName+' = NULL
        SET @indexAction'+@DatabaseName+' = NULL
    END
    DROP TABLE #tmpIndexes'+@DatabaseName+'
    SET @tableName'+@DatabaseName+' = NULL
    SET @indexAction'+@DatabaseName+' = NULL
    SET @sqlAction'+@DatabaseName+' = NULL
    SET @db_id'+@DatabaseName+' = NULL
    
    '
    
    DELETE FROM #tblDatabases WHERE name = @DatabaseName
    
    --PRINT @strSQL
    EXEC(@strSQL);
    
    SET @DatabaseName = NULL
    SET @strSQL = ''
END

DROP TABLE #tblDatabases

GO

Burada dikkat edilmesi gereken nokta, eger veritabanlariniz gün içinde yogun çalisiyorlar ise bu islemi onlarin yogun olmadiklari zamanda yapmanizdir. Aksi halde kilitlenmelere neden olabilir.

Saglicakla kalin :)

If you like this, follow my RSS channel!