İ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:
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 :
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 ('dbHatapAntepKKK')
--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