Veritabanınızda tüm tabloların indexlerini defrag etmek scripti - SQL İndex ve Defrag Optimizasyonu

İlk olarak veritabanı üzerine sağ tıklayıp > Özellikler (Properties) > Options > Recovery kısmındaki Model

seçeneğini Simple yapın.




- Öncelik olarak yoğun kullanılan tablolarınıza optimize işlemi yapın. Aşağıdaki scripti kullanarak hagi tablolarınızda en çok kayıt var onu bulun.

CREATE PROCEDURE dbo.listTableRowCounts 
AS 
BEGIN 
    SET NOCOUNT ON 
 
    DECLARE @SQL VARCHAR(255) 
    SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')' 
    EXEC(@SQL) 
 
    CREATE TABLE #foo 
    ( 
        tablename VARCHAR(255), 
        rc INT 
    ) 
 
    INSERT #foo 
        EXEC sp_msForEachTable 
            'SELECT PARSENAME(''?'', 1), 
            COUNT(*) FROM ?' 
 
    SELECT tablename, rc 
        FROM #foo 
        ORDER BY rc DESC 
 
    DROP TABLE #foo 
END

- DBCC SHOWCONTIG kullanarak en yoğun olan tablo istatistiklerine göre hareket edelim. En basit kullanımı aşağıdaki gibidir.



DBCC SHOWCONTIG (TabloAdı)



Bu komut size liste halinde birçok veri geri verecektir. Şimdi bu komutun bize geri vereceği bilgilerin ne anlama geldiğine bakalım.

Pages Scanned: (Taranılmış Sayfa Sayısı) Eğer veritabanınızda yaklasik olarak ne kadar satır bulunduğunu biliyorsanız, bu bölümde size ne kadar page(sayfa) verileceğini tahmin etmeniz mümkün.



Extents Scanned: Tranılmış sayfa sayınızı alıp 8 e böldüğünüzde, elinize geçen rakamı en yakın rakama yuvarlayın ve Extents Scanned bölümünde elinize geçmesi gereken rakamı bulmuş olacaksınız. Eğer DBCC SHOWCONTIG tarafından size verilen rakam sizin hesapladığınızdan yüksek çıkıyorsa database/tablonuzda fragmentation(bölünme) var demektir. Aradaki fark çok büyükse, mutlaka makalenin sonundaki açıklayacağım adımları uygulayın.



Extent Switches: Bu rakam Extents Scanned bölümdeki rakamdan 1 rakam küçük olmalıdır. Rakamlar arasındaki yüksek fark, database/tablonuzda bir veri fragmantasyonu olduğunu gösterir.



Avg Pages per Extent: Pages Scanned / Extents Scanned e eşittir ve doğal olarak 8'e eşit olmalıdır. 8'den küçük rakamlar database/tablo fragmantasyonu olduğunu gösterir.



Scan Density: (En önemli bölümlerden biri) Bu bölümde DBCC CONTIG tarafından size gösterilen deger, yüzde 100'e olabildiğince yakın olmalıdır. Düşük değerler veritabanınızın/tablonun fragmantasyona uğradığını gösterir.



Logical Scan Fragmentation: Veritabanınızdaki olması gerektiği yerde bulunmayan page'lerin oranını gösterir.Yüzde 0 ile yüzde 10 arasında olmalıdır. Yüzde 10'un üzerinde ise, veritabanınız/tablonuz fragmentation'a uğramış demektir.

Extent Scan Fragmentation: Veritabanınızdaki alanlar arasındaki boşlukların yüzdesini gösterir. Yüzdelik değer 0 olmalıdır. Daha yüksek yüzde değerleri, databaseinizin/tablonuzun fragmentation'a uğradığını gösterir.

Avg.

Bytes Free per Page: Sayfalardaki ortalama boş alanı gösterir. Yüksek rakamlar database'inizin/tablonuzun fragmantasyona uğradığını gösterir.



Avg Page Density: 1/Avg Bytes Free Per page . Yukarıdaki değerin yüzdelik şekilde belirtilmiş halidir. Düşük yüzdeler database/tablonun fragmentasyonuna işarettir.



Database Fragmentation (Bölünmeleri) İçin Çözümler


1. Indexlerinizi Drop ederek tekrar yaratabilirsiniz.
2. İndexlerinizi Recreate komutunun DROP EXISTING özelliğini kullanarak tekrar yaratabilirsiniz.
3. DBCC DBREINDEX komutunu kullanabilirsiniz.
4. DBCC INDEXDEFRAG komutunu kullanarak indexlerinizi defragment(birleştirmek) edebilirsiniz.

Eğer veritabanınız üzerinde o sırada işlemler yapılıyorsa, indexi drop etme(atma) opsiyonunu seçerseniz yeni indexi çok çabuk şekilde yaratmalısınız yoksa veritabanınız indexsiz şekilde çok verimsiz çalışabilir.



DBCC DBREINDEX komutu çalışırken durdurulduğu takdirde, bütün defragmentasyon kaybedilir ve yeniden defragment komunutunu çalıştırmanız gerekir.

DBCC INDEXDEFRAG komutu, veritabanınız üzerinde en az yükü yaratacak opsiyondur. O anda gerçekleşen SELECT tarzı indexi kullanan komutlarla herhangi bir çakışmaya sebep olmaz. Ancak sonuçları yeni bir index oluşturmak kadar verimli olmayabilir. Çünkü bu komut indexleri düzene sokmak için diğer yukarıdaki methodlar gibi yeni sayfalar yaratmaz.




Not : Özellikler yukarıdaki bilgileri benim yazmadığımı belirteyim bu bilgileri http://teknoturk.blogspot.com/2005/10/database-performans-ve-tuning.html adresinden alınmıştır.

- Veritabanınızda tüm tabloların indexlerini defrag etmek için aşağıdaki scripti kullanabilirsiniz.

SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr   VARCHAR (255)
DECLARE @objectid  INT
DECLARE @indexid   INT
DECLARE @frag      DECIMAL
DECLARE @maxfrag   DECIMAL

-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0

-- Declare cursor
DECLARE tables CURSOR FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the table
CREATE TABLE #fraglist (
   ObjectName CHAR (255),
   ObjectId INT,
   IndexName CHAR (255),
   IndexId INT,
   Lvl INT,
   CountPages INT,
   CountRows INT,
   MinRecSize INT,
   MaxRecSize INT,
   AvgRecSize INT,
   ForRecCount INT,
   Extents INT,
   ExtentSwitches INT,
   AvgFreeBytes INT,
   AvgPageDensity INT,
   ScanDensity DECIMAL,
   BestCount INT,
   ActualCount INT,
   LogicalFrag DECIMAL,
   ExtentFrag DECIMAL)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
   FROM tables
   INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
   FETCH NEXT
      FROM tables
      INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN indexes

-- loop through the indexes
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%'
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')'
   EXEC (@execstr)

   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table
DROP TABLE #fraglist
GO 

Kaynaklar :
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
http://sqlserver2000.databases.aspfaq.com/....html
http://teknoturk.blogspot.com/2005/10/database-performans-ve-tuning.html

http://sqlservercode.blogspot.com/