SQL Server実行計画における並列処理

投稿者：Ahmad Yaseen|更新日：2018-03-30|コメント（3）|関連：詳細>クエリの最適化

無料MSSQLTipsウェビナー:パフォーマンスのためのインデックス作成:SQL Serverインデックスを最大化するためのクエリ戦略
問題
ソリューション
並列実行計画のデモ
SQL Serverの並列性情報の取得
次の手順
著者について

無料MSSQLTipsウェビナー:パフォーマンスのためのインデックス作成:SQL Serverインデックスを最大化するためのクエリ戦略

SQL Server内のSQLステートメントの速度とパフォーマこのセッションでは、運用データベースのインデックスに関連するクエリパフォーマンスの基本を理解するのに役立ちます。適切なインデックス戦略を使用しているかどうか、およびインデックス戦略がクエリに直接影響するかどうかを判断する方法をお教えします。

問題

SQL Serverがクエリを並列に実行できることは知っていますが、クエリが並列実行計画を使用することをどのように伝えることができますか？方法を学ぶためにこの先端から点検しなさい。

ソリューション

並列性の概念は、大きなタスクを小さなタスクに分割することから来ています。最後に、各小さなタスクから得られた部分的な結果は、一つの最終結果に一緒に結合されます。一つの大きなタスクを直列に実行するのと比較して、これがどれだけ速く実行できるか、複数のシリアルタスクを同時に並列に実行することでパフォーマ

ヒントを理解する前に、SQL Serverの並列性の概念に関連する多くの技術的な用語を理解することは有益です:

スケジューラは、SQL Serverスレッドの実行をスケジュールする責任があります。
ワーカーは、特定のタスクを実行するためにスケジューラにバインドされているスレッドです。
並列度は、ワーカータスクを達成するために並列計画に割り当てられているワーカーの数、またはプロセッサの数です。
MAXIMUM Degree of Parallelism(MAXDOP)は、並列計画が使用できるプロセッサの数を制限するために使用されるサーバー、データベース、またはクエリレベルオプションです。 MAXDOPのデフォルト値は0で、sql Server Engineはクエリparallelexecutionで使用可能なすべてのプロセッサ(最大64)を使用できます。 MAXDOPオプションを1に設定すると、複数のprocessorinを使用してクエリを実行することができなくなり、SQL Serverエンジンではserialplanを使用してクエリを実行します。 MAXDOPオプションの値は最大32767で、MAXDOP値がサーバーで使用可能なprocessorsavailableの数を超えた場合、SQL Serverエンジンは使用可能なすべてのサーバープロセッサを並列計画実行で使 SQL Serverが単一のprocessorserverにインストールされている場合、MAXDOPの値は無視されます。
タスクは、特定の作業者に割り当てられた小さな作業です。
実行コンテキストは、各singletaskが内部で実行される境界です。
Parallel Page Supplierは、クエリによって要求されたデータセットを参加ワーカー内に分散するSQL ServerStorageエンジンの一部です。
Exchangeは、クエリ並列計画に含まれる異なる実行コンテキストを一緒に接続し、最終的な結果を取得するコンポーネントです。

並列計画を使用してクエリを実行するかどうかの決定は、複数の要因に依存します。たとえば、SQL Serverをマルチprocessorserverにインストールし、要求されたスレッド数を満たす必要があり、theMaximum Degree of Parallelismオプションが1に設定されておらず、クエリのcostofがParallelismvalueに

このヒントの目的は、クエリの実際の実行計画から並列クエリスレッドの情報を取得することです。 Sql Serverエンジンがクエリの実行中に使用可能なすべてのプロセッサ、つまりこのデモで使用されているmymachineの4つのプロセッサを使用できるようにするために、sql Serverエンジンがクエリの実行中に使用可能なすべてのプロセッサを使用できるようにするために、すべてのケースでクエリのコストがParallelismのコストしきい値を超えていることを確認するために、Cost Thresholdfor Parallelism値を0に設定します。

並列処理のコストしきい値は、SQL Server managementstudioを使用してSQL Serverインスタンスに接続し、instancenameを右クリックしてプロパティオプションを選択することで設定できます。 Server Propertieswindowの詳細ページから、Parallelismセクションまでスクロールダウンして、以下のスクリーンショットに示されているように、Cost Threshold for Parallelismオプションのデフォルト値(5)、または必要に応じてParallelismのMaxDegreeをオーバーライドすることができます。:

sp_configureを使用して、並列処理のコストしきい値の既定値を上書きすることもできます。並列処理のコストしきい値を変更できるようにするには、次のスクリプトに示すように、sp_configureを使用して最初にShow Advancedオプションを有効にする必要があ:

EXEC sp_configure 'show advanced options', 1;RECONFIGURE;GOEXEC sp_configure 'cost threshold for parallelism', 0;RECONFIGURE;GOEXEC sp_configure 'show advanced options', 0;RECONFIGURE;

並列実行計画のデモ

デモ用にサーバーが構成されました。以下に示すように、Create TABLE T-SQL文を使用して新しいテーブルを作成してみましょう:

USE MSSQLTipsDemoGOCREATE TABLE ParallelDemo( ID INT IDENTITY (1,1) PRIMARY KEY, FirstName NVARCHAR (200), LastName NVARCHAR (200), PhoneNumber VARCHAR(50), BirthDate DATETIME, Address NVARCHAR(MAX) )

テーブルが作成されたら、以下のINSERT INTO文を使用して、テーブルに100Kのレコードを入力します:

INSERT INTO ParallelDemo VALUES ('John','Horold','+96255889955','1987-01-08','Jordan - Amman - Mecca Street - Building 77')GO 50000 INSERT INTO ParallelDemo VALUES ('Michel','Anglo','+96255776655','1985-06-02','Jordan - Amman - Mecca Street - Building 74')GO 50000

ここで、「実際の実行計画を含める」を有効にしてから、以下のSELECTクエリを実行します:

SELECT , , , , , FROM .. WHERE Address LIKE '%AMM%' ORDER BY BirthDate desc

これが実行が完了したら、クエリを実行してから生成された実行計画を確認すると、SQL Serverエンジンがparallelplanを使用してクエリを実行することを決定したことがわかります。

以下では、Parallelismoperatorと、並列処理を実行した各演算子の下に二つの矢印が付いた黄色の円があるため、実行計画が並列計画であることがわかります。

実行プランは単なるグラフではなく、送信されたクエリの実行プロセスを記述する貴重な情報が含まれています。この情報を表示するには、実行計画の選択ノードを右クリックし、プロパティオプションを選択します。プロパティウィンドウから、以下に示すMiscセクションの下にある並列度属性から送信されたクエリを処理するために使用されるプロセッサの数を:

この同じウィンドウから、並列クエリスレッドに関する情報もThreadStatセクションの下に表示されます。この属性には、クエリ実行プラン内の同時実行パスのnumberof、NUMAノードID、ReservedThreads属性の下で指定されたNUMAnode用に予約されている並列スレッドの数、およびUsedThreads属性の下で

プロパティウィンドウから、SQL Serverエンジンが4プロセッサを使用してクエリを実行したことを導き出すことができます。さらに、クエリ実行プランにはexecutionpathがあり、4つのスレッドが予約され、並列プランを使用して送信されたクエリを実行するために使用されます。

以下に示すように、ThreadStatセクションの下を参照することにより、XML実行計画から同じ情報を取得することもできます:

また、I/Oとeachthreadによって消費されるCPUリソースを確認するために深く潜ることができます。 ExecutionplanのClustered Index Scanノードを右クリックし、プロパティを選択します。表示されたプロパティウィンドウから、実際のI/O統計ノードを拡張して、クエリの実行中に各スレッドによって実行される論理的および物理的なreadsoperationsの数をチェックすることができます。さらに、各スレッドから取得される行数、各スレッドによって消費されるCPU時間、および最後に各スレッドによって経過した実行時間を確認して、:

Sql Server MAXDOPのクラスター化インデックススキャンのプロパティ

結果は、クエリタスクの実行に参加した4つのワーカースレッドと、ワーカースレッド間を調整するcoordinatorthreadがあるノードのプロパティの選択ウィンドウと一致します。

以前の情報は、クエリを実行した後に生成された実際の実行プランからのみ取得できることを考慮してください。クエリを実行せずに見積実行計画からその情報を検索しようとすると、見積実行計画について以下に示すように、queryexecution中に使用可能なプロセッサからクエリ

SQL Serverの並列性情報の取得

クエリの実行中に、クエリの実行中にsubmittedqueryの実行に使用されるスレッドに関する情報を取得するには、querysession IDを指定してsp_who2コマンドを実行するだけで、以下に示すように、クエリの実行中にsubmittedqueryを実行するために使用されるスレッドに関する情報を取得できます。:

実行プランでは、そのプラン内の各演算子には番号が割り当てられており、実行に使用されるスケジューラがありました。この情報は、sysを呼び出すことで取得できます。以下のT-SQLスクリプトのように、dm_os_tasksシステムカタログビューとsys.dm_os_workersand sys.dm_exec_query_profilesシステムカタログビューとの結合:

SELECT OSTSK.scheduler_id, qp.node_id, qp.physical_operator_nameFROM sys.dm_os_tasks OSTSKLEFT JOIN sys.dm_os_workers OSWRK on OSTSK.worker_address=OSWRK.worker_addressLEFT JOIN sys.dm_exec_query_profiles qp on OSWRK.task_address=qp.task_addressWHERE OSTSK.session_id=58ORDER BY scheduler_id, node_id;

ActualExecutionプランを有効にしてSELECTステートメントを実行している間に前のスクリプトを実行すると、sort、Filter、Clustered Indexの三つのプラン演算子が、その横に表示されている各演算子のIDを使用してスキャンされていることが表示されます。これは、4つのスケジューラを使用して4回実行されます。 IDが0の並列処理演算子は、次のように、最後のステップでallexecutionコンテキストを接続するために、第四のスケジューラによって一度実行されます: