演習 - アプリケーションのパフォーマンスを最適化する

• 10 分

この演習では、新しいパフォーマンス シナリオを観察し、アプリケーションとクエリを最適化することによってそれを解決します。

Azure SQL によってアプリケーション パフォーマンスを最適化する

場合により、既存のアプリケーションと SQL クエリ ワークロードを Azure に移行すると、クエリの最適化とチューニングの機会が明らかになることがあります。

AdventureWorks の注文の Web サイトへの新しい拡張機能をサポートして、顧客からの評価システムを提供するために、あなたは大量の同時 INSERT アクティビティ用の新しいテーブルを追加する必要があります。 データベースとトランザクション ログ用にローカル SSD ドライブを備えた SQL Server 2022 の開発用コンピューターで、SQL クエリ ワークロードをテストしました。

汎用レベル (8 仮想コア) を使用して、テストを Azure SQL Database に移動すると、INSERT ワークロードが遅くなります。 新しいワークロードをサポートするためにサービス目標またはレベルを変更する必要があるでしょうか? または、アプリケーションを調査する必要があるでしょうか?

この演習のすべてのスクリプトは、クローンした GitHub リポジトリまたはダウンロードした ZIP ファイルの 04-Performance\tuning_applications フォルダーにあります。

アプリケーションの新しいテーブルを作成する

オブジェクト エクスプローラーで AdventureWorks データベースを選択します。 [ファイル]>[開く]>[ファイル] を使って order_rating_ddl.sql スクリプトを開き、AdventureWorks データベースにテーブルを作成します。 クエリ エディター ウィンドウは、次のテキストのようになるはずです。

DROP TABLE IF EXISTS SalesLT.OrderRating;
GO
CREATE TABLE SalesLT.OrderRating
(OrderRatingID int identity not null,
SalesOrderID int not null,
OrderRatingDT datetime not null,
OrderRating int not null,
OrderRatingComments char(500) not null);
GO

[実行] を選んでスクリプトを実行します。

クエリを読み込んでクエリの実行を監視する

ここで、動的管理ビュー (DMV) の T-SQL クエリをいくつか読み込んで、アクティブなクエリ、待機、および I/O のクエリ パフォーマンスを観察します。 AdventureWorks データベースのコンテキストでこれらのすべてのクエリを読み込みます。

1. オブジェクト エクスプローラーで AdventureWorks データベースを選択します。 [ファイル]>[開く]>[ファイル] を使って sqlrequests.sql スクリプトを開き、アクティブな SQL クエリを確認します。 クエリ エディター ウィンドウは、次のテキストのようになるはずです。

   SELECT er.session_id, er.status, er.command, er.wait_type, er.last_wait_type, er.wait_resource, er.wait_time
   FROM sys.dm_exec_requests er
   INNER JOIN sys.dm_exec_sessions es
   ON er.session_id = es.session_id
   AND es.is_user_process = 1;
   

2. オブジェクト エクスプローラーで AdventureWorks データベースを選択します。 [ファイル]>[開く]>[ファイル] を使って top_waits.sql スクリプトを開き、回数が多い待機の種類を調べます。 クエリ エディター ウィンドウは、次のテキストのようになるはずです。

   SELECT * FROM sys.dm_os_wait_stats
   ORDER BY waiting_tasks_count DESC;
   

3. オブジェクト エクスプローラーで AdventureWorks データベースを選択します。 [ファイル]>[開く]>[ファイル] を使って tlog_io.sql スクリプトを開き、トランザクション ログの書き込みの待ち時間を観察します。 クエリ エディター ウィンドウは、次のテキストのようになるはずです。

   SELECT io_stall_write_ms/num_of_writes as avg_tlog_io_write_ms, * 
   FROM sys.dm_io_virtual_file_stats
   (db_id('AdventureWorks'), 2);
   

ワークロード スクリプトの実行を準備する

order_rating_insert_single.cmd ワークロード スクリプトを開いて編集します。

• 最初の演習で、-S parameterのサーバー名として指定した unique_id を置き換えます。
• 最初の演習のデータベースのデプロイで -P parameterに指定したパスワードを置き換えます。
• 変更をファイルに保存します。

ワークロードの実行

1. PowerShell コマンド プロンプトで、このモジュール アクティビティのディレクトリに移動します。

   cd c:<base directory>\04-Performance\tuning_applications
   

2. 次のコマンドを使用してワークロードを実行します。

   .\order_rating_insert_single.cmd
   

   このスクリプトでは、ostress.exe プログラムを使用して、次の T-SQL ステートメント (スクリプト order_rating_insert_single.sql 内) を実行することで、25 人の同時ユーザーを実行します。

   DECLARE @x int;
   SET @x = 0;
   WHILE (@x < 500)
   BEGIN
   SET @x = @x + 1;
   INSERT INTO SalesLT.OrderRating
   (SalesOrderID, OrderRatingDT, OrderRating, OrderRatingComments)
   VALUES (@x, getdate(), 5, 'This was a great order');
   END
   

   このスクリプトからわかるように、Web サイトから取得される実際のデータを正確に描写したものではありません。 ですが、データベースに取り込まれる多くの注文評価をシミュレートするものです。

DMV とワークロードのパフォーマンスを監視する

ここで、SQL Server Management Studio (SSMS) で以前に読み込んだクエリを実行し、パフォーマンスを観察します。 sqlrequests.sql、top_waits.sql、tlog_io.sqlのクエリを実行します。

これらのクエリを使用して、次のことを観察できます。

• 多くの要求は常に、値が 0 より大きい WRITELOG の wait_type になります。
• WRITELOG 待機の種類は、回数が最も多い待機の種類の 1 つです。
• トランザクション ログへの平均書き込み時間 (tlog_io.sql 結果セットの avg_tlog_io_write_ms 列) は、約 2 ミリ秒です。

SSD ドライブを搭載した SQL Server 2022 インスタンスでのこのワークロードの実行期間は、約 10 から 12 秒です。 Gen5 v8 コアを使用した Azure SQL Database の合計実行時間は約 25 秒です。

待ち時間が長い WRITELOG 待機の種類は、トランザクション ログへのフラッシュの待ち時間を示しています。 書き込みごとの 2 ミリ秒の待機時間は長いようには見えませんが、ローカル SSD ドライブでは、これらの待機が 1 ミリ秒未満になることがあります。

解決策を決定する

問題は、ログ書き込みアクティビティの割合が高いことではありません。 Azure portal と sys.dm_db_resource_stats では、20 から 25% を超える数値は示されません (これらのクエリを実行する必要はありません)。 問題は、IOPS の制限でもありません。 問題は、このアプリケーションのワークロードでは、トランザクション ログ書き込みの待機時間が短いことが重視され、汎用レベルがこの種の待機時間要件に対応して設計されていないことにあります。 Azure SQL Database の予想される I/O 待ち時間は、5 から 7 ミリ秒です。

order_rating_insert_single.sql ワークロード T-SQL スクリプトを調べます。 各 INSERT は 1 回のトランザクション コミットであり、トランザクション ログのフラッシュが必要です。

挿入ごとに 1 回のコミットは効率的でありませんが、ローカル SSD では、各コミットがきわめて高速であるため、アプリケーションは影響を受けませんでした。 Business Critical 価格レベル (サービス目標または SKU) では、待機時間が短いローカル SSD ドライブが提供されます。 アプリケーションの最適化が行われる可能性があるため、ワークロードはトランザクション ログの I/O 待ち時間にそれほど影響を受けません。

INSERT の反復を BEGIN TRAN/COMMIT TRAN でラップするように、ワークロードの T-SQL バッチを変更できます。

変更した、より効率的なワークロードを実行する

スクリプトを編集して実行し、I/O パフォーマンスの効率向上を確認します。 変更されたワークロードは、order_rating_insert.sql スクリプトで確認できます。

1. 正しいサーバー名とパスワードを使うように order_rating_insert.cmd を編集して、ワークロード スクリプトを準備します。

2. 前のワークロード スクリプトの実行方法と同じように、order_rating_insert.cmd スクリプトを使って、変更したワークロードを実行します。

新しい結果を確認する

1. SSMS で sqlrequests.sql の T-SQL スクリプトの結果を確認します。 WRITELOG の待機時間が大幅に短縮され、これら待機に関する全体の待機時間も短縮されたことに注目してください。

   前回の実行と比較して、ワークロードの実行時間が大幅に短縮されました。 これは、Azure の内外で実行される SQL クエリ用にアプリケーションをチューニングする例です。

   Note

   このワークロードは、リダイレクト接続の種類を使って、Azure SQL Database のインスタンスに対してより速く実行できます。 この演習で行ったデプロイでは既定の接続の種類が使われ、Azure の外部で接続しているため、それはプロキシの種類になります。 リダイレクトを使用すると、クライアントからサーバーへの必要なラウンド トリップを考慮して、このようにワークロードを大幅に高速化できます。

2. ワークロードの実行時間について観察します。 ワークロードが高速に実行されるため、このアクティビティで以前に使用したクエリから診断データを監視するのが困難な場合があります。

   "バッチ処理" の概念は、Azure SQL に接続されているアプリケーションを含むほとんどのアプリケーションで役立ちます。

次のユニットでは、Azure SQL でのインテリジェント パフォーマンスについて学習します。