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は拾ってないので必要ならその辺修正。