SQL Server: パフォーマンスの問題を掘り下げる

  • [アーティクル]

SQL Server で待機時間を引き起こしている一番の原因を特定する技法は、いくつかあります。これらの技法を使用すると、パフォーマンス チューニングを管理できます。

出典: 『SQL Server DMV Starter Pack』(Red Gate Books、2010 年)

Glenn Berry、Louis Davidson、Tim Ford

OS レベルまで話を掘り下げて、トランザクションで必要なタスクを実行するワーカー スレッドについて検討しましょう。このカテゴリの動的管理オブジェクト (DMO) では、SQL Server と OS およびハードウェアの間で行われる処理に関する詳細な情報が提供されます (このカテゴリの DMO の名前は、すべて sys.dm_os_ で始まります)。次の DMO を使用すると、あらゆる種類のパフォーマンスに関する問題の解決策を得ることができます。

  • wait_stats DMO - SQL Server の OS スレッドで待機している対象がわかります。
  • performance_counters - SQL Server のパフォーマンス カウンターの値とそれらの値をデコードする方法がわかります。
  • ring_buffers、schedulers、wait_stats - CPU の使用率に関する情報が通知されます。
  • sys_info - SQL Server を実行しているコンピューターの特性に関する情報が提供されます。
  • sys_memory または process_memory - メモリ全体の使用状況がわかります。
  • memory_cache_counters または buffer_descriptors - キャッシュ メモリの使用状況がわかります。

上記のすべてのクエリは、SQL Server 2005、SQL Server 2008、および SQL Server 2008 R2 で機能します。これらのクエリを実行するには、VIEW SERVER STATE 権限が必要です。上記のオブジェクトの正式名は動的管理オブジェクト (DMO) ですが、DMO という表現を使用すると、まったく関連のない分散管理オブジェクト (Distributed Management Object) と混同されがちなので、データベース管理者の間では、DMO の総称として動的管理ビュー (DMV) という表現が使われるのがきわめて一般的です。

SQL Server の待機

OS レベルのパフォーマンス チューニングで最も重要な DMV は sys.dm_os_wait_stats であることは、ほぼ間違いないでしょう。要求された作業を続行する前に、なんらかの理由でセッションが待機しなければならない状況が発生するたびに、SQL Server では、待機時間と待機対象のリソースが記録されます。sys.dm_os_wait_stats DMV を使用すると、全セッション ID の情報を集約した待機の統計データが表示され、特定のインスタンスで深刻な待機が発生している場所の概要が提供されます。

また sys.dm_os_wait_stats DMV を使用すると、特定のリソースの使用状況に関する測定値 (ディスクの転送速度、消費された CPU 時間の合計など) を提供するパフォーマンス (PerfMon) カウンターの情報も表示されます。待機の統計データとリソースの使用状況に関する測定値を関連付けることによって、システムで最も問題が大きなリソースをすばやく特定し、潜在的なボトルネックを浮き彫りにすることができます。

待機とキュー

Tom Davidson 氏が執筆したホワイト ペーパー「SQL Server 2005 Waits and Queues (SQL Server 2005 の待機とキュー、英語)」で説明されているように、待機とキューという考え方を使用するのは、パフォーマンス チューニングの効果的な基本原則です。基本的に、SQL Server の各要求によって、いくつもの "ワーカー タスク" が発行され、SQL Server のスケジューラによって、各タスクがワーカー スレッドに割り当てられます。通常、CPU ごとに SQL Server の OS スケジューラが 1 つ存在し、1 つのスケジューラにつき実行できるセッションは常に 1 つだけです。

利用可能なワーカー スレッドにワークロードを均等に分配するのは、スケジューラの役割です。セッションのワーカースレッドがプロセッサで実行されている場合、セッションは実行中になり、sys.dm_exec_requests DMV の [status] (状態) 列に [running] (実行中) と表示されます。

スレッドの実行準備が整っていて、現在割り当てられているスケジューラで別のセッションが実行中の場合、そのスレッドは "runnable" (実行可能) キューに配置されます。これは単にプロセッサで実行されるためのキューに配置されていることを意味します。この状態はシグナルの待機と呼ばれます。

シグナルの待機

シグナルの待機時間は [signal_wait_time_ms] 列に表示されます。これは、単に CPU の待機時間を意味します。セッションで別のリソースが利用できるようになるのを待機している場合 (ページがロックされている場合など)、または実行中のセッションで I/O の実行が必要な場合、そのセッションは待機キューに配置されます。これがリソースの待機で、リソースを待機しているセッションの状態は [suspended] (中断) と記録されます。待機の理由も記録され sys.dm_os_wait_stats DMV の [wait_type] 列に表示されます。

待機時間の合計も [wait_time_ms] 列に表示されるので、次の式を使って、リソースの待機にかかった時間を計算できます。

リソースの待機時間 = 全体の待機時間 - シグナルの待機時間 (<wait_time_ms> - <signal_wait_time_ms>)

オンライン トランザクション処理 (OLTP) システムは、大量の短いトランザクションで構成されているので、シグナルの待機は回避できません。全体の待機時間に対するシグナルの待機時間の割合は、潜在的な CPU の負荷に関する重要な指標です。

シグナルの待機の割合が高い場合、CPU に負荷がかかっている証拠です。通常、この割合が 25% 以上であれば割合が高いと見なされますが、この基準はシステムによって異なります。割合が 10 ～ 15% 以上でも、注意が必要な場合があります。概して、待機の統計は、システムの応答時間を診断する効果的な手段になります。端的に言うと、実行中か待機中のどちかでしかないのです。

応答に時間がかかっているのに、重大な待機 (主にシグナルの待機) が見つからない場合、CPU に注目する必要があることがわかります。応答時間の大部分が、主にネットワークや I/O などの他のリソースの待機で占められていることが発覚した場合、これらのリソースでパフォーマンス チューニングを重点的に行う必要があることがわかります。

パフォーマンス プロファイル

OS のパフォーマンス チューニングの 1 つ目のスクリプト例では、実行済みスレッドで発生したすべての待機に関する情報を返す sys.dm_os_wait_stats DMV を使用します。このさまざまな情報が集約されたビューを使用して、SQL Server 全体および特定のクエリやバッチに関するパフォーマンスの問題を診断できます。

次のシンプルなクエリを使用すると、全体の待機時間に対するシグナルの待機とリソースの待機の割合を算出して、潜在的な CPU の負荷を診断できます。

-- Total waits are wait_time_ms (high signal waits indicate CPU pressure)
SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits] , CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20, 2)) AS [%resource waits]FROM sys.dm_os_wait_stats ;

このクエリは、CPU に負荷がかかっていることを確認するのに有効です。シグナルの待機は、CPU がスレッドにサービスを提供するまでの待機時間なので、シグナルの待機の合計が 10 ～ 15% 以上になると、CPU に負荷がかかっている目安となります。この待機の統計データは、SQL Server が最後に再起動されたときから蓄積されているので、シグナルの待機の基準値を把握し、長い時間をかけて、その動向を観察する必要があります。

待機の統計データは、サーバーを再起動しなくても、次のようにデータベースの整合性チェック (DBCC) SQLPERF コマンドを発行して、手動で消去できます。

-- Clear Wait Stats

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) ;

SQL Server インスタンスが、長期間稼動し続けているときに、重要な新しいインデックスを追加するなど、大きな変更を加えた場合は、古い待機の統計データの消去を検討する必要があります。古いデータを消去しないと、蓄積された待機の統計の古いデータによって、その変更が待機時間に及ぼしている影響が数値として表れづらくなります。

2 つ目のスクリプト例では、sys.dm_os_wait_stats DMV を使用し、SQL Server で最も待機時間が多く発生しているリソースを特定する方法を示します (図 1 参照)。

図 1 次のスクリプトでは待機の原因についてのレポートが生成されます

-- Isolate top waits for server instance since last restart

-- or statistics clear

WITH Waits AS ( SELECT wait_type , wait_time_ms / 1000. AS wait_time_s , 100. * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS pct ,

ROW_NUMBER() OVER ( ORDER BY wait_time_ms DESC ) AS rn FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ( 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN' ) ) SELECT W1.wait_type , CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s , CAST(W1.pct AS DECIMAL(12, 2)) AS pct , CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn GROUP BY W1.rn , W1.wait_type , W1.wait_time_s , W1.pct HAVING SUM(W2.pct) - W1.pct < 95 ; -- percentage threshold

図 1 のスクリプトでは、インスタンス レベルで大きなボトルネックを特定し、特定の問題に集中してパフォーマンス チューニングを行うのに役立ちます。たとえば、ディスク I/O 関連で最も多くの待機が発生している場合、ディスク関連の DMV のクエリやパフォーマンス カウンターを使って、この問題をさらに調査することができます。

パフォーマンス カウンター

パフォーマンス カウンターの情報が表示される DMV は sys.dm_os_performance_counters です。この DMV を使用すると、サーバーで管理されているパフォーマンス カウンターごとに 1 行のデータが返されます。この DMV は便利ですが、取り扱いが面倒だと感じる場合もあります。

指定した行の cntr_type の値によっては、複数の操作を行わないと、この DMV から有益な情報を得られないこともあります。sys.dm_os_performance_counters は、SQL Server 2000 で使われていた sys.sysperfinfo の後継です。

図 2 のスクリプトは、トランザクション ログに記録された異常な状態を調査するのに有効です。このスクリプトを使用すると、復旧モデル、ログ再利用の待機状態、トランザクション ログのサイズ、ログの使用領域、ログの使用率、互換性レベル、および現在の SQL Server インスタンスの各データベースのページ確認オプションが返されます。

図 2 次のスクリプトを使用して、トランザクション ログに記録されている情報を確認できます

-- Recovery model, log reuse wait description, log file size,

-- log usage size and compatibility level for all databases on instance

SELECT db.[name] AS [Database Name] , db.recovery_model_desc AS [Recovery Model] , db.log_reuse_wait_desc AS [Log Reuse Wait Description] , ls.cntr_value AS [Log Size (KB)] , lu.cntr_value AS [Log Used (KB)] , CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Log Used %] , db.[compatibility_level] AS [DB Compatibility Level] , db.page_verify_option_desc AS [Page Verify Option]FROM sys.databases AS db INNER JOIN sys.dm_os_performance_counters AS lu ON db.name = lu.instance_name INNER JOIN sys.dm_os_performance_counters AS ls ON db.name = ls.instance_nameWHERE lu.counter_name LIKE ‘Log File(s) Used Size (KB)%’ AND ls.counter_name LIKE 'Log File(s) Size (KB)%' ;

図 2 のクエリは、あまり使用したことのないデータベース サーバーを評価するのに有効です。また一般的には監視用途により適しています。たとえば、トランザクション ログの 85% が使用済みで、Log Reuse Wait Status (ログ再利用の待機状態) が ACTIVE_TRANSACTION のような例外的な状態である場合、そのことを通知するなんらかの機能が起動されます。

このような監視機能は、待機や他のパフォーマンスの問題の発生源や原因を特定するのに非常に有効です。このような調査を行うことは、より正確に適切な箇所に対してパフォーマンス チューニングの労力を注ぐのにも役立ちます。

Glenn Berry

Glenn Berry は、米国コロラド州デンバーの NewsGator Technologies でデータベース アーキテクトを務めています。彼は SQL Server の MVP ですが、MCITP、MCDBA、MCSE、MCSD、MCAD、MCTS など全種類のマイクロソフト認定資格を保有しており、試験好きであることの証明になっています。

Louis Davidson

Louis Davidson は、データベースの社内開発者およびアーキテクトとして、IT 業界に 16 年間携わっています。彼は、SQL Server の MVP を 6 年連続で受賞しており、データベース設計についての書籍を 4 冊執筆しています。現在は、米国のバージニア州バージニア ビーチとテネシー州ナッシュビルにある企業をサポートする Christian Broadcasting Network のデータ アーキテクトとして働いていますが、データベース管理者として任務に当たることもあります。

Timothy Ford

Timothy Ford は、SQL Server の MVP であり、SQL Server に 10 年以上携わっています。彼は、Spectrum Health の SQL Server プラットフォームにおける主力データベース管理者で、非常に詳しい知識を持っています。また、2007 年からさまざまな Web サイトでテクノロジについての記事を執筆しており、自身のブログ (thesqlagentman.com、英語) では、SQL Server、在宅勤務、および高度な開発のトピックを扱っています。

『SQL Server DMV Starter Pack』の詳細については、red-gate.com (英語) を参照してください。

関連コンテンツ