パフォーマンスのためにMicrosoftSQLServerを調整する方法

公開: 2022-03-11

ユーザーを維持するには、アプリケーションやWebサイトを高速に実行する必要があります。 ミッションクリティカルな環境では、情報の取得が数ミリ秒遅れると大きな問題が発生する可能性があります。 データベースのサイズが日々大きくなるにつれて、データをできるだけ速くフェッチし、データをデータベースにできるだけ速く書き戻す必要があります。 すべての操作がスムーズに実行されるようにするには、データベースサーバーのパフォーマンスを調整する必要があります。

この記事では、市場でトップクラスのデータベースサーバーの1つであるMicrosoft SQL Server（略してSQL Server）での基本的なパフォーマンス調整の手順を段階的に説明します。

＃1犯人を見つける

他のソフトウェアと同様に、SQLServerは複雑なコンピュータープログラムであることを理解する必要があります。 問題が発生した場合は、期待どおりに実行されない理由を見つける必要があります。

SQLサーバーのパフォーマンス

SQL Serverから、データを可能な限り高速かつ正確にプルおよびプッシュする必要があります。 問題がある場合、いくつかの基本的な理由と、最初に確認する2つのことは次のとおりです。

  • ハードウェアとインストールの設定。SQLServerのニーズは特定のものであるため、修正が必要になる場合があります。
  • SQLServerが実装するための正しいT-SQLコードを提供した場合

SQL Serverはプロプライエタリソフトウェアですが、Microsoftは、SQL Serverを理解し、効率的に使用するための多くの方法を提供しています。

ハードウェアに問題がなく、インストールが適切に行われているにもかかわらず、SQL Serverの実行速度が遅い場合は、最初にソフトウェア関連のエラーがあるかどうかを確認する必要があります。 何が起こっているかを確認するには、さまざまなスレッドのパフォーマンスを観察する必要があります。 これは、さまざまなスレッドの待機統計を計算することによって実現されます。 SQL Serverは、すべてのユーザー要求にスレッドを使用します。スレッドは、SQLServerと呼ばれる複雑なプログラム内の別のプログラムに他なりません。 このスレッドは、SQLサーバーがインストールされているオペレーティングシステムスレッドではないことに注意してください。 これは、SQLServerの疑似オペレーティングシステムであるSQLOSスレッドに関連しています。

待機統計は、 sys.dm_os_wait_stats動的管理ビュー（DMV）を使用して計算できます。これにより、現在の状態に関する追加情報が提供されます。 このビューを照会するためのオンラインのスクリプトはたくさんありますが、理解しやすく、待機統計を観察するためのすべての重要なパラメーターがあるため、私のお気に入りはPaulRandalのスクリプトです。

 WITH [Waits] AS (SELECT [wait_type], [wait_time_ms] / 1000.0 AS [WaitS], ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS], [signal_wait_time_ms] / 1000.0 AS [SignalS], [waiting_tasks_count] AS [WaitCount], 100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage], ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum] FROM sys.dm_os_wait_stats WHERE [wait_type] NOT IN ( N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') AND [waiting_tasks_count] > 0 ) SELECT MAX ([W1].[wait_type]) AS [WaitType], CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S], CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S], CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S], MAX ([W1].[WaitCount]) AS [WaitCount], CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage], CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S], CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S], CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S] FROM [Waits] AS [W1] INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum] GROUP BY [W1].[RowNum] HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold GO

このスクリプトを実行するときは、結果の一番上の行に集中する必要があります。これは、最初に設定され、最大待機タイプを表すためです。

正しい決定を下せるように、待機タイプを理解する必要があります。 さまざまな待機タイプについては、Microsoftの優れたドキュメントを参照してください。

PAGEIOLATCH_XXが多すぎる例を見てみましょう。 これは、スレッドがディスクからバッファへのデータページの読み取りを待機していることを意味します。バッファはメモリブロックにすぎません。 何が起こっているのかを確実に理解する必要があります。 これは、必ずしもI / Oサブシステムの不良やメモリの不足を意味するわけではなく、I / Oサブシステムとメモリを増やすことで問題が解決しますが、一時的なものにすぎません。 永続的な解決策を見つけるには、ディスクから大量のデータが読み取られている理由を確認する必要があります。これを引き起こしているのはどのタイプのSQLコマンドですか。 where句などのフィルターを使用して読み取るデータが少なくなるのではなく、読み取るデータが多すぎませんか？ テーブルスキャンまたはインデックススキャンが原因で発生しているデータ読み取りが多すぎませんか？ 既存のインデックスを実装または変更することで、それらをインデックスシークに変換できますか？ SQL Optimizer（SQLサーバープログラム内の別のプログラム）によって誤解されているSQLクエリを記述していますか？

さまざまな角度から考え、さまざまなテストケースを使用して解決策を考え出す必要があります。 上記の待機タイプごとに異なるソリューションが必要です。 データベース管理者は、アクションを実行する前に、それらを徹底的に調査する必要があります。 ただし、ほとんどの場合、問題のあるT-SQLクエリを見つけて調整すると、問題の60〜70％が解決されます。

＃2問題のあるクエリを見つける

上記のように、最初にできることは、問題のあるクエリを検索することです。 次のT-SQLコードは、パフォーマンスが最も低い20のクエリを検出します。

 SELECT TOP 20 total_worker_time/execution_count AS Avg_CPU_Time ,Execution_count ,total_elapsed_time/execution_count as AVG_Run_Time ,total_elapsed_time ,(SELECT SUBSTRING(text,statement_start_offset/2+1,statement_end_offset ) FROM sys.dm_exec_sql_text(sql_handle) ) AS Query_Text FROM sys.dm_exec_query_stats ORDER BY Avg_CPU_Time DESC

結果に注意する必要があります。 クエリの平均実行時間が最大になる場合でも、1回だけ実行すると、平均実行時間が中程度で1日に何度も実行されるクエリと比較して、サーバーへの全体的な影響は少なくなります。

＃3微調整クエリ

T-SQLクエリの微調整は重要な概念です。 理解しておくべき基本的なことは、T-SQLクエリを記述してインデックスを実装できることです。これにより、SQLオプティマイザーは、必要なことを実行するための最適化された計画を見つけることができます。 SQL Serverの新しいリリースごとに、最適化されていないSQLクエリを作成する際の間違いをカバーし、以前のオプティマイザーに関連するバグを修正する、より洗練されたオプティマイザーを入手します。 ただし、オプティマイザがどれほどインテリジェントであっても、（適切なT-SQLクエリを記述して）必要なものがわからない場合、SQLオプティマイザはその役割を果たしません。

SQL Serverは、高度な検索および並べ替えアルゴリズムを使用します。 検索と並べ替えのアルゴリズムが得意であれば、ほとんどの場合、SQLServerが特定のアクションを実行している理由を推測できます。 このようなアルゴリズムをさらに学び、理解するための最良の本は、DonaldKnuthによるTheArt ofComputerProgrammingです。

微調整が必​​要なクエリを調べるときは、SQLサーバーがクエリをどのように解釈しているかを確認できるように、それらのクエリの実行プランを使用する必要があります。

ここでは実行計画のすべての側面を網羅することはできませんが、基本的なレベルでは、考慮する必要があることを説明できます。

  • まず、どの演算子がクエリコストの大部分を占めるかを見つける必要があります。
  • オペレーターが多額の費用を負担している場合は、その理由を知る必要があります。 ほとんどの場合、スキャンはシークよりも多くのコストがかかります。 インデックスシークではなく、特定のスキャン（テーブルスキャンまたはインデックススキャン）が発生している理由を調べる必要があります。 テーブルの列に適切なインデックスを実装することでこの問題を解決できますが、他の複雑なプログラムと同様に、固定された解決策はありません。 たとえば、テーブルが小さい場合、スキャンはシークよりも高速です。
  • 約78の演算子があり、SQLServer実行プランのさまざまなアクションと決定を表します。 それらをよりよく理解し、適切なアクションを実行できるように、Microsoftのドキュメントを参照してそれらを詳細に調査する必要があります。
関連： SQLインデックスの説明、Pt。 1

＃4実行計画の再利用

テーブルに適切なインデックスを実装し、適切なT-SQLコードを記述しても、実行プランを再利用しないと、パフォーマンスの問題が発生します。 クエリを微調整した後、必要に応じて実行プランが再利用できることを確認する必要があります。 CPU時間のほとんどは、計画を再利用する場合に排除できる実行計画の計算に費やされます。

以下のクエリを使用して、実行プランが再利用された回数を確認できますusecountsは、プランが再利用された回数を表します。

 SELECT [ecp].[refcounts] , [ecp].[usecounts] , [ecp].[objtype] , DB_NAME([est].[dbid]) AS [db_name] , [est].[objectid] , [est].[text] as [query_ext] , [eqp].[query_plan] FROM sys.dm_exec_cached_plans ecp CROSS APPLY sys.dm_exec_sql_text ( ecp.plan_handle ) est CROSS APPLY sys.dm_exec_query_plan ( ecp.plan_handle ) eqp

実行プランを再利用する最良の方法は、パラメーター化されたストアドプロシージャを実装することです。 ストアドプロシージャを実装する立場にない場合は、 sp_executesqlを使用できます。これは、SQLステートメントへの唯一の変更がパラメーター値である場合にT-SQLステートメントを実行するために代わりに使用できます。 SQL Serverは、最初の実行で生成した実行プランを再利用する可能性があります。

繰り返しますが、他の複雑なコンピュータプログラムと同様に、固定された解決策はありません。 場合によっては、計画を再度コンパイルする方がよい場合があります。

次の2つのクエリ例を調べてみましょう。

  • select name from table where name = 'sri';
  • select name from table where name = 'pal';

name列に非クラスター化インデックスがあり、テーブルの半分に値sriがあり、 name列にpalがある行がいくつかあると仮定します。 最初のクエリでは、テーブルの半分が同じ値であるため、SQLServerはテーブルスキャンを使用します。 ただし、2番目のクエリでは、 pal値を持つ行が少ないため、インデックススキャンを使用することをお勧めします。

クエリは似ていますが、同じ実行プランは適切なソリューションではない場合があります。 ほとんどの場合、それは別のケースになるため、決定する前にすべてを注意深く分析する必要があります。 実行プランを再利用したくない場合は、ストアドプロシージャでいつでも「再コンパイル」オプションを使用できます。

ストアドプロシージャまたはsp_executesqlを使用した後でも、実行プランが再利用されない場合があることに注意してください。 彼らです：

  • クエリで使用されるインデックスが変更または削除された場合
  • クエリで使用されるテーブルの統計、構造、またはスキーマが変更された場合
  • 「再コンパイル」オプションを使用する場合
  • 挿入、更新、削除が多い場合
  • 単一のクエリ内でDDLとDMLを混在させる場合

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

クエリを微調整した後、インデックスがどのように使用されているかを確認する必要があります。 インデックスのメンテナンスには、多くのCPUとI/Oが必要です。 データベースにデータを挿入するたびに、SQL Serverもインデックスを更新する必要があるため、使用されていない場合はインデックスを削除することをお勧めします。

SQLサーバーのパフォーマンス

SQLサーバーは、インデックス統計を検索するためdm_db_index_usage_statsを提供します。 以下のT-SQLコードを実行すると、さまざまなインデックスの使用統計が得られます。 まったく使用されていない、またはほとんど使用されていないインデックスが見つかった場合は、それらを削除してパフォーマンスを向上させることができます。

 SELECT OBJECT_NAME(IUS.[OBJECT_ID]) AS [OBJECT NAME], DB_NAME(IUS.database_id) AS [DATABASE NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM SYS.DM_DB_INDEX_USAGE_STATS AS IUS INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = IUS.[OBJECT_ID] AND I.INDEX_ID = IUS.INDEX_ID

＃6SQLServerのインストールとデータベースのセットアップ

データベースを設定するときは、データとログファイルを別々に保持する必要があります。 これの主な理由は、データファイルの書き込みとアクセスがシーケンシャルではないのに対し、ログファイルの書き込みとアクセスはシーケンシャルであるためです。 それらを同じドライブに配置すると、最適化された方法でそれらを使用することはできません。

ストレージエリアネットワーク（SAN）を購入する際、ベンダーからセットアップ方法に関する推奨事項が提供される場合がありますが、この情報が常に役立つとは限りません。 データとログファイルを別々に最適化された方法で保持する方法について、ハードウェアおよびネットワーク担当者と詳細に話し合う必要があります。

＃7SQLServerをオーバーロードしないでください

データベース管理者の主なタスクは、運用サーバーがスムーズに実行され、可能な限り顧客にサービスを提供できるようにすることです。 これを実現するには、次の環境用に（可能であれば、別々のマシンに）別々のデータベースを維持する必要があります。

  • 製造
  • 発達
  • テスト
  • 分析

本番データベースの場合は完全リカバリモードのデータベースが必要であり、他のデータベースの場合は単純なリカバリモードで十分です。

本番データベースでテストすると、トランザクションログ、インデックス、CPU、およびI/Oに多くの負荷がかかります。 そのため、生産、開発、テスト、分析に別々のデータベースを使用する必要があります。 可能であれば、データベースごとに別々のマシンを使用してください。これにより、CPUとI/Oの負荷が軽減されます。

＃8トランザクションログ、tempdb、メモリ

ログファイルの自動拡張操作には時間がかかり、他の操作が完了するまで待機する可能性があるため、ログファイルには通常の操作に十分な空き領域が必要です。 各データベースのログファイルサイズとその使用量を確認するには、 DBCC SQLPERF(logspace)を使用できます。

tempdbを設定する最良の方法は、別のディスクに置くことです。 自動成長の状況に達するとパフォーマンスが低下するため、初期サイズを可能な限り大きく保つ必要があります。

前述のように、SQLサーバーが別のマシンで実行されていることを確認する必要があります。できれば、他のアプリケーションがインストールされていないマシンで実行することをお勧めします。 オペレーティングシステム用にメモリをいくらか保持する必要があります。さらに、クラスタの一部である場合はさらにメモリを保持する必要があるため、ほとんどの場合、約2GBで十分です。

ミッションクリティカルな環境では、情報の取得が1ミリ秒遅れると、取引が妨げられる可能性があります。
つぶやき

結論：

ここで説明する手順と提案は、基本的なパフォーマンス調整のみを目的としています。 これらの手順に従うと、平均して、パフォーマンスが約40〜50％向上する可能性があります。 高度なSQLServerのパフォーマンス調整を行うには、ここで説明する各手順をさらに深く掘り下げる必要があります。

Toptal Engineeringブログでさらに読む：

  • SQLインデックスとパーティションを使用したボトルネックの解決
  • OracleからSQLServerおよびSQLServerからOracleへの移行ガイド