まえがき
インターネットでは、インデックスの最適化または再構築に関する多くの情報を見つけることができます。 ただし、ほとんどの推奨事項は、ロード時間が最小のデータベース（主に夜間）に向けられています。
しかし、データの変更と24時間365日の情報の受信の両方に常に使用されるデータベースについてはどうでしょうか。
この記事では、データベース内のインデックスの最適化を自動化するための実装メカニズムを提供して、企業内のデータベースをサポートします。 24x7システムではインデックスの断片化が常に発生するため、このメカニズムにより、必要なインデックスを常に最適化できます。 また、インデックスの1日1回のデフラグでも十分ではありません。
解決策
まず、一般的なアプローチ：
1）目的のデータベースのビューを作成します。これを使用して、どのインデックスと何パーセントのフラグメントを取得できます
2）インデックスの最適化結果を保存するためのテーブルを作成します
3）選択したインデックスを分析および最適化するストアドプロシージャを作成する
4）インデックスの最適化の結果に関する統計を表示するビューを作成します
5）3で実装されたストアドプロシージャを起動するタスクをエージェントで作成します。
そして今、実装：
1）目的のデータベースのビューを作成します。これを使用して、断片化されているインデックスとパーセントを取得できます。
コード
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [srv].[vIndexDefrag] as with info as (SELECT [object_id], database_id, index_id, index_type_desc, index_level, fragment_count, avg_fragmentation_in_percent, avg_fragment_size_in_pages, page_count, record_count, ghost_record_count FROM sys.dm_db_index_physical_stats (DB_ID(N'__') , NULL, NULL, NULL , N'LIMITED') where index_level = 0 ) SELECT b.name as db, s.name as shema, t.name as tb, i.index_id as idx, i.database_id, idx.name as index_name, i.index_type_desc,i.index_level as [level], i.[object_id], i.fragment_count as frag_num, round(i.avg_fragmentation_in_percent,2) as frag, round(i.avg_fragment_size_in_pages,2) as frag_page, i.page_count as [page], i.record_count as rec, i.ghost_record_count as ghost, round(i.avg_fragmentation_in_percent*i.page_count,0) as func FROM Info as i inner join [sys].[databases] as b on i.database_id = b.database_id inner join [sys].[all_objects] as t on i.object_id = t.object_id inner join [sys].[schemas] as s on t.[schema_id] = s.[schema_id] inner join [sys].[indexes] as idx on t.object_id = idx.object_id and idx.index_id = i.index_id where i.avg_fragmentation_in_percent >= 30 and i.index_type_desc <> 'HEAP'; GO
このビューには、断片化の割合が30以上のインデックスのみが表示されます。最適化する必要のあるインデックス。 ヒープではないインデックスのみが出力されます。後者はデフラグされると、そのようなヒープをブロックするか、さらに断片化されたインデックスのいずれかで表されるというマイナスの影響を与える可能性があるためです。
ビューは、重要なsys.dm_db_index_physical_statsシステムビューを使用します（ 詳細 ）。
2）テーブルを作成して、インデックスの最適化の結果を保存します。
コード
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [srv].[Defrag]( [ID] [bigint] IDENTITY(794,1) NOT NULL, [db] [nvarchar](100) NULL, [shema] [nvarchar](100) NULL, [table] [nvarchar](100) NULL, [IndexName] [nvarchar](100) NULL, [frag_num] [int] NULL, [frag] [decimal](6, 2) NULL, [page] [int] NULL, [rec] [int] NULL, [func] [int] NULL, [ts] [datetime] NULL, [tf] [datetime] NULL, [frag_after] [decimal](6, 2) NULL, [object_id] [int] NULL, [idx] [int] NULL, [InsertUTCDate] [datetime] NOT NULL, CONSTRAINT [PK_Defrag] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]; GO ALTER TABLE [srv].[Defrag] ADD CONSTRAINT [DF_Defrag_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate]; GO
主なことは、このテーブルのデータを削除することを忘れないことです（たとえば、1か月以上前またはそれ以上）。
表のフィールドは、次の段落で理解されます。
3）選択したインデックスを分析および最適化するストアドプロシージャを作成します。
コード
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[AutoDefragIndex] AS BEGIN SET NOCOUNT ON; -- declare @IndexName nvarchar(100) -- ,@db nvarchar(100) -- ,@Shema nvarchar(100) -- ,@Table nvarchar(100) -- ,@SQL_Str nvarchar (2000) -- ,@frag decimal(6,2) --% ,@frag_after decimal(6,2) --% -- IN_ROW_DATA ,@frag_num int ,@func int --round(i.avg_fragmentation_in_percent*i.page_count,0) ,@page int --- ,@rec int -- - ,@ts datetime -- ,@tf datetime -- -- , ,@object_id int ,@idx int; --ID -- set @ts = getdate(); -- -- . , -- , select top 1 @IndexName = index_name, @db=db, @Shema = shema, @Table = tb, @frag = frag, @frag_num = frag_num, @func=func, @page =[page], @rec = rec, @object_id = [object_id], @idx = idx from [srv].[vIndexDefrag] order by func*power((1.0- convert(float,(select count(*) from SRV.[srv].[Defrag] vid where vid.db=db and vid.shema = shema and vid.[table] = tb and vid.IndexName = index_name)) / convert(float, case when (exists (select top 1 1 from SRV.[srv].[Defrag] vid1 where vid1.db=db)) then (select count(*) from SRV.[srv].[Defrag] vid1 where vid1.db=db) else 1.0 end)) ,3) desc -- if(@db is not null) begin -- set @SQL_Str = 'alter index ['+@IndexName+'] on ['+@Shema+'].['+@Table+'] Reorganize'; execute sp_executesql @SQL_Str; -- set @tf = getdate() -- SELECT @frag_after = avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(@db), @object_id, @idx, NULL , N'DETAILED') where index_level = 0; -- insert into SRV.srv.Defrag( [db], [shema], [table], [IndexName], [frag_num], [frag], [page], [rec], ts, tf, frag_after, object_id, idx ) select @db, @shema, @table, @IndexName, @frag_num, @frag, @page, @rec, @ts, @tf, @frag_after, @object_id, @idx; -- set @SQL_Str = 'UPDATE STATISTICS ['+@Shema+'].['+@Table+'] ['+@IndexName+']'; execute sp_executesql @SQL_Str; end END
4）インデックスの最適化の結果に関する統計を表示するビューを作成します。
コード
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [srv].[vStatisticDefrag] as SELECT top 1000 [db] ,[shema] ,[table] ,[IndexName] ,avg([frag]) as AvgFrag ,avg([frag_after]) as AvgFragAfter ,avg(page) as AvgPage FROM [srv].[Defrag] group by [db], [shema], [table], [IndexName] order by abs(avg([frag])-avg([frag_after])) desc; GO
このビューを使用して、インデックスの最適化を自動化するために行われた作業を管理者に毎日通知できます。
5）手順3で実装されたストアドプロシージャを実行するエージェントでタスクを作成します。
ここでは、実験的に時間を選択する必要があります。 約5分、どこかで約1時間かかりました。
このアルゴリズムは複数のデータベースに拡張できますが、ポイント6も入力する必要があります。
データベース内のインデックスデフラグの自動化に関するすべての統計を1か所で収集し、後で管理者に送信します。
そして今、私はインデックスサポートのためのすでにレイアウトされた推奨事項についてより詳しく説明したいと思います：
1）最小データベースロード中のすべてのインデックスのデフラグ-インデックスは絶えず断片化されており、データベースのダウンタイムが実質的にないため、これは24時間365日のシステムでは受け入れられません。
2）インデックスの再構築-この操作は、テーブルまたはセクションをブロックします（パーティションインデックスの場合）。これは、24時間365日のシステムには適していません。 さらに、リアルタイムインデックスの再構築はエンタープライズソリューションでのみサポートされており、データの破損につながる可能性もあります。
この方法は最適ではありませんが、オプティマイザーが実行計画を作成するために使用するためにインデックスが十分に最適化（断片化の30〜40％以下）されていることを確認できます。
コメントにこのアプローチの賛否両論、および実証済みの代替提案がある場合、私は非常に感謝します。
ソース：
インデックスの再編成と再構築
» Sys.dm_db_index_physical_stats