MSSQL üzerindeki tablolarda INDEX bakımı

Zaman zaman sizlerinde başına gelmiştir, çeşitli veritabanları ile çalışırken gayet düzgün çalışan bir veritabanı bir anda kararsız duruma düşebilir ya da yavaşlayabilir.

Bunun temel nedenlerinden bir tanesi tablolarda kullanılan INDEX yapılarının fragmantasyonlarının bozulmasıdır.

Peki bu durumları nasıl tespit edebiliriz ya da index yapısı bozuldu diyelim. Bunu nasıl düzeltebiliriz?

Normal şartlarda her tablonun index özelliklerine girip ilgili fragmantasyon değerinin kontrol edilmesi ve buna göre yine aynı menü üzerinden REPAIR ya da REBUILD işleminin yapılması gerekir.

Fakat veritabanı sayınız 5 den fazla ve her DB üzerindeki tablo sayınızda 20 den fazla ise çok işim var! demekte haklısınız 

Malumunuz programcı dediğin ya çok tembel olur (kendine göre) ya da çok pratik olur (diğerlerine göre) bu durumda da mutlaka bir çözüm bulabilir 

İlk olarak yapılması gereken işlem: Query Editor üzerinde

select * from sys.dm_db_index_physical_stats (DB_ID(),null,null,null,'LIMITED') WHERE avg_fragmentation_in_percent >10.0 and index_id>0

komutu ile veritabanlarımız üzerindeki dağılmış indexlerin (%10 dan  fazla) durumunu öğreniriz.

ancak gelen sorgu sonucunda da görebileceğiniz gibi kolonlarda ne bir DB adı ne de Index adı bulunmuyor, peki bu bilgileri nasıl öğrenebiliriz.

Çözüm

CREATE FUNCTION dbo.index_name (@object_id int, @index_id int)

RETURNS sysname

AS

BEGIN

RETURN(SELECT name FROM sys.indexes WHERE

object_id = @object_id and index_id = @index_id)

END;

GO

ile öncelikle fonksiyonumuzu yaratıyoruz. Sonra da;

SELECT

OBJECT_NAME(object_id) AS tabloadi

,dbo.index_name(object_id, index_id) AS indexadi

,avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)

WHERE avg_fragmentation_in_percent > 20

AND index_type_desc IN('CLUSTERED INDEX', 'NONCLUSTERED INDEX')

ile hangi tablonun hangi index verisi ne oranda bozulmuş görebiliyoruz.

Artık hangi tabloda hangi index bozulmuş gördüğümüze göre bunları elimizle düzeltebiliriz. Yok ben yine tembelliğimden şaşmak istemiyorum 

Aşağıdaki kod ile tüm veritabanlarında yer alan tüm index yapıları kontrol ediliyor ve fragmantasyon yapılıyor

DECLARE @Database VARCHAR(255)

DECLARE @Table VARCHAR(255)

DECLARE @cmd NVARCHAR(500)

DECLARE @fillfactor INT

SET @fillfactor = 90 

DECLARE DatabaseCursor CURSOR FOR

SELECT name FROM MASTER.dbo.sysdatabases

WHERE name NOT IN ('master','msdb','tempdb','model','distribution')

ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +

table_name + '']'' as tableName FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES

WHERE table_type = ''BASE TABLE'''

/* create table cursor  */

EXEC (@cmd)

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Table

WHILE @@FETCH_STATUS = 0

BEGIN

IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)

BEGIN

/* SQL 2005 veya sonrası*/

SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

EXEC (@cmd)

END

ELSE

BEGIN

/* SQL 2000 */

DBCC DBREINDEX(@Table,' ',@fillfactor)

END

FETCH NEXT FROM TableCursor INTO @Table

END

CLOSE TableCursor

DEALLOCATE TableCursor

FETCH NEXT FROM DatabaseCursor INTO @Database

END

CLOSE DatabaseCursor

DEALLOCATE DatabaseCursor 

İşte hepsi bu kadar…

Siteden SQL komutlarını kopyala yapıştır ile alırken sorun yaşayan kullanıcılar için bu kodları TXT formatında kaydettim isteyenler SQL Server Index Bakımı adresinden ulaşabilir.

Kaynak