MSSQL Tablo INDEX bakımı – Sql Server Indexlerin Fragmentation İzlenmesi ve Bakımı

Veritabanımızda kullandığımız tablolardaki kayıtlar artamaya başladığında zamanla tablolarımızda eklemiş olduğumuz index yapılarında fragmantasyonlarında bozulmalar oluşabilir ,

Bu bozulmaları kontrol ve düzeltmek için , Manuel olarak tek tek tablolardaki indexleri rebuild veya  reorganize yapabileceğimiz gibi aşağıdaki sorgularımızda bozulan indexleri tespit edebilir ve tablolarımızdaki indexleri toplu şekilde bakımını yapabiliriz.

İndexlerin Fragmentation sorgulanması : 

Öncelikle sorgumuzda kullanacağımı fonksiyonu oluturuyoruz :

--

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

--

izleme sorgumuz: 

--

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')

--

Veritabanımızdaki tabloların indexlerini düzenleme sorgumuz :

Sorguda tüm db ler veya belirli bir db için işlem yapabilmeniz için yorum satırı ile kapatmış olduğum kısımları açıp isteğinize göre kullanabilirsiniz.

--

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 IN ('dbName')
--WHERE name NOT IN ('master','msdb','tempdb')
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  */
--PRINT REPLACE(@cmd,'"','''')
SET @cmd = REPLACE(@cmd,'"','''')
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) + ')'
--PRINT @cmd
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


--

İyi Çalışmalar Dilerim – Salih ŞEKER.