データサイエンスとデータベース

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

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

nodejsをいつ使用するか

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

＃1犯人を見つける

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

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

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

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

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

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

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

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オプティマイザーはその役割を果たしません。

SQLServerは高度な検索とソートアルゴリズム。検索と並べ替えのアルゴリズムが得意であれば、ほとんどの場合、SQLServerが特定のアクションを実行している理由を推測できます。そのようなアルゴリズムをもっと学び、理解するための最良の本は コンピュータプログラミングの芸術 ドナルド・クヌース。

微調整が必要なクエリを調べるときは、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は、最初の実行で生成した実行プランを再利用する可能性があります。

プログラマーのためのC ++

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

機械学習をプログラムする方法

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

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

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

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

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

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

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

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

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

SQLサーバーは私たちにdm_db_index_usage_statsを提供しますインデックス統計を見つけるためのDMV。以下の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

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

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

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

＃7 SQLServerを過負荷にしない

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

アマゾンウェブサービス認定学習ガイド

製造
開発
テスト
分析

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

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

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

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

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

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

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