SQL veritabani üzerinde indekslerin önemini hepimiz biliyoruz. Ancak olusturdugumuz bu indekslerin zaman zaman bakima alinmasi gerekiyor. Burada amaç veri parçalari arasinda olusan bosluklarin alinmasi ve indeksleme zamanlarinin ve index araliklarinin küçültülerek performans artisinin saglanmasidir. Bu islem için SQL server üzerinde asagidaki kod parçacigini zamanlanmis görev olarak çalistirdigimizda gerekli islem yapilmis olacaktir. Burada dikkat edilmesi gereken husus, eger veritabani çok yogun çalisiyor ise, bu zamanlanmis görevin,  yogunlugun en az oldugu zaman zarfinda olmasini önemle öneririm.

 

DECLARE @DatabaseName VARCHAR(100)
DECLARE @strSQL VARCHAR(8000)

SET @strSQL = ''

SELECT Name
INTO #tblDatabases
FROM sys.databases
WHERE name IN ('myFirstDB','mySecondDB')

WHILE (0 < (SELECT COUNT(*) FROM #tblDatabases))
BEGIN
	SELECT TOP 1 @DatabaseName = Name FROM #tblDatabases ORDER BY Name
	IF ISNULL(@DatabaseName,'') = '' BEGIN
		BREAK
	END
	
	SET @strSQL = @strSQL + '
	USE '+@DatabaseName+'
	GO
	DECLARE @tableName VARCHAR(100);
	DECLARE @indexAction VARCHAR(100);
	DECLARE @sqlAction VARCHAR(1000);
	DECLARE @db_id SMALLINT;
	SET @db_id = DB_ID(N'''+@DatabaseName+''');
	SELECT DISTINCT TableName,IndexAction 
	INTO #tmpIndexes
	FROM (
		SELECT OBJECT_NAME(dt.object_id) as TableName
			,CASE WHEN dt.page_count > 100 AND dt.avg_fragmentation_in_percent > 5 AND dt.avg_fragmentation_in_percent <= 30 THEN ''REORGANIZE''
				WHEN dt.page_count > 100 AND  dt.avg_fragmentation_in_percent > 30 THEN ''REBUILD''
				ELSE ''SKIP''
			END AS IndexAction
		FROM (
			SELECT object_id, index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent, page_count 
			FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL,''DETAILED'')
			WHERE index_id <> 0
		) as dt 
		INNER JOIN sys.indexes si ON si.object_id = dt.object_id AND si.index_id = dt.index_id
	) AS TBL 
	WHERE IndexAction <> ''SKIP''
	ORDER BY TableName,IndexAction
	
	WHILE (0 < (SELECT COUNT(*) FROM #tmpIndexes)) 
	BEGIN
		SELECT TOP 1 @tableName = TableName,@indexAction = IndexAction FROM #tmpIndexes ORDER BY TableName,IndexAction
		IF ISNULL(@tableName,'''') = '''' BEGIN
			BREAK
		END
		IF @indexAction = ''REBUILD'' BEGIN
			SET @sqlAction = ''ALTER INDEX ALL ON [dbo].[''+@tableName+''] REBUILD WITH ( FILLFACTOR = 90 )''
		END
		ELSE IF @indexAction = ''REORGANIZE'' BEGIN
			SET @sqlAction = ''ALTER INDEX ALL ON [dbo].[''+@tableName+''] REORGANIZE WITH ( FILLFACTOR = 90 )''
		END
		
		EXEC(@sqlAction);
		
		DELETE FROM #tmpIndexes WHERE TableName = @tableName
		SET @sqlAction = NULL
		SET @tableName = NULL
		SET @indexAction = NULL
	END
	DROP TABLE #tmpIndexes
	GO
	
	' 
	
	DELETE FROM #tblDatabases WHERE name = @DatabaseName
	
	SET @DatabaseName = NULL
END

DROP TABLE #tblDatabases

--PRINT @strSQL
EXEC(@strSQL);

GO

Saglicakla kalin :)

If you like this, follow my RSS channel!