Selamlar...
Sql Server'da indeksler zamanla parçalanıyor. Bu da okuma ve yazma hızını etkiliyor.   
Bunlara arada sırada bakım yapmak gerekiyor. Ancak bakımın da bazı kriterleri var. 
Misal, hergün indekslere rebuild işlemi uygulamak ve ayrıca tüm indekslere bunu uygulamak da doğru değil.
İnternette araştırdığım kadarıyla genel yaklaşım, indeks'in ortalama bozulma oranı ve sayfa sayısına göre uygun bir çözüm bulmak yönünde. 
Sadece indeks oranı yeterli değil, ReBuild ya da ReOrganize için. Kayıt sayısı da önemli. Kayıt sayısı az olan tablolarda mesela bu değer büyük. 
Ancak  sayfa sayısı 1.000 küçükse işlem yapmaya çok da gerek olmayabilir. (https://technet.microsoft.com/en-us/library/cc966523.aspx)
http://www.turgaysahtiyan.com adresinde bir store prosedür buldum. Parçalanma oranına göre Rebuild ya da ReOrganize yapıyor. Gayet temiz ve iş gören bir script.
Aslında bu işin otoritesi Ola Hallengren diye biri sanırım. İnternette herkes onun hazırladığı bakım scriptinden bahsediyor. 
Hatta http://sqlskills.com/ sitesinden bir yönetici ile yazıştım birkaç soru sormak için, o bile direkt bunu söyledi.
Her şeyi yapıyor, fakat çok karışık. (https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html). O yüzden ilk script üzerinden gitmeyi tercih ettim.
Bu scripte istatistik, kayıt sayısı kontrolü ve sonuç raporu ile ilgili eklemeler yaptim.
İstatistik de performansı etkileyen bir unsur bazı durumlarda. Bir sorgu çalışmadan evvel SQL Server, kaç kayıt döneceğini hesaplamak için kullanıyor. 
Ona göre tabloyu okuyor. Aslında ReBuild işlemi ilgili indekse ait istatistiği de güncelliyor, fakat burada şunlar aklımıza geliyor:
1- ReOrganize yaptığımız indekslere ait istatistikleri güncellememiz gerekmiyor mu?
2- Rebuild sadece indekslere ait istatistikleri güncelliyor, ya tabloya ait indeks olmayan kolonlara ait istatistikler ne olacak?
Bu sorulara farklı kaynaklardan bulduğum cevapları harmanlayarak scriptte düzenlemeler yaptım.
Öncelikle bozulma oranının yanında sayfa sayısı da önemli olduğundan sayfa sayısı 1.000'den düşük olanlarda indekslere herhangi bir işlem yapmıyorum.
- İndeks ReBuild işleminden sonra istatistikleri güncellemekle ilgili herhangi bir işleme gerek yok.
- İndeks ReOrganize işleminden sonra istatistikleri güncelliyorum.
- Her iki işleme de tabi tutulmamış indeksler için de sadece istatistikleri güncelliyorum.
- İndekslerle ilgili işlem bittikten sonra da içerisinde kayıt olan tüm tabloları dolaşarak, sadece kolon istatistiklerini güncelliyorum.
- Tüm işlemler bittikten sonra da yetkili kişiye e-posta yolluyorum.
Bu işlem çok miktarda RAM tüketiyor. Sql Server'ın ilginç bir özelliği de RAM'i kullandıktan sonra işi bitse de bırakmıyor. Ancak RAM'e ihtiyaç duyan bir
iş çalışırsa işletim sistemi bırakmaya zorluyor. Ancak RAM'in bu şekilde yüksek gözükmesi herhangi bir performans sorununun işareti değil. 
SQL Server sadece alıkoyuyor, kullanmıyor aslında işi bitince.
Bunun haftada bir çalışması yeterli gibi görünüyor başlangıçta. Zaten bir süre sonra işlediği kayıt sayısında azalma olmasını bekleriz en azından indekslerde.
Ancak istatistikleri her hafta güncellemek işlem zamanını çok artırıyor. 
Sql Server veritabanının ayarı da AUTO_CREATE_STATISTICS->on ve AUTO_UPDATE_STATISTICS->on ise istatistikleri her hafta güncellemek çok da elzem değil gibi.
Bu sebeple scripte ayın başı olup olmadığını kontrol etme şartı ekledim.
Eğer script ayın ilk haftası çalışıyorsa istatistikleri de güncelliyor, değilse sadece indekslerle ilgileniyor.
 
IF OBJECT_ID('INDEKS_ISTATISTIK_BAKIMI', 'P') IS NOT NULL
  DROP PROCEDURE INDEKS_ISTATISTIK_BAKIMI 
GO 
 
CREATE PROC [INDEKS_ISTATISTIK_BAKIMI] @DBName VARCHAR(100) 
AS
  BEGIN
      SET NOCOUNT ON; 
      DECLARE @OBJECT_ID            INT, 
              @INDEX_NAME           SYSNAME, 
              @SCHEMA_NAME          SYSNAME, 
              @OBJECT_NAME          SYSNAME, 
              @AVG_FRAG             FLOAT, 
              @FRAG_COUNT           BIGINT, 
              @sqlCommand           NVARCHAR(MAX), 
              @sqlCommandIndex      NVARCHAR(MAX), 
              @sqlCommandStats      NVARCHAR(MAX), 
              @rebuildCount         INT, 
              @reOrganizeCount      INT,
              @indexStatsCount      INT, 
              @kolonStatsCount      INT, 
              @reOrganizeStatsCount INT, 
              @tableName            SYSNAME, 
              @schemaName           SYSNAME, 
              @msgContent           NVARCHAR(MAX),
              @msgHeader            NVARCHAR(MAX),
              @dStart               DATETIME,
              @dFinish              DATETIME,
              @ayinKacinciHaftasi   INT
      CREATE TABLE #tempIM 
        ( 
           [ID]             [INT] IDENTITY(1, 1) NOT NULL PRIMARY KEY, 
           [INDEX_NAME]     SYSNAME NULL, 
           [OBJECT_ID]      INT NULL, 
           [SCHEMA_NAME]    SYSNAME NULL, 
           [OBJECT_NAME]    SYSNAME NULL, 
           [AVG_FRAG]       FLOAT,
           [FRAG_COUNT]     BIGINT
        ) 
      SELECT @rebuildCount = 0, 
             @reOrganizeCount = 0,
             @indexStatsCount = 0,
             @reOrganizeStatsCount = 0,
             @kolonStatsCount = 0;
      SELECT @msgContent = '',
             @sqlCommand = '',
             @sqlCommandIndex = '',
             @sqlCommandStats = '';  
      SET @ayinKacinciHaftasi = (SELECT DATEPART(DAY, DATEDIFF(DAY, 0, GETDATE())/7 * 7) / 7 + 1);
      SELECT @msgHeader = '<html><head><style type="text/css">table.tblMesaj {font-family: verdana,arial,sans-serif;font-size:11px;color:#333333;border-width: 1px;border-color: #666666;border-collapse: collapse;}' ;
      SET @msgHeader = @msgHeader + 'table.tblMesaj th {border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;}' ;  
      SET @msgHeader = @msgHeader + 'table.tblMesaj td {border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;}</style></head><body>' ;
      SET @msgHeader = @msgHeader + '<table class="tblMesaj"><tr><th colspan="2">İNDEKS - İSTATİSTİK BAKIMI SONUÇLARI</th></tr>' ;
      SET @dStart = GETDATE();
       
      SELECT @sqlCommand = 'USE ' + @DBName + '; INSERT INTO #tempIM (OBJECT_ID, INDEX_NAME, SCHEMA_NAME, OBJECT_NAME, AVG_FRAG, FRAG_COUNT) ';
      SET @sqlCommand = @sqlCommand + ' SELECT ps.object_id, i.name as IndexName, OBJECT_SCHEMA_NAME(ps.object_id) as ObjectSchemaName, OBJECT_NAME (ps.object_id) as ObjectName, ';
      SET @sqlCommand = @sqlCommand + ' ps.avg_fragmentation_in_percent, ps.fragment_count FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, ''LIMITED'') ps INNER JOIN sys.indexes i ';
      SET @sqlCommand = @sqlCommand + ' ON i.object_id = ps.object_id and i.index_id = ps.index_id WHERE ps.index_id > 0 and ps.database_id = DB_ID(''' + @DBName + ''') ORDER BY avg_fragmentation_in_percent desc ' ;     
      EXEC(@sqlCommand) ; 
      
      SELECT @sqlCommandIndex = 'USE ' + @DBName + '; ';
      DECLARE c CURSOR FAST_FORWARD FOR
        SELECT OBJECT_ID, 
               INDEX_NAME, 
               SCHEMA_NAME, 
               OBJECT_NAME, 
               AVG_FRAG,
               FRAG_COUNT
        FROM   #tempIM 
      OPEN c 
      FETCH NEXT FROM c INTO @OBJECT_ID, @INDEX_NAME, @SCHEMA_NAME, @OBJECT_NAME, @AVG_FRAG, @FRAG_COUNT 
      WHILE @@FETCH_STATUS = 0 
        BEGIN
            -- Reorganize veya Rebuild İşlemi.
            IF @AVG_FRAG > 30 AND @FRAG_COUNT > 1000 
              BEGIN
                  SET @sqlCommandIndex = @sqlCommandIndex + ' ALTER INDEX [' + @INDEX_NAME + '] ON [' + @SCHEMA_NAME + '].[' + @OBJECT_NAME + '] REBUILD WITH (ONLINE = ON);' ; 
                  SET @rebuildCount = @rebuildCount + 1 ;
              END
            ELSE -- Rebuild index istatistikleri de guncelliyor. Ancak reorganize istatistikleri guncellemiyor.
                IF @AVG_FRAG <= 30 AND @AVG_FRAG > 5 AND @FRAG_COUNT > 1000 
                    BEGIN
                          SET @sqlCommandIndex = @sqlCommandIndex + ' ALTER INDEX [' + @INDEX_NAME + '] ON [' + @SCHEMA_NAME + '].[' + @OBJECT_NAME + '] REORGANIZE;'
                          SET @sqlCommandStats = @sqlCommandStats + ' UPDATE STATISTICS [' + @SCHEMA_NAME + '].[' + @OBJECT_NAME + '] [' + @INDEX_NAME + '] WITH FULLSCAN, NORECOMPUTE;' ;  
                          SET @reOrganizeCount = @reOrganizeCount + 1 ; 
                          SET @reOrganizeStatsCount = @reOrganizeStatsCount + 1 ; 
                    END
                ELSE -- Kalanlari da ekleyelim. 
                    BEGIN
                          SET @sqlCommandStats = @sqlCommandStats + ' UPDATE STATISTICS [' + @SCHEMA_NAME + '].[' + @OBJECT_NAME + '] [' + @INDEX_NAME + '] WITH FULLSCAN, NORECOMPUTE;'; 
                          SET @indexStatsCount = @indexStatsCount + 1 
                    END
            FETCH NEXT FROM c INTO @OBJECT_ID, @INDEX_NAME, @SCHEMA_NAME, @OBJECT_NAME, @AVG_FRAG, @FRAG_COUNT  
        END
      CLOSE c 
      DEALLOCATE c 
      DROP TABLE #tempIM 
      EXEC (@sqlCommandIndex); 
      --PRINT 'indeksler tamamdır.';
-- Son olarak da indeksler haricindeki kolonlarin istatistiklerini guncelleyelim.
      IF (@ayinKacinciHaftasi = 1)
        BEGIN
          CREATE TABLE #tempIST 
            ( 
               [ID]             [INT] IDENTITY(1, 1) NOT NULL PRIMARY KEY, 
               [TABLE_NAME]     SYSNAME NULL, 
               [SCHEMA_NAME]    SYSNAME NULL
            ) 
           
          SELECT @sqlCommand = 'USE ' + @DBName + '; INSERT INTO #tempIST (TABLE_NAME, SCHEMA_NAME) ';
          SET @sqlCommand = @sqlCommand + ' SELECT t.NAME, s.Name FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id ';
          SET @sqlCommand = @sqlCommand + ' INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id ';
          SET @sqlCommand = @sqlCommand + ' INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id ';
          SET @sqlCommand = @sqlCommand + ' LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id ';
          SET @sqlCommand = @sqlCommand + ' WHERE t.NAME NOT LIKE ''dt%'' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 AND a.used_pages > 0 GROUP BY t.Name, s.Name' ;
          EXEC(@sqlCommand) ; 
           
          SELECT @sqlCommandStats = 'USE ' + @DBName + '; ' + @sqlCommandStats;
          DECLARE c2 CURSOR FAST_FORWARD FOR
            SELECT TABLE_NAME, SCHEMA_NAME FROM #tempIST
          OPEN c2 
          FETCH NEXT FROM c2 INTO @tableName, @schemaName 
          WHILE @@FETCH_STATUS = 0 
            BEGIN
                SET @sqlCommandStats = @sqlCommandStats + ' UPDATE STATISTICS [' + @schemaName + '].[' + @tableName + '] WITH FULLSCAN, COLUMNS;'; 
                SET @kolonStatsCount = @kolonStatsCount + 1 ;
                FETCH NEXT FROM c2 INTO @tableName, @schemaName   
            END
          CLOSE c2; 
          DEALLOCATE c2; 
          DROP TABLE #tempIST;
          EXEC (@sqlCommandStats);
          --PRINT 'kolonlar tamamdır.';
        END
         
      SELECT 'İşlem başarılı' AS RESULT ;
       
      SET @dFinish = GETDATE();
      SET @msgContent = @msgHeader + '<tr><td>Başlangıç zamanı</td><td>' + CAST(@dStart AS nvarchar(30)) + '</td></tr>'
                        + '<tr><td>Bitiş zamanı</td><td>' + CAST(@dFinish AS nvarchar(30)) + '</td></tr>'
                        + '<tr><td>Rebuild işlemine tabi tutulmuş indeks sayısı</td><td>' + CAST(@rebuildCount AS VARCHAR(5)) + '</td></tr>'
                        + '<tr><td>Reorganize işlemine tabi tutulmuş indeks sayısı</td><td>' + CAST(@reOrganizeCount AS VARCHAR(5)) + '</td></tr>'
                        + '<tr><td>Reorganize işlemine tabi tutulmuş indekslere ait güncellenen istatistik sayısı</td><td>' + CAST(@reOrganizeStatsCount AS VARCHAR(5)) + '</td></tr>'
                        + '<tr><td>Rebuild/Reorganize işlemine tabi tutulmamış indekslere ait güncellenen istatistik sayısı</td><td>' + CAST(@indexStatsCount AS VARCHAR(5)) + '</td></tr>'
                        + '<tr><td>İndeks olmayan güncellenen kolon istatistik sayısı</td><td>' + CAST(@kolonStatsCount AS VARCHAR(5)) + '</td></tr></table></body></html>'
 
      EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'bt_noreply',
            @recipients = 'isminiz@firmadi.com.tr',
            @blind_copy_recipients = 'ikinciisim@firmadi.com.tr',
            @body = @msgContent,
            @body_format = 'HTML',
            @subject = 'İndeks-İstatistik Bakım' ;
 
  END
 
Bu prosedürü oluşturduktan sonra her hafta çalışan bir iş tanımlayıp çağırmak yeterli.
EXEC master.dbo.INDEKS_ISTATISTIK_BAKIMI 'VeritabaniAdi'
Saygılar...
# Kategoriler : Sql Server Veritabanı
# Etiketler : Etiket Yok
# Yorumlar : 0 Yorum Yorum Yaz

Yorumlar

Yorum Eklenmemiş...

Yorum Yaz

Adınız: *
E-Mail Adresiniz: *
Web Sitesi:
Yorum: *
Güvenlik Kodu: *
 
Arama
  Ara
Sayfalar
Takvim
<December 2018>
SMTWTFS
2526272829301
2345678
9101112131415
16171819202122
23242526272829
303112345
Bağlantılar