Azureはじめました

Windows Azureで業務システムを組んでみる日記

SQLサーバーのデータベースをうまいことメンテナンスするクエリ

インデックスの再構築とか断片化解消とかそのへんのスクリプトをまとめておく。
もう本当にAzure関係ねぇ。

断片化率のチェック

10%以上断片化してるものを表示。

SELECT 
	DB_NAME(s.database_id) as DBName, 
	OBJECT_NAME(s.object_id) as TableName, 
	s.index_type_desc, 
	i.name as IndexName , 
	s.fragment_count, 
	s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') s
INNER JOIN sys.indexes i ON i.object_id = s.object_id  and i.index_id = s.index_id
WHERE s.avg_fragmentation_in_percent > 10;

断片化を一気に解消

DECLARE 
	@tableName varchar(100),
	@indexName varchar(100),
	@sql varchar(512)

-- 
-- 10%以上断片化しているインデックスを抽出
--
DECLARE FlagmentedIndex CURSOR FOR
SELECT 
	OBJECT_NAME(s.object_id) as TableName, 
	i.name as IndexName
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') s
INNER JOIN sys.indexes i ON i.object_id = s.object_id  and i.index_id = s.index_id
WHERE s.avg_fragmentation_in_percent > 10;

-- 
-- 再構築
--
OPEN FlagmentedIndex
FETCH NEXT FROM FlagmentedIndex INTO @tableName,@indexName
WHILE   @@FETCH_STATUS = 0
	BEGIN
		SET @sql ='ALTER INDEX '+@indexName+ ' ON ' + @tableName +' REBUILD WITH(ONLINE=ON);'
		PRINT @sql
		EXECUTE(@sql)

		SET @sql ='-- ALTER INDEX '+@indexName+ ' ON ' + @tableName +' REORGANIZE;'
		PRINT @sql
		EXECUTE(@sql)

        FETCH NEXT FROM FlagmentedIndex INTO @tableName,@indexName
    END
CLOSE FlagmentedIndex
DEALLOCATE FlagmentedIndex

REBUILDするようにしてるけどREORGANIZEを使うなら適宜コメント外したりする。

(ヒープテーブルはクラスター化インデックスが無いと解消できないので、まずクラスター化インデックスの作成をしてから。)

ヒープテーブルのプライマリキーをCRUSTERD にして CRUSTERD TABLEに。

大昔から引き継いでるDBとかヒープテーブルのまま使ってたりするのでこれを変更

DECLARE 
	@dbName VARCHAR(100), 
	@tableName varchar(100),
	@pkName varchar(100),
	@PKcolumnName varchar(512),
	@sql varchar(512)

-- 
-- ヒープテーブルとプライマリキーを抽出
--
DECLARE HeapTables CURSOR FOR
SELECT 
	DB_NAME(s.database_id), 
	OBJECT_NAME(s.object_id),
	pk.name
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') s
	LEFT JOIN sys.indexes pk on pk.object_id= s.object_id and pk.is_primary_key>0
where 
	s.index_type_desc='HEAP' and pk.name is not null
order by OBJECT_NAME(s.object_id)

-- 
-- DROP CONSTRAINT PK→ ADD CONSTRAINT PK CRUSTERD で作り直し
--
OPEN HeapTables
FETCH NEXT FROM HeapTables INTO @dbName,@tableName,@pkName
WHILE   @@FETCH_STATUS = 0
	BEGIN
		SET @sql ='ALTER TABLE '+@tableName+' DROP CONSTRAINT '+ @pkName + ';'
		PRINT @sql

		SET @PKcolumnName = (select REPLACE(RTRIM(
			 (select COLUMN_NAME+' ' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
				WHERE 
					OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND table_name = @tableName
				order by ORDINAL_POSITION 
				FOR XML PATH(''))
			),' ',',')
		)

		SET @sql = 'ALTER TABLE '+@tableName +' ADD  CONSTRAINT '+@pkName+' PRIMARY KEY CLUSTERED ('+@PKcolumnName+') WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY];'
		PRINT(@sql)

        FETCH NEXT FROM HeapTables INTO @dbName,@tableName,@pkName
    END
CLOSE HeapTables
DEALLOCATE HeapTables

SQLが出力されるのでそれを適宜実行。
PKのカラムは拾ってるけどasc/descは拾ってないので必要ならその辺修正。