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.
