演習 - パフォーマンスの監視とトラブルシューティング

  • 10 分

この演習では、使い慣れたものと新しいもの両方のツールと機能を使って、Azure SQL のパフォーマンスの問題の監視とトラブルシューティングを行う方法について学習します。

セットアップ:スクリプトを使用して Azure SQL Database をデプロイする

右側のターミナル セッション (Azure Cloud Shell) では、ブラウザーを使用して Azure と対話できます。 この演習では、スクリプトを実行して環境 (AdventureWorks データベースを含む Azure SQL Database のインスタンス) を作成します。 (小さくて簡単なサンプルの AdventureWorksLT データベースを使いますが、混乱しないように AdventureWorks と呼びます)。このスクリプトでは、パスワードとローカル IP アドレスを入力するように求められます。これにより、ご利用のデバイスでデータベースに接続できるようになります。

このスクリプトの完了には 3 分から 5 分かかります。 パスワード、一意の ID、リージョンを必ず書き留めてください。 これらは再表示されません。

  1. まず、ローカル IP アドレスを取得します。 どの VPN サービスからも切断されていることを確実にし、デバイスでローカル PowerShell ターミナルを開きます。 次のコマンドを実行し、結果として得られる IP アドレスを書き留めます。

    (Invoke-WebRequest -Uri "https://ipinfo.io/ip").Content

  2. 右側の Azure Cloud Shell で、次のコードを入力します。プロンプトが表示されたら、前の手順で取得した複雑なパスワードとローカル パブリック IP アドレスを入力します。 Enter キーを押して、スクリプトの最後の行を実行します。

    $adminSqlLogin = "cloudadmin"
$password = Read-Host "Your username is 'cloudadmin'. Please enter a password for your Azure SQL Database server that meets the password requirements"
# Prompt for local ip address
$ipAddress = Read-Host "Disconnect your VPN, open PowerShell on your machine and run '(Invoke-WebRequest -Uri "https://ipinfo.io/ip").Content'. Please enter the value (include periods) next to 'Address':"
# Get resource group and location and random string
$resourceGroup = Get-AzResourceGroup | Where ResourceGroupName -like "<rgn>Sandbox resource group name</rgn>"
$resourceGroupName = "<rgn>Sandbox resource group name</rgn>"
$uniqueID = Get-Random -Minimum 100000 -Maximum 1000000
$storageAccountName = "mslearnsa"+$uniqueID
$location = $resourceGroup.Location
$serverName = "aw-server$($uniqueID)"

  3. Azure Cloud Shell で次のスクリプトを実行します。 出力を保存します。この情報は、モジュール全体を通して必要になります。 コードに貼り付けたら、Enter キーを押します。その結果、コードの最後の行によって、必要とする出力が表示されます。

    Write-Host "Please note your unique ID for future exercises in this module:"  
Write-Host $uniqueID
Write-Host "Your resource group name is:"
Write-Host $resourceGroupName
Write-Host "Your resources were deployed in the following region:"
Write-Host $location
Write-Host "Your server name is:"
Write-Host $serverName

    ヒント

    出力を保存し、パスワード、一意の ID、サーバーを記録しておきます。 これらの項目はモジュールを通して必要になります。

  4. 次のスクリプトを実行して、Azure SQL Database のインスタンスと論理サーバーを AdventureWorks サンプルと共にデプロイします。 このスクリプトを実行すると、自分の IP アドレスがファイアウォール規則として追加され、Advanced Data Security が有効になり、このモジュールの残りの演習で使用するストレージ アカウントが作成されます。 このスクリプトは、完了するまで数分かかる場合があります。また、何回か一時停止します。 コマンド プロンプトを待ちます。

    # The logical server name has to be unique in the system
$serverName = "aw-server$($uniqueID)"
# The sample database name
$databaseName = "AdventureWorks"
# The storage account name has to be unique in the system
$storageAccountName = $("sql$($uniqueID)")
# Create a new server with a system wide unique server name
$server = New-AzSqlServer -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -Location $location `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
# Create a server firewall rule that allows access from the specified IP range and all Azure services
$serverFirewallRule = New-AzSqlServerFirewallRule `
    -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -FirewallRuleName "AllowedIPs" `
    -StartIpAddress $ipAddress -EndIpAddress $ipAddress 
$allowAzureIpsRule = New-AzSqlServerFirewallRule `
    -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -AllowAllAzureIPs
# Create a database
$database = New-AzSqlDatabase  -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -DatabaseName $databaseName `
    -SampleName "AdventureWorksLT" `
    -Edition "GeneralPurpose" -Vcore 2 -ComputeGeneration "Gen5"
# Enable Advanced Data Security
$advancedDataSecurity = Enable-AzSqlServerAdvancedDataSecurity `
    -ResourceGroupName $resourceGroupName `
    -ServerName $serverName
# Create a Storage Account
$storageAccount = New-AzStorageAccount -ResourceGroupName $resourceGroupName `
    -AccountName $storageAccountName `
    -Location $location `
    -Type "Standard_LRS"

  5. ローカル デバイス上で SQL Server Management Studio (SSMS) を開いて、ご利用の論理サーバーへの新しい接続を作成します。

  6. [サーバーに接続] ログイン ダイアログ ボックスで、次の情報を指定します。

    フィールド 値
    サーバーの種類 "データベース エンジン" (既定値)。
    サーバー名 Cloud Shell で返された $serverName と、URI の残りの部分。 たとえば、aw-server<unique ID>.database.windows.netです。
    認証 "SQL Server 認証" (既定値)
    ログイン cloudadmin この演習の手順 1 で割り当てた adminSqlLogin。
    Password この演習の手順 1 で指定したパスワード。
    パスワードを保存する checked

  7. [接続] を選択します。

    Screenshot of connection dialog for SQL Database in SSMS.

    Note

    ローカル構成 (VPN など) によっては、クライアントの IP アドレスが、デプロイの間に Azure portal で使用した IP アドレスと異なる場合があります。 その場合、次のメッセージが表示されます。"このクライアント IP アドレスではサーバーにアクセスできません。 アクセスできるようにするには、Azure アカウントにサインインし、新しいファイアウォール規則を作成します。" というポップアップ メッセージが表示されます。このメッセージが表示される場合は、サンドボックスに使っているアカウントを使ってサインインし、クライアント IP アドレスに対するファイアウォール規則を追加します。 SSMS のウィザードを使用して、これらの手順をすべて完了できます。

スクリプトを読み込んで編集することで演習を準備する

この演習のすべてのスクリプトは、クローンした GitHub リポジトリまたはダウンロードした ZIP ファイルの 04-Performance\monitor_and_scale フォルダーにあります。 スクリプトを読み込んで編集することで演習を準備しましょう。

  1. SSMS のオブジェクト エクスプローラーで [データベース] フォルダーを展開して、AdventureWorks データベースを選びます。

  2. [ファイル]>[開く]>[ファイル] を選んで、dmexecrequests.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;

  3. SSMS で同じ方法を使用して、dmdbresourcestats.sql スクリプトを読み込みます。 新しいクエリ エディター ウィンドウは、次のテキストのようになるはずです。

    SELECT * FROM sys.dm_db_resource_stats;

    この動的管理ビュー (DMV) では、Azure SQL Database に対してワークロードの全体的なリソースの使用率が追跡されます。 たとえば、CPU、I/O、メモリの追跡が行われます。

  4. sqlworkload.cmd スクリプトを開いて編集します (ostress.exe プログラムを使います)。

    • デプロイ スクリプトから保存した自分の unique_id を、サーバー名に代入します。
    • -P parameter は、Azure SQL Database サーバーへのサインインで使用したパスワードに置き換えます。
    • スクリプトへの変更を保存します。

ワークロードの実行

このタスクでは、T-SQL クエリでワークロードを実行し、同時ユーザーをシミュレートしたときのパフォーマンスを観察します。

  1. SSMS を使って topcustomersales.sql スクリプト ファイルを開き、クエリを観察します。 SSMS からはクエリを実行しません。 クエリ エディター ウィンドウは、次のテキストのようになるはずです。

    DECLARE @x int
DECLARE @y float
SET @x = 0;
WHILE (@x < 10000)
BEGIN
SELECT @y = sum(cast((soh.SubTotal*soh.TaxAmt*soh.TotalDue) as float))
FROM SalesLT.Customer c
INNER JOIN SalesLT.SalesOrderHeader soh
ON c.CustomerID = soh.CustomerID
INNER JOIN SalesLT.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
INNER JOIN SalesLT.Product p
ON p.ProductID = sod.ProductID
GROUP BY c.CompanyName
ORDER BY c.CompanyName;
SET @x = @x + 1;
END
GO

    これは小さなデータベースです。 最も売り上げの高い顧客順に並べ替えられた、顧客の一覧と彼らに関連付けられている販売情報を取得するクエリを実行しても、大きな結果セットが生成されることはないでしょう。 結果セットの列数を減らせばこのクエリを調整できますが、これらの列はこの演習のデモンストレーションのために必要なものです。

  2. PowerShell コマンド プロンプトから、次のコマンドを入力して、この演習のための正しいディレクトリに移動します。 <base directory> を、このモジュールのユーザー ID およびパスに置き換えます。

    cd <base directory>\04-Performance\monitor_and_scale

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

    .\sqlworkload.cmd

    このスクリプトでは、ワークロード クエリを 2 回実行する 10 人の同時ユーザーが使用されます。 スクリプト自体では 1 つのバッチが実行されますが、10,000 回ループすることに注目してください。 また、結果が変数に割り当てられたため、クライアントへのほとんどすべての結果セットのトラフィックが排除されます。 これは必要なことではありませんが、"純粋な" CPU ワークロードがサーバー上で完全に実行されるのを示すのに役立ちます。

    ヒント

    お使いの環境でこのワークロードの CPU 使用率の動作がわからない場合は、ユーザー数用の -n parameter と反復用の -r parameter を調整できます。

    コマンド プロンプトでの出力は、次の出力のようになります。

    [datetime] [ostress PID] Max threads setting: 10000
[datetime] [ostress PID] Arguments:
[datetime] [ostress PID] -S[server].database.windows.net
[datetime] [ostress PID] -isqlquery.sql
[datetime] [ostress PID] -U[user]
[datetime] [ostress PID] -dAdventureWorks
[datetime] [ostress PID] -P********
[datetime] [ostress PID] -n10
[datetime] [ostress PID] -r2
[datetime] [ostress PID] -q
[datetime] [ostress PID] Using language id (LCID): 1024 [English_United States.1252] for character formatting with NLS: 0x0006020F and Defined: 0x0006020F
[datetime] [ostress PID] Default driver: SQL Server Native Client 11.0
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_1.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_2.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_3.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_4.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_5.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_6.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_7.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_8.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_9.out]
[datetime] [ostress PID] Starting query execution...
[datetime] [ostress PID]  BETA: Custom CLR Expression support enabled.
[datetime] [ostress PID] Creating 10 thread(s) to process queries
[datetime] [ostress PID] Worker threads created, beginning execution...

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

前に読み込んだ DMV クエリを使用して、パフォーマンスを観察してみましょう。

  1. 以前に読み込んだクエリを SSMS で実行して dm_exec_requests (dmexecrequests.sql) を監視し、アクティブな要求を観察します。 このクエリを 5、6 回実行し、結果の一部を観察します。

    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;

    要求の多くは状態が RUNNABLE であり、last_wait_type は SOS_SCHEDULER_YIELD であることがわかるはずです。 多数の RUNNABLE 要求と多数の SOS_SCHEDULER_YIELD 待機は、アクティブなクエリ用の CPU リソースが不足している可能性を示すものの 1 つです。

    Note

    コマンドが SELECT で wait_type が XE_LIVE_TARGET_TVF であるアクティブな要求が、1 つ以上見つかる場合があります。 これらは Microsoft によって管理されているサービスによって実行されたクエリです。 これらは、拡張イベントを使用することでパフォーマンス分析情報のような機能で役立ちます。 Microsoft はこれらのセッションの詳細を公開していません。

    このクエリ エディター ウィンドウは開いたままにしておきます。 次の演習でもう一度実行します。

  2. SSMS で以前に読み込んだクエリを実行し、sys.dm_db_resource_stats (dmdbresourcestats.sql) を監視します。 クエリを実行して、この DMV の結果を 3、4 回表示します。

    SELECT * FROM sys.dm_db_resource_stats;

    この DMV では、15 秒ごとにデータベースのリソースの使用率のスナップショットが記録されます (1 時間保持されます)。 いくつかのスナップショットで、列 avg_cpu_percent が 100% に近いことがわかるはずです。 これは、ワークロードがデータベースの CPU リソースの制限に近づいている兆候です。

    オンプレミスの SQL Server 環境では、通常、オペレーティング システムに固有のツールを使って、CPU などの全体的なリソースの使用状況を追跡します。 たとえば Windows パフォーマンス モニターをこの目的のために使用できます。 オンプレミスの SQL Server または 2 つの CPU を搭載した仮想マシンの SQL Server でこの例を実行した場合、サーバーでの CPU 使用率が 100% に近くなります。

    Note

    別の DMV sys.resource_stats を Azure SQL Database サーバーの master データベースのコンテキストで実行して、サーバーに関連付けられているすべての Azure SQL Database データベースのリソース使用率を確認できます。 このビューは粒度が低いため、リソースの使用率が 5 分ごとに表示されます (14 日間保持されます)。

    このクエリ エディター ウィンドウは開いたままにしておきます。 次の演習でもう一度実行します。

  3. ワークロードを完了させて、その全体の期間を書き留めます。 ワークロードが完了すると、次の出力のような結果が表示され、コマンド プロンプトに戻ります。

    [datetime] [ostress PID] Total IO waits: 0, Total IO wait time: 0 (ms)
[datetime] [ostress PID] OSTRESS exiting normally, elapsed time: 00:01:22.637

    実行時間は異なる場合がありますが、通常は少なくとも 1 分から 3 分かかります。 この実行を完了させてください。 ワークロードが完了すると、コマンド プロンプトに戻ります。

詳細な分析のためにクエリ ストアを使用する

クエリ ストアは、クエリのパフォーマンスの実行を追跡する SQL Server の機能です。 パフォーマンス データは、ユーザー データベースに格納されます。 クエリ ストアは、SQL Server で作成されたデータベースに対して既定で有効にされませんが、Azure SQL Database (および Azure SQL Managed Instance) では既定でオンにされます。

クエリ ストアには、パフォーマンス データを表示するための一連のシステム カタログ ビューが付属しています。 SSMS では、これらのビューを使用してレポートが提供されます。

  1. SSMS のオブジェクト エクスプローラーを使用して、[クエリ ストア] フォルダーを開き、[リソースを消費するクエリの上位] のレポートを検索します。

    Screenshot of the Query Store.

  2. レポートを選択して、最も平均的なリソースを使用したクエリとそれらのクエリの実行の詳細を調べます。 この時点までのワークロードの実行に基づいて、レポートは次のイメージのように見えるはずです。

    Screenshot of the top query report.

    表示されるクエリは、顧客の売上のワークロードからの SQL クエリです。 このレポートには、合計実行時間が長いクエリ (メトリックは変更できます)、関連付けられているクエリ プランとランタイム統計、およびビジュアル マップ内の関連付けられているクエリ プランの 3 つのコンポーネントが含まれています。

  3. クエリの棒グラフを選択します (query_id はシステムによって異なる場合があります)。 結果は次のイメージのようになるはずです。

    Screenshot of the query ID.

    クエリの合計実行時間とクエリ テキストを確認できます。

  4. この棒グラフの右側は、クエリに関連付けられているクエリ プランの統計のグラフです。 プランに関連付けられているドットの上にマウス ポインターを移動します。 結果は次のイメージのようになるはずです。

    Screenshot of slow query statistics.

    クエリの平均実行時間に注意してください。 時間は異なる場合がありますが、このクエリの平均実行時間と平均待機時間を比較します。 後でパフォーマンスの向上の説明を行うので、この比較を再度実施して違いを確認します。

  5. 最後のコンポーネントは、ビジュアル クエリ プランです。 このクエリのクエリ プランは、次のイメージのようになります。

    Screenshot of the workload query plan.

    このデータベース テーブルは含まれる行が非常に少ないため、プランは必要なく、非効率的になる可能性があります。 クエリをチューニングしても、測定可能な程のパフォーマンスの向上は見られません。 クラスター化インデックス シークの列の 1 つについて、統計が不足しているという警告がプランに表示されることがあります。 全体的なパフォーマンスには影響しません。

  6. SSMS の [リソースを消費するクエリの上位] レポートの後に、[クエリ待機統計] というレポートがあります。 以前の診断から、多数の要求がほぼ 100% の CPU に加えて、絶えず RUNNABLE 状態にあったことがわかっています。 クエリ ストアには、リソースを待機することになる可能性のあるパフォーマンスのボトルネックを調べるためのレポートが付属しています。 このレポートを選択し、棒グラフの上にマウス ポインターを移動します。 結果は次のイメージのようになるはずです。

    Screenshot of the top wait statistics.

    上位の待機カテゴリは CPU (これは wait_typeSOS_SCHEDULER_YIELD に相当し、sys.dm_os_wait_stats で確認できます) と平均待機時間です。

  7. レポートの CPU 棒グラフを選択します。 CPU を待機している上位のクエリは、使用しているワークロードからのクエリです。

    Screenshot of the top wait statistics query.

    このクエリでの CPU の平均待機時間は、クエリ全体の平均実行時間の高い割合を占めることに注意してください。

    証拠を考慮すると、クエリのチューニングを何も行わない場合、ワークロードには Azure SQL Database のインスタンスにデプロイした以上の CPU 容量が必要です。

  8. 両方のクエリ ストア レポートを閉じます。 次の演習で同じレポートを使用します。

Azure Monitor によるパフォーマンスの監視

もう 1 つの方法を使用して、ワークロードのリソース使用率を表示してみましょう。 Azure Monitor には、Azure portal を使用することを含むさまざまな方法で表示できるパフォーマンス メトリックが用意されています。

  1. Azure portal を開いてから、AdventureWorks SQL データベースのインスタンスを検索します。 データベースの [概要] ペインで、[監視] タブを選びます。[監視] ペインの既定のビューは [コンピューティング使用率] です。

    Screenshot of the Azure portal with a slow query.

    この例では、最近の時間範囲の CPU 使用率が 100% に近くなっています。 このグラフには、過去 1 時間のリソース使用率 (既定では CPU と I/O) が示され、継続的に最新の情報に更新されます。 グラフを選択すると、他のリソース使用率が表示されるように、カスタマイズできます。

  2. SQL データベースのメニューで、[メトリックの追加] を選びます。 Azure SQL Database 用の Azure Monitor によって自動的に収集された [コンピューティング使用率] メトリックやその他のメトリックを確認するためのもう 1 つの方法は、[メトリックス エクスプローラー] を使用するというものです。

    Note

    [コンピューティング使用率] は、[メトリックス エクスプローラー] の定義済みのビューです。 [メトリックの追加] ウィンドウの [メトリック] ドロップダウンを選ぶと、次の結果が表示されます。

    Screenshot of Azure Monitor metrics.

    スクリーンショットに示されているように、メトリックス エクスプローラーで表示するために使用できるいくつかのメトリックがあります。 メトリックス エクスプローラーの既定のビューの期間は 24 時間で、粒度は 5 分です。 [コンピューティング使用率] ビューは過去 1 時間で、1 分の粒度 (変更可能) です。 同じビューを表示するには、[CPU の割合] を選び、キャプチャを 1 時間に変更します。 粒度が 1 分に変更され、次のイメージのようになります。

    Screenshot of Azure Monitor metrics, including CPU after 1 minute.

    既定は折れ線グラフですが、[エクスプローラー] ビューでグラフの種類を変更できます。 メトリックス エクスプローラーには、同じグラフに複数のメトリックを表示する機能など、多くのオプションが用意されています。

Azure Monitor ログ

この演習では、Azure Monitor ログを設定しませんでしたが、CPU リソース使用率のシナリオでログがどのように表示されるかを確認することをお勧めします。 Azure Monitor ログでは、Azure メトリックスよりもはるかに長い履歴レコードを取得できます。

Log Analytics ワークスペースで Azure Monitor ログを構成した場合、次の Kusto クエリを使って、データベースについての同じ CPU 使用率の結果を表示できます。

AzureMetrics
| where MetricName == 'cpu_percent'
| where Resource == "ADVENTUREWORKS"
| project TimeGenerated, Average
| render columnchart

結果は次のイメージのようになります。

Screenshot of a query measuring CPU.

Azure Monitor ログでは、最初にデータベースのログ診断を構成する際に遅延が発生するため、これらの結果が表示されるまでに多少の時間がかかることがあります。

この演習では、パフォーマンスを向上させる可能性のあるソリューションを決定するために、一般的な SQL Server パフォーマンス シナリオを観察し、詳細を確認する方法について説明しました。 次のユニットでは、パフォーマンスの高速化と調整の方法について学習します。