SQL Serverのパフォーマンスチューニング：非効率なクエリの特定とインデックスの最適化ガイド

SQL Serverのパフォーマンスが低下すると、データベースを利用するアプリケーション全体に悪影響を及ぼします。その多くは、非効率なクエリや不適切なインデックス設計が原因です。

本記事では、SQL Serverのパフォーマンスを向上させるために、非効率なクエリを特定し、インデックスを見直し、最適化する方法を詳しく解説します。

非効率なクエリの特定
1. SQL Server Management Studio (SSMS)のクエリ実行プラン
2. 動的管理ビュー (DMV) を使用した非効率なクエリの特定
インデックスの見直し
演習問題
解答例
おわりに

非効率なクエリの特定

SQL Serverで非効率なクエリを見つけるためには、以下のツールと手法を活用します。

SQL Server Management Studio (SSMS)のクエリ実行プラン

クエリ実行プランは、クエリがどのように実行されるかを視覚的に示します。非効率な箇所を特定するのに役立ちます。

実行プランの確認手順

SSMSでクエリを実行する際に、Ctrl + Mを押して「実行プランを表示」します。
クエリを実行すると、実行結果とともに実行プランが表示されます。
実行プランに「警告」アイコン（黄色の三角形）が表示されている箇所に注意します。

動的管理ビュー (DMV) を使用した非効率なクエリの特定

SQL Serverには、クエリパフォーマンスを監視するためのDMVが用意されています。特に以下のクエリが役立ちます。

SELECT TOP 10 
    qs.total_logical_reads AS LogicalReads,
    qs.total_elapsed_time / qs.execution_count AS AvgExecutionTime,
    qs.execution_count AS ExecutionCount,
    st.text AS QueryText
FROM 
    sys.dm_exec_query_stats qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY 
    LogicalReads DESC;

このクエリは、ロジカルリードが多いクエリ（=I/O負荷が高いクエリ）を特定します。

インデックスの見直し

非効率なクエリを特定したら、次にインデックスの見直しを行います。インデックスが適切に設計されていない場合、パフォーマンス低下の原因になります。

必要なインデックスを特定する

SQL Serverでは、欠けているインデックスを特定するためのDMVが用意されています。以下のクエリを使用してインデックスが不足しているテーブルを確認します。

SELECT 
    dm_mid.database_id,
    dm_migs.user_seeks AS UserSeeks,
    OBJECT_NAME(dm_mid.object_id, dm_mid.database_id) AS TableName,
    dm_mid.equality_columns,
    dm_mid.inequality_columns,
    dm_mid.included_columns,
    'CREATE INDEX IX_' + OBJECT_NAME(dm_mid.object_id, dm_mid.database_id) + '_' +
    REPLACE(REPLACE(equality_columns, ',', '_'), ' ', '') +
    ' ON ' + OBJECT_NAME(dm_mid.object_id, dm_mid.database_id) +
    '(' + equality_columns +
    CASE 
        WHEN inequality_columns IS NOT NULL THEN ',' + inequality_columns ELSE '' END +
    ')' +
    CASE 
        WHEN included_columns IS NOT NULL THEN ' INCLUDE (' + included_columns + ')' ELSE '' END AS CreateIndexStatement
FROM 
    sys.dm_db_missing_index_details dm_mid
JOIN 
    sys.dm_db_missing_index_groups dm_mig ON dm_mid.index_handle = dm_mig.index_handle
JOIN 
    sys.dm_db_missing_index_group_stats dm_migs ON dm_mig.index_group_handle = dm_migs.index_group_handle
ORDER BY 
    UserSeeks DESC;

このクエリを実行することで、不足しているインデックスを自動的に提案してくれます。

不要なインデックスの削除

過剰なインデックスはデータ挿入や更新のパフォーマンスを低下させます。以下のクエリで使用されていないインデックスを特定できます。

SELECT 
    OBJECT_NAME(s.[object_id]) AS TableName,
    i.name AS IndexName,
    i.index_id AS IndexID,
    s.user_seeks, 
    s.user_scans, 
    s.user_lookups, 
    s.user_updates
FROM 
    sys.dm_db_index_usage_stats s
JOIN 
    sys.indexes i ON i.index_id = s.index_id AND s.[object_id] = i.[object_id]
WHERE 
    OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
    AND s.database_id = DB_ID()
    AND s.user_seeks = 0 
    AND s.user_scans = 0
    AND s.user_lookups = 0;

結果を確認して、使用されていないインデックスを削除してください。

インデックスの再構築と再編成

インデックスの断片化は、パフォーマンス低下の原因になります。インデックスの断片化率を確認し、再構築や再編成を行いましょう。

-- 断片化率の確認
SELECT 
    dbschemas.[name] AS SchemaName,
    dbtables.[name] AS TableName,
    dbindexes.[name] AS IndexName,
    indexstats.avg_fragmentation_in_percent AS FragmentationPercent
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') indexstats
JOIN 
    sys.objects dbtables ON dbtables.[object_id] = indexstats.[object_id]
JOIN 
    sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
JOIN 
    sys.indexes dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
    AND indexstats.index_id = dbindexes.index_id
WHERE 
    indexstats.avg_fragmentation_in_percent > 10; -- 断片化率が10%以上のインデックス

断片化率が高いインデックスは、以下のコマンドで最適化します。

再構築

ALTER INDEX [インデックス名] ON [テーブル名] REBUILD;

再編成

ALTER INDEX [インデックス名] ON [テーブル名] REORGANIZE;

演習問題

問題1: 非効率なクエリの特定

以下のクエリを実行し、クエリパフォーマンスを確認してください。

SELECT * FROM Orders WHERE OrderDate > '2023-01-01';

クエリ実行プランを確認し、何が問題になっているか説明してください。
パフォーマンスを改善するためのインデックスを提案してください。

問題2: インデックスの断片化率の確認

以下の手順を実行してください。

指定されたデータベースの断片化率を確認するクエリを実行してください。
断片化率が10%以上のインデックスを再構築してください。

解答例

解答1: 非効率なクエリの特定

クエリ実行プランでは、「テーブルスキャン」が発生していることがわかります。これは、適切なインデックスがないために発生しています。
以下のインデックスを作成することで、パフォーマンスを改善できます。

CREATE INDEX IX_Orders_OrderDate ON Orders(OrderDate);

解答2: インデックスの断片化率の確認

断片化率の確認クエリを実行します。

SELECT 
    dbindexes.[name] AS IndexName,
    indexstats.avg_fragmentation_in_percent AS FragmentationPercent
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') indexstats
JOIN 
    sys.indexes dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
    AND indexstats.index_id = dbindexes.index_id
WHERE 
    indexstats.avg_fragmentation_in_percent > 10;

断片化率が高いインデックスを再構築します。

ALTER INDEX [再構築対象のインデックス名] ON [テーブル名] REBUILD;

おわりに

SQL Serverのパフォーマンスチューニングは、非効率なクエリの特定から始まり、インデックスの見直しや再構築まで多岐にわたります。

本記事を参考に、効率的なクエリ実行を目指してみてください。