SQL Database で低パフォーマンスクエリの問題領域を分離する

推定所要時間:30 分

あなたは、ユーザーが AdventureWorks2017 データベースのクエリを実行するときに現在発生しているパフォーマンスの問題に対処するために、シニアデータベース管理者として採用されました。あなたの仕事は、このモジュールで学習した手法を使用して、クエリのパフォーマンスの問題を明らかにし、解決することです。

最適ではないパフォーマンスのクエリを実行し、クエリプランを調べて、データベース内で改良を行います。

📝 これらの演習では、T-SQL コードをコピーして貼り付けるように求められます。コードを実行する前に、コードが正しくコピーされていることを確認してください。

環境をセットアップします

ラボ仮想マシンが提供され、事前に構成されている場合は、C:\LabFiles フォルダーにラボファイルが用意されているはずです。 少し時間をとって確認してください。ファイルが既に存在存在している場合には、このセクションをスキップしてください。ただし、独自のマシンを使用している場合、またはラボファイルが見つからない場合は、 GitHub からそれらを複製して続行する必要があります。

ラボ仮想マシン、または提供されていない場合はローカルコンピューターから、Visual Studio Code のセッションを起動します。
コマンドパレット (Ctrl+Shift+P) を開き、「Git Clone」と入力します。 [Git: Clone] オプションを選択します。
[Repository URL] フィールドに次の URL を貼り付け、Enter キーを押します。
```
 https://github.com/MicrosoftLearning/dp-300-database-administrator.git
```
リポジトリを、ラボ仮想マシン、または提供されていない場合はローカルコンピューターの [C:\LabFiles] フォルダーに保存してください（フォルダーが存在しない場合は作成します）。

データベースを復元する

既に AdventureWorks2017 データベースを復元済みの場合は、このセクションをスキップしてもかまいません。

ラボ仮想マシン、または提供されていない場合はローカルコンピューターから、SQL Server Management Studio (SSMS) のセッションを起動します。
SSMS が開くと、既定で [サーバーへ接続] ダイアログが表示されます。既定のインスタンスを選択し、[接続] を選択します。場合によっては、 [Trust Server Certificate] (サーバー証明書を信頼する) チェックボックスをオンにする必要があります。

📝 独自の SQL Server インスタンスを使用している場合は、適切なサーバーインスタンス名と資格情報を使用して接続する必要があることに注意してください。
Databases フォルダーを選択し、[新しいクエリ] を選択します。
次の T-SQL をコピーして、新しいクエリウィンドウに貼り付けます。クエリを実行してデータベースを復元します。
```
 RESTORE DATABASE AdventureWorks2017
 FROM DISK = 'C:\LabFiles\dp-300-database-administrator\Allfiles\Labs\Shared\AdventureWorks2017.bak'
 WITH RECOVERY,
       MOVE 'AdventureWorks2017' 
         TO 'C:\LabFiles\AdventureWorks2017.mdf',
       MOVE 'AdventureWorks2017_log'
         TO 'C:\LabFiles\AdventureWorks2017_log.ldf';
```
📝 C:\LabFiles という名前のフォルダーが必要です。このフォルダーがない場合は、作成するか、データベースとバックアップファイルの別の場所を指定します。
[メッセージ] タブに、データベースが正常に復元されたことを示すメッセージが表示されます。

実際の実行プランを生成する

SQL Server Management Studio で実行プランを生成するには、いくつかの方法があります。

[新しいクエリ] を選択します。次の T-SQL コードをコピーして、クエリウィンドウに貼り付けます。 [Execute] を選択してこのクエリを実行します。

注: クエリの実行プランは、個別のタブにグラフィカルに表示するのではなく、SHOWPLAN_ALL を使用して、テキストバージョンを結果ペインに表示します。
```
 USE AdventureWorks2017;

 GO

 SET SHOWPLAN_ALL ON;

 GO

 SELECT BusinessEntityID
 FROM HumanResources.Employee
 WHERE NationalIDNumber = '14417807';

 GO

 SET SHOWPLAN_ALL OFF;

 GO
```
結果ペインでは、SELECT ステートメントの実際のクエリ結果ではなく、実行プランのテキストバージョンが表示されます。
少し時間を取って、StmtText 列の 2 行目のテキストを調べます。
```
 |--Index Seek(OBJECT:([AdventureWorks2017].[HumanResources].[Employee].[AK_Employee_NationalIDNumber]), SEEK:([AdventureWorks2017].[HumanResources].[Employee].[NationalIDNumber]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)) ORDERED FORWARD)
```
上記のテキストは、この実行プランでは AK_Employee_NationalIDNumber キーに対して Index Seek が使用されていることを説明しています。また、実行プランで CONVERT_IMPLICIT 手順を行う必要があったことも示しています。

クエリオプティマイザーは、必要なレコードをフェッチするために適切なインデックスを見つけることができました。

最適でないクエリプランを解決する

次のコードをコピーして、新しいクエリウィンドウに貼り付けます。

[実行] ボタンの右側にある [実際の実行プランを含める] アイコンを選択するか、 Ctrl + M キーを押します。 [Execute] を選択するか、F5 キーを押して、クエリを実行します。実行プランと、[メッセージ] タブの論理読み取り数を記録しておきます。
```
 SET STATISTICS IO, TIME ON;

 SELECT [SalesOrderID] ,[CarrierTrackingNumber] ,[OrderQty] ,[ProductID], [UnitPrice] ,[ModifiedDate]
 FROM [AdventureWorks2017].[Sales].[SalesOrderDetail]
 WHERE [ModifiedDate] > '2012/01/01' AND [ProductID] = 772;
```
実行プランを確認すると、Key Lookupがあることがわかります。そのアイコンをマウスでポイントすると、クエリによって取得された行ごとにそれが実行されることがプロパティで示されます。実行プランでKey Lookup操作が実行されていることを確認できます。

[出力リスト] セクションの列をメモします。このクエリをどのように改善しますか?

キー参照を削除するために変更する必要があるインデックスを特定するには、その上のインデックスシークを調べる必要があります。インデックスシークオペレーターをマウスでポイントすると、オペレーターのプロパティが表示されます。
クエリで返される、または検索されるすべてのフィールドを含む対象とするインデックスを追加することで、キー参照を削除できます。この例のインデックスでは、ProductID 列のみが使用されています。インデックスの現在の定義を次に示します。ProductID 列は、クエリに必要な他の列を取得するために Key Lookup を強制する唯一のキー列であることに注意してください。
```
 CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail]
 ([ProductID] ASC)
```
含まれる列として出力リストのフィールドをインデックスに追加すると、キー参照が削除されます。インデックスは既に存在するので、インデックスを削除して作成し直すか、列を追加するために DROP_EXISTING=ON を設定する必要があります。 ProductID 列は既にインデックスの一部であり、含まれる列として追加する必要はないことに注意してください。 ModifiedDate を追加することにより、インデックスのパフォーマンスをさらに向上させることができます。 [新しいクエリ] ウィンドウを開き、次のスクリプトを実行してインデックスを削除し再構築します。
```
 CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID]
 ON [Sales].[SalesOrderDetail] ([ProductID],[ModifiedDate])
 INCLUDE ([CarrierTrackingNumber],[OrderQty],[UnitPrice])
 WITH (DROP_EXISTING = on);
 GO
```
ステップ 1 からクエリを再実行します。論理読み取り数の変化と実行プランの変更を記録しておきます。プランで使用する必要があるのは、作成した非クラスター化インデックスのみになりました。

📝 実行プランを確認すると、Key Lookup がなくなったので、非クラスター化インデックスのみを使用しています。

クエリストアを使用して回帰を検出して処理する

次に、ワークロードを実行して、クエリストアのクエリ統計を生成し、リソースを消費するクエリの上位レポートを調べて低パフォーマンスを特定し、より優れた実行プランを強制する方法を確認します。

[New Query] を選択します。次の T-SQL コードをコピーして、クエリウィンドウに貼り付けます。 [Execute] を選択してこのクエリを実行します。

このスクリプトにより、クエリストア機能が AdventureWorks2017 データベースに対して有効になり、データベースの互換レベルが 100 に設定されます。
```
 USE [master];

 GO

 ALTER DATABASE [AdventureWorks2017] SET QUERY_STORE = ON;

 GO

 ALTER DATABASE [AdventureWorks2017] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

 GO

 ALTER DATABASE [AdventureWorks2017] SET COMPATIBILITY_LEVEL = 100;

 GO
```
互換レベルの変更は、データベースの時間を戻すことに似ています。これにより、SQL Server で使用できる機能が、SQL Server 2008 で使用できるものに制限されます。
SQL Server Management Studio で [ファイル] > [開く] > [ファイル] を選択します。
C:\LabFiles\dp-300-database-administrator\Allfiles\Labs\10\CreateRandomWorkloadGenerator.sql ファイルに移動します。
SQL Server Management Studio で開いたら、[Execute] を選択してクエリを実行します。
新しいクエリエディターで、 C:\LabFiles\dp-300-database-administrator\Allfiles\Labs\10\ExecuteRandomWorkload.sql ファイルを開き、[実行] を選択してクエリを実行します。
実行が完了したら、スクリプトをもう一度実行して、サーバーにさらに負荷を加えます。このクエリのクエリタブは開いたままにします。
次のコードをコピーして新しいクエリウィンドウに貼り付け、[実行] を選択して実行します。

このスクリプトにより、データベース互換モードが SQL Server 2022 (160) に変更されます。これで、SQL Server 2008 以降のすべての機能と機能強化をデータベースで使用できるようになりました。
```
 USE [master];

 GO

 ALTER DATABASE [AdventureWorks2017] SET COMPATIBILITY_LEVEL = 160;

 GO
```
ExecuteRandomWorkload.sql ファイルからクエリタブに戻り、もう一度実行します。

リソースを消費する上位のクエリのレポートを調べる

クエリストアノードを表示するには、SQL Server Management Studio で AdventureWorks2017 データベースを更新する必要があります。データベース名を右クリックし、 [更新] を選択します。データベースの下に [クエリストア] ノードが表示されます。
[クエリストア] ノードを展開すると、使用可能なすべてのレポートが表示されます。 [リソースを消費するクエリの上位] レポートを選択します。
レポートが開いたら、メニュードロップダウンを選択し、レポートの右上隅にある [構成] を選択します。
構成画面で、クエリプランの最小数のフィルターを 2 に変更します。 [OK] をクリックします。
レポートの左上にある棒グラフの左端にある棒を選択して、期間が最も長いクエリを選択します。

これにより、クエリストア内の最長クエリのクエリとプランの概要が表示されます。レポートの右上隅にあるプランの概要グラフと、レポートの下部にあるクエリプランを確認します。

より適切な実行プランを強制する

次に示すように、レポートのプラン概要部分に移動します。期間が大きく異なる 2 つの実行プランがあることに注意してください。
レポートの右上のウィンドウで、期間が最も短いプラン ID を選択します (これは、グラフの Y 軸の下側にある位置で示されます)。プラン概要グラフの横にあるプラン ID を選択します。
概要グラフの [プランの強制] を選択します。確認ウィンドウが表示されたら、 [はい] を選択します。

プランが強制されたら、 [強制されたプラン] は淡色表示され、プランの概要ウィンドウのプランに、強制されたことを示すチェックマークが付けられます。

クエリオプティマイザーによって使用するように選択された実行プランが、適切ではない場合があります。このような状況が発生した場合、パフォーマンスがもっとよいプランがわかっているときは、それを使用するように SQL Server に強制することができます。

クエリヒントを使用してパフォーマンスに影響を与えます

次に、ワークロードを実行し、パラメーターを使用するようにクエリを変更し、クエリヒントをクエリに適用して、再実行します。

演習を続ける前に、[ウィンドウ] メニューを選択して現在のクエリウィンドウをすべて閉じ、[すべてのドキュメントを閉じる] を選択します。ポップアップで [いいえ] を選択します。

[New Query] を選択し、クエリを実行する前に [実際の実行プランを含める] アイコンを選択するか、CTRL + M キーを使用します。
次のクエリを実行します。実行プランでインデックスシークオペレーターが示されていることに注意してください。
```
 USE AdventureWorks2017;

 GO

 SELECT SalesOrderId, OrderDate
 FROM Sales.SalesOrderHeader
 WHERE SalesPersonID=288;
```
新しいクエリウィンドウで、次のクエリを実行します。両方の実行プランを比較します。
```
 USE AdventureWorks2017;
 GO

 SELECT SalesOrderId, OrderDate
 FROM Sales.SalesOrderHeader
 WHERE SalesPersonID=277;
```
今回の変更は、SalesPersonID の値が 277 に設定されていることだけです。実行プランのクラスター化インデックススキャン操作に注意してください。

ご覧のとおり、WHERE 句の値が異なるため、クエリオプティマイザーでは、インデックス統計に基づいて、別の実行プランが選択されています。

変更したのは SalesPersonID の値のみであるのに、なぜ異なるプランが存在するのでしょうか?

このクエリでは WHERE 句で定数が使用されているため、オプティマイザーでは、これらのクエリはそれぞれ一意と見なされ、毎回異なる実行プランが生成されます。

変数とクエリヒントを使用するようにクエリを変更する

SalesPersonID に変数値を使用するようにクエリを変更します。
T-SQL の DECLARE ステートメントを使用して @SalesPersonID を宣言し、WHERE 句の値をハードコーディングするのではなく、値を渡すことができるようにします。暗黙的な変換を回避するには、変数のデータ型をターゲットテーブルの列のデータ型と一致させる必要があります。実際のクエリプランを有効にしてクエリを実行します。
```
 USE AdventureWorks2017;

 GO

 SET STATISTICS IO, TIME ON;

 DECLARE @SalesPersonID INT;

 SELECT @SalesPersonID = 288;

 SELECT SalesOrderId, OrderDate
 FROM Sales.SalesOrderHeader
 WHERE SalesPersonID= @SalesPersonID;
```
実行プランを調べると、インデックススキャンを使用して結果を取得していることがわかります。クエリオプティマイザーは、実行時までローカル変数の値がわからないので、適切な最適化を行うことができませんでした。
クエリヒントを提供することにより、クエリオプティマイザーでより適切な選択が行われるようにすることができます。 OPTION (RECOMPILE) を使用して上記のクエリを再度実行します。
```
 USE AdventureWorks2017

 GO

 SET STATISTICS IO, TIME ON;

 DECLARE @SalesPersonID INT;

 SELECT @SalesPersonID = 288;

 SELECT SalesOrderId, OrderDate
 FROM Sales.SalesOrderHeader
 WHERE SalesPersonID= @SalesPersonID
 OPTION (RECOMPILE);
```
クエリオプティマイザーで、より効率的な実行プランを選択できるようになったことに注意してください。 RECOMPILE オプションを指定すると、クエリコンパイラによって変数がその値に置き換えられます。

[メッセージ] タブで統計を比較すると、クエリヒントを使用しないクエリの方が、論理読み取り数が 68% 多い (689 対 409) ことがわかります。

クリーンアップ

データベースまたはラボファイルを他の目的で使用していない場合は、このラボで作成したオブジェクトをクリーンアップできます。

C:\LabFiles フォルダーを削除する

ラボ仮想マシン、または提供されていない場合はローカルコンピューターから、エクスプローラーを開きます。
C:\ に移動します。
C:\LabFiles フォルダーを削除します。

AdventureWorks2017 データベースを削除する

ラボ仮想マシン、または提供されていない場合はローカルコンピューターから、SQL Server Management Studio (SSMS) のセッションを起動します。
SSMS が開くと、既定で [サーバーへ接続] ダイアログが表示されます。既定のインスタンスを選択し、[接続] を選択します。場合によっては、 [Trust Server Certificate] (サーバー証明書を信頼する) チェックボックスをオンにする必要があります。
オブジェクトエクスプローラーで、[データベース] フォルダーを展開します。
AdventureWorks2017 データベースを右クリックし、[削除] を選択します。
[オブジェクトの削除] ダイアログで、[既存の接続を閉じる] チェックボックスをオンにします。
[OK] を選択します。

このラボは以上で完了です。

この演習では、クエリの問題を特定する方法と、それを修正してクエリプランを改善する方法を学習しました。

SQL Database で低パフォーマンス クエリの問題領域を分離する