テーブルのボリュームが大きくなると、目的のない場所では耐え難いほど痛みを感じるようになりますが、インデックスを削除することはなくなり、使用統計からインデックスが使用されていることがわかります。
24時間年中無休で稼働する高負荷データベースでのインデックス統合の説明例。 アプリケーションはストアドプロシージャのみを使用します。 MS SQL Server 2012 SP3バージョン。
クラスター化インデックスを持つソーステーブル：
CREATE TABLE [dbo].[ClientFile]( [StorageId] [int] NOT NULL, [FolderId] [int] NOT NULL, [ClientFileInternalId] [int] IDENTITY(1,1) NOT NULL, [FileName] [nvarchar](900) NOT NULL, [FileExtension] [nvarchar](10) NOT NULL, [FileClientVersionId] [smallint] NOT NULL, [ClientFileVersionId] [int] NULL, [FileInternalId] [bigint] NOT NULL, [FileLength] [bigint] NOT NULL, [OrderId] [tinyint] NOT NULL, [FileFileExtensionId] [int] NULL, [FileStatus] [tinyint] NOT NULL, [DirectoryVersionId] [int] NOT NULL, [DateDeleted] [datetime] NULL, CONSTRAINT [PK_ClientFile] PRIMARY KEY CLUSTERED ( [StorageId] ASC, [ClientFileInternalId] ASC ) )
非クラスター化インデックス：
CREATE NONCLUSTERED INDEX [IX_ClientFile_StorageId_FileStatus] ON [dbo].[ClientFile] ( [StorageId] ASC, [FileStatus] ASC, [OrderId] DESC ) INCLUDE ( [ClientFileInternalId], [FolderId], [DirectoryVersionId], [FileInternalId], [FileClientVersionId], [FileLength]); CREATE NONCLUSTERED INDEX [IX_ClientFile_StorageId_FolderId_FileStatus_FileName] ON [dbo].[ClientFile] ( [StorageId] ASC, [FolderId] ASC, [FileStatus] ASC, [FileName] ASC ) INCLUDE ( [DateDeleted], [DirectoryVersionId], [FileExtension], [FileInternalId], [FileClientVersionId], [FileLength]);
テーブルの14フィールドの最初のインデックスには9が含まれ、2番目には10が含まれます。
合計でこれら2つのインデックスは、各サーバー、12サーバーで最大180 GBを占有しますが、一致するフィールドと6つのフィールドがそれぞれ含まれるインデックスがあるため、イライラして心配です。 また、クラスター化インデックスを使用する方が効率的で、プロシージャの再コンパイルが必要な場合に、サーバーがこれらのインデックスを選択することがあります。 再コンパイル後、クラスターインデックスは既に使用されており、CPUの負荷が軽減されています。
ステップ1.インデックス使用統計の分析
ClientFileテーブルのインデックスの使用に関する情報は、サーバーから収集されます。
テーブルインデックスの使用統計
declare @dbid int select @dbid = db_id() select (cast((user_seeks + user_scans + user_lookups) as float) / case user_updates when 0 then 1.0 else cast(user_updates as float) end) * 100 as [%] , (user_seeks + user_scans + user_lookups) AS total_usage , objectname=object_name(s.object_id), s.object_id , indexname=i.name, i.index_id , user_seeks, user_scans, user_lookups, user_updates , last_user_seek, last_user_scan, last_user_update , last_system_seek, last_system_scan, last_system_update , 'DROP INDEX ' + i.name + ' ON ' + object_name(s.object_id) as [Command] from sys.dm_db_index_usage_stats s, sys.indexes i where database_id = @dbid and objectproperty(s.object_id,'IsUserTable') = 1 and i.object_id = s.object_id and i.index_id = s.index_id and i.name not like 'PK_%' and object_name(s.object_id) = 'ClientFile' order by [%] asc
説明されているケースでは、両方のインデックスが使用されます。 何らかの種類のインデックスが使用されていない場合、これによりタスクが簡単になり、多くの場合、単純に削除できます。 これらの統計は、MS SQL Serverの再起動時にクリアされます。それに基づいてインデックスを削除するかどうかを決定する場合、データベースに月に1回考慮され、このインデックスを使用する非常に重要なレポートがないことを確認する必要があります。
手順2.説明されている2つのインデックスを使用するキャッシュからすべてのプランを選択します。
これを行うには、spGetPlanUsingIndexプロシージャ（メインクエリはJonathan Kehayiasの記事www.sqlskills.com/blogs/jonathanから取得されます）を使用します。これにより、使用統計がテーブルに追加されます。 1時間ごとに統計を収集する手順を開始するようにジョブが構成されています。
重要なポイント-すべての計画がキャッシュにあるわけではありません。たとえば、RECOMPILEヒントを持つプロシージャです。 そのようなヒントを使用する場合は、手順を確認し、手順を計画し、分析に含める必要があります。
指定されたインデックスを使用してキャッシュからプランを取得する
CREATE TABLE [dbo].[LogDataFileIndexUsage]( [PlanId] [int] IDENTITY(1,1) NOT NULL, [PlanDate] [datetime] NOT NULL CONSTRAINT [DF_LogDataFileIndexUsage_PlanDate] DEFAULT (getutcdate()), [DBname] [varchar](50) NULL, [SPname] [varchar](256) NULL, [ScanCount] [int] NULL, [SeekCount] [int] NULL, [UpdateCount] [int] NULL, [RefCount] [int] NULL, [UseCount] [int] NULL, [QueryPlan] [xml] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] SET ANSI_PADDING OFF ALTER TABLE [dbo].[LogDataFileIndexUsage] ADD [IndexName] [varchar](256) NULL PRIMARY KEY CLUSTERED ( [PlanId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE PROCEDURE [dbo].[spGetPlanUsingIndex] @indexName NVARCHAR(128) AS BEGIN SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- Make sure the name passed is appropriately quoted IF (LEFT(@IndexName, 1) <> '[' AND RIGHT(@IndexName, 1) <> ']') SET @IndexName = QUOTENAME(@IndexName); WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') INSERT INTO [dbo].[LogDataFileIndexUsage] ( DBName, SPname, ScanCount, SeekCount, UpdateCount, RefCount, UseCount, QueryPlan, IndexName ) SELECT DB_NAME(E.dbid) AS [DBName], object_name(E.objectid, dbid) AS [ObjectName], E.query_plan.value('count(//RelOp[@LogicalOp = ''Index Scan'' or @LogicalOp = ''Clustered Index Scan'']/*/Object[@Index=sql:variable("@IndexName")])','int') AS [ScanCount], E.query_plan.value('count(//RelOp[@LogicalOp = ''Index Seek'' or @LogicalOp = ''Clustered Index Seek'']/*/Object[@Index=sql:variable("@IndexName")])','int') AS [SeekCount], E.query_plan.value('count(//Update/Object[@Index=sql:variable("@IndexName")])','int') AS [UpdateCount], P.refcounts AS [RefCounts], P.usecounts AS [UseCounts], E.query_plan AS [QueryPlan], @IndexName FROM sys.dm_exec_cached_plans P CROSS APPLY sys.dm_exec_query_plan(P.plan_handle) E WHERE E.query_plan.exist('//*[@Index=sql:variable("@IndexName")]') = 1 OPTION(MAXDOP 1, RECOMPILE); END GO
ステップ3.収集されたデータの分析
この場合、分析により、両方のインデックスが同じプロシージャで使用されていることがわかりました。つまり、プロシージャが使用し、同じクエリに対して1つのインデックスが別のインデックスになります。
これらのインデックスを使用する合計24のプロシージャがあります。 各手順について、データがフィルタリングされるフィールド、JOINが作成されるフィールド、およびSELECTで指定されるフィールドによって分析されました。
これはすべて、Excelテーブルで手動で行われました。 これで、xmlプランから選択したクエリを作成することで、手作業を大幅に回避できることがわかりました。 選択のためのフィールド：述語、述語、およびテーブルから選択されるフィールドを探します。
ステップ4.新しいインデックスの形成
計画でのインデックスの使用の分析で得られたデータに基づいて、新しいインデックスが形成されます。
CREATE NONCLUSTERED INDEX [IX_ClientFile_StorageId_FolderId_FileStatus] ON [dbo].[ClientFile] ([StorageId], [FolderId], [FileStatus]);
ステップ5.テスト
プロジェクトで変更を行う場合、負荷テストは必ず行われます。 新しいインデックスを使用した負荷テストでは、キールックアップを削除するには、インデックスに名前フィールドを追加する必要があることが示されました。 WHERE句で使用されるため、フィールドが追加されます。
また、テスト後、欠落しているインデックスの情報がチェックされます。
インデックスがありません
SELECT mig.index_group_handle, mid.index_handle, CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) ) AS improvement_measure, 'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) + ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns,'') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') + ' with (online=on)' AS create_index_statement, migs.*, mid.*, mid.database_id, mid.[object_id] FROM sys.dm_db_missing_index_groups mig INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10 --AND database_id = 12 AND mid.statement like ('%[ClientFile]') ORDER BY convert(varchar(10), last_user_seek, 120) desc, migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) /*last_user_seek*/ DESC
最終インデックス
CREATE NONCLUSTERED INDEX [IX_ClientFile_StorageId_FolderId_FileStatus_FileName] ON [dbo].[ClientFile] ([StorageId], [FolderId], [FileStatus], [FileName]);
ステップ6.変更を適用する
テーブルは7億行なので、新しいテーブルを作成してそこにデータを移行することにより、新しいインデックスが適用されました。
各サーバーで最大45％のディスク容量を節約します。 サーバーは多くの場合、クラスター化インデックスを使用します。これにより、プラン内のキー検索の回数が削減されます。