SQL Serverのリンクサーバーを深堀りした

本稿は、ZOZO Advent Calendar 2023 シリーズ 5 の18日目の記事です。

はじめに

ZOZOTOWNではSQL Serverをサービスのメインデータベースとして使用しています。SQL Serverにはリンクサーバーという機能があるのですが、深く理解できていなかったので調べてみました。

リンクサーバーとは

異なるインスタンスやデータソースに対して、1つのインスタンスからクエリを実行できる機能です。複数のデータベースや異なるデータソースに跨るデータを結合してクエリを実行したり、異なるインスタンスに対して直接クエリを発行したりすることができます。
注意点として、リンクサーバーを使用する際は、適切なアクセス権の設定が必要となります。また、クエリパフォーマンスの低下が発生する可能性があるため、慎重に使用する必要があります。

便利な機能ではあるが、特徴を理解したうえで使用する必要がありそうです。

リンクサーバーを調査・検証する

Oracleやpostgresqlといった異種DBMSにも接続できるようですが、弊社ではSQL Server間でのクエリ実行が主な用途なので、SQL Serverインスタンス同士で以下の項目を調査、検証します。

• リンクサーバーのアクセス権
• クエリパフォーマンス
• ローカル上のテーブルとJOIN

リンクサーバーのアクセス権

リンクサーバーを使用する際の注意点として挙げられていた適切なアクセス権の設定について調査しました。

リンクサーバーを作成し、プロパティを表示すると「セキュリティ」という項目を表示できます。
4つの接続方法のうち最も気を付けるべきは、「このセキュリティコンテキストを使用する」です。
「リモートログイン」と「パスワード」にリモート先にあるログインの情報を設定しておくことで、リンクサーバー経由でアクセスした場合、設定したログインの権限で処理を実行できてしまいます。
指定したログインにsysadminなどの強力な権限が付与されていた場合、強力な権限でリモート先のインスタンスにアクセスできるということになるので、注意が必要です。

リンクサーバーは、ログインのマッピング機能などを使用して適切な権限で運用していくことが重要です。

「ローカル サーバーのログインとリモート サーバーのログインのマッピング」機能については、以前のテックブログで紹介していますので、ご覧いただいていない場合は、以下のリンクから詳細をご確認いただけます。
https://techblog.zozo.com/entry/sensitive-data-sqlserver-implementation

クエリパフォーマンス

2つのSQL Serverインスタンスを用意します。今回使用する各インスタンスの用途と呼称は以下の通りです。

インスタンス名	用途
DB-TEST-LINK01	リンクサーバー先となるリモートデータベース
DB-TEST-LINK02	リンクサーバーを作成し、リンクサーバー経由でのクエリを発行

DB-TEST-LINK01に作成したAdventureWorksデータベースに向けて、同様のクエリをDB-TEST-LINK01のローカルからとDB-TEST-LINK02のリンクサーバーからそれぞれ実行します。各クエリの実行時間の計測と実行計画を確認してみました。

まず、クエリを実行し実行時間を計測します。

【使用クエリ】

SET STATISTICS TIME ON

SELECT
    *
FROM
    [AdventureWorks].[Sales].[SalesOrderHeader] as OH
    JOIN
        [AdventureWorks].[Sales].[SalesOrderDetail] as OD
    ON  OH.SalesOrderID = OD.SalesOrderID
WHERE
    OH.rowguid = '79B65321-39CA-4115-9CBA-8FE0903E12E6'

DB-TEST-LINK01のローカルからの実行は0ms以下という結果になりました。

DB-TEST-LINK02のリンクサーバー経由での実行は4msという結果になりました。

次に実行計画を確認します。

DB-TEST-LINK01から実行されたクエリは以下の画像のような実行計画が表示されており、WHERE句の絞り込みにインデックスが使用されていることなどが確認できます。

DB-TEST-LINK02で実行されたクエリは「Remote Query」と表示されているのみで、実行計画から確認できる内容が少ないことがわかりました。

更にプロパティを表示して「Remote Query」にカーソルを合わせると「SELECT "Col1058"...」というクエリに変換されているようです。

以下のクエリでDB-TEST-LINK01側のクエリ実行履歴を確認します。

SELECT TOP(1000)
    qt.dbid,
    qt.text,
    (substring(qt.text, qs.statement_start_offset / 2,(
                case
                    when qs.statement_end_offset = - 1 then len(convert(nvarchar(max), qt.text)) * 2
                    else qs.statement_end_offset
                end - qs.statement_start_offset
            ) / 2)) as statement
FROM
    sys.dm_exec_query_stats qs outer apply sys.dm_exec_sql_text(qs.plan_handle) as qt
WHERE
    1 = 1
and qt.text like '%SELECT "Col1058","Col1059"%'
and dbid in(1)
ORDER BY
    creation_time desc option(maxdop 1)

実行履歴として変換されたクエリがヒットしました。

text列をコピーし、DB-TEST-LINK01側で実行計画を確認してみると、DB-TEST-LINK01側での実行時に取得した実行計画と同じ結果となりました。このことからリモート先のDB-TEST-LINK01では同じ実行計画でクエリが実行されているということがわかります。

以上の結果からリンクサーバーを経由した場合、ネットワークのオーバーヘッド分のクエリ実行が遅くなることがわかりました。また、クエリが変換されてしまい、リンクサーバー経由で異なる複数のクエリが実行されている環境では、調査対象となるクエリの特定に手間がかかるといったことも起きそうです。

ローカル上のテーブルとJOIN

DB-TEST-LINK01のAdventureWorksデータベースにあるテーブルとDB-TEST-LINK02のローカル上にあるテーブルをJOINした場合どういった実行計画となるかを確認します。

DB-TEST-LINK02のローカル上に一時テーブルを作成し、DB-TEST-LINK01のAdventureWorksデータベースにあるテーブルとJOINした場合の実行計画を確認します。

【使用クエリ】
DB-TEST-LINK02のローカル上に一時テーブルを作成（データ件数は100件）

SELECT
    TOP 100 SalesOrderID
INTO
    #SalesOrderID
FROM
    [DB-TEST-LINK01].[AdventureWorks].[Sales].[SalesOrderDetail]

作成した一時テーブルとDB-TEST-LINK01のテーブルをJOIN

SELECT
    *
FROM
    #SalesOrderID as ID
    JOIN
        [DB-TEST-LINK01].[AdventureWorks].[Sales].[SalesOrderDetail] as OD
    on  ID.SalesOrderID = OD.SalesOrderID

実行計画のプロパティを表示して「Remote Query」を確認すると実行回数が100であることが確認できます。これは実行計画に「Nested Loops」があるので一時テーブルにある100件のSalesOrderIDを突き合わせているためで、実行計画上の違和感はありません。

しかし、DB-TEST-LINK01でクエリの実行回数を調査してみると、リモートで実行のクエリが100回実行されていました。これは、1つのIDを突き合わせる毎にリモートアクセスを1回実行していることになり、オーバーヘッドが大きいことがわかります。

【実行履歴の確認クエリ】

SELECT TOP(1000)
    qt.dbid,
    qt.text,
    (substring(qt.text, qs.statement_start_offset / 2,(
                case
                    when qs.statement_end_offset = - 1 then len(convert(nvarchar(max), qt.text)) * 2
                    else qs.statement_end_offset
                end - qs.statement_start_offset
            ) / 2)) as statement,
    execution_count
FROM
    sys.dm_exec_query_stats qs outer apply sys.dm_exec_sql_text(qs.plan_handle) as qt
WHERE
    1 = 1
and qt.text like '%(@P1 int)SELECT%'
and dbid in(1)
ORDER BY
    creation_time desc option(maxdop 1)

execution_count（実行回数）が100回となっている。

リンクサーバーを経由せず、DB-TEST-LINK01の同じインスタンス上に一時テーブルを作成した場合の実行計画と実行履歴は以下の通りです。
実行計画上では実行回数が100回になっている

execution_count（実行回数）が1回になっている

以上のことから、リンクサーバーを経由したリモートデータベース上のテーブルJOINはオーバーヘッドが大きく、効率が悪いということがわかりました。

まとめ

リンクサーバーを使用する際には、適切なアクセス権のもと運用する必要があります。
クエリ実行については、オーバーヘッドが大きくなるということを念頭にいれて、使用方法を検討すべきということがわかりました。
リモートデータベースでリンクサーバー経由でのクエリの実行状況を調査する際に、クエリが変換されてしまい、クエリの特定に手間がかかるという点で運用が大変になりそうだと感じました。
しかし、リンクサーバーは複数のデータベースや異なるデータソースに跨るデータを結合を可能としているとても便利な機能ですので、上記のことを理解したうえで、使用していこうと思います。