リンクサーバを用いたクエリの動作を試してみた

　12月に入って、冬とは思えない日が続きますね。今日も暖かい。最高気温は 18℃ ですか。もう春ですねww

　このままだと、スノーボードも出来なさそうな気がします。毎年いってるスノータウンイエティは人工降雪機フル稼働でなんとか営業してます。
www.yeti-resort.com

　8℃って春....

　暖かい陽気で何かとぼーっとして、無意識で物事進めちゃうことも多くなりがちですが、リンクサーバもインスタンスとか意識せずに SQL 書けちゃうんで便利ですよね。
ryuchan.hatenablog.com

　けど、その便利さの反面落とし穴も。今回の環境は、SQL Server 2016 CTP3.1 で同一クライアント内にインスタンスを二つ立てた下図の状態で検証を行います。今日も移動時間の隙間でブログを書きます！

　片方のインスタンスにリンクサーバを定義します。下記のスクリプトを SQL Server Management Studio で実行しましょう。

EXEC sp_addlinkedserver @server=N'INSTANCE2', 
                        @srvproduct=N'',
                        @provider=N'SQLNCLI', 
                        @datasrc=N'[ホスト名]\INSTANCE2';

　上記のスクリプトを実行すると、INSTANCE1 にリンクサーバの定義が生成されます。

　次に下記のクエリを INSTACE1 側から実行します。どのような動作になっているか詳細に把握するために、INSTANCE2 側 に SQL Server Profiler を実行させます。

SELECT 
    BusinessEntityID,
    TerritoryID,
    SalesQuota,
    Bonus,
    CommissionPct,
    SalesYTD,
    SalesLastYear,
    rowguid,
    ModifiedDate
FROM 
    INSTANCE2.AdventureWorks2016CTP3.Sales.SalesPerson

　SQL Server Profiler の実行結果は下記の通りです。SQL 実行前に、sp_getschemalock が Sales.SalesPerson に対して発行されています。これは、対象のテーブルに対して Sch-Sでロックを獲得します。その後、SQL 実行後に sp_releaseschemalock でテーブルのロックを解放します。実行する SQL の処理時間が短ければ大きな問題にはなりませんが、処理時間が数十秒かかる場合は....ご想像にお任せします。

declare @p1 int
set @p1=1
declare @p2 bigint
set @p2=181771620019433
exec [sys].sp_getschemalock @p1 output,@p2 output,N'"AdventureWorks2016CTP3"."Sales"."SalesPerson"'
select @p1, @p2
go
declare @p1 int
set @p1=5
exec sp_prepexec @p1 output,NULL,N'SELECT "Tbl1002"."BusinessEntityID" "Col1005","Tbl1002"."TerritoryID" "Col1006","Tbl1002"."SalesQuota" "Col1007","Tbl1002"."Bonus" "Col1008","Tbl1002"."CommissionPct" "Col1009","Tbl1002"."SalesYTD" "Col1010","Tbl1002"."SalesLastYear" "Col1011","Tbl1002"."rowguid" "Col1012","Tbl1002"."ModifiedDate" "Col1003" FROM "AdventureWorks2016CTP3"."Sales"."SalesPerson" "Tbl1002"'
select @p1
go
exec sp_unprepare 5
go
exec [sys].sp_releaseschemalock 1
go

　この問題を解決するには、OPENQUERY を使用します。OPENQUERY を使用すると、テーブルに対してロックがかかりません。下記のクエリを SQL Server Management Studio で実行します。

DECLARE @query         NVARCHAR(500)
DECLARE @sql           NVARCHAR(500)
DECLARE @linked_server NVARCHAR(500)

SET @linked_server = 'INSTANCE2'
SET @query = 'SELECT 
                  * 
              FROM 
                  OPENQUERY('+ @linked_server + ','''

SET @sql = 'SELECT
                BusinessEntityID,
                TerritoryID,
                SalesQuota,
                Bonus,
                CommissionPct,
                SalesYTD,
                SalesLastYear,
                rowguid,
                ModifiedDate 
            FROM
                AdventureWorks2016CTP3.Sales.SalesPerson'')' 
EXEC (@query+@sql)

　先ほど同じように SQL Server Profiler で動作を確認してみましょう。exec [sys].sp_getschemalock @p1 output,@p2,output,N'"AdventureWorks2016CTP3"."Sales"."SalesPerson"'　や exec [sys].sp_releaseschemalock 1 のステートメントが実行されていないことがわかります。

declare @p1 int
set @p1=0
exec sp_prepare @p1 output,NULL,N'SELECT
                BusinessEntityID,
                TerritoryID,
                SalesQuota,
                Bonus,
                CommissionPct,
                SalesYTD,
                SalesLastYear,
                rowguid,
                ModifiedDate 
            FROM
                AdventureWorks2016CTP3.Sales.SalesPerson',1
select @p1
go
exec sp_unprepare 2
go

　いくら学習しても、知らないことは次々に出てきますね。最近、O365 も学習していますが、数週間・数か月で機能が追加されていくので、キャッチアップしていくことができません。MDM 関連でも二ヶ月前くらいには Mac は対象外だったのに、いつの間にか対応されていたり....

※本で学習しても、得た知識はすぐ陳腐化するもんなぁ。継続学習あるのみ！

（無料電話サポート付）できる Office 365 Business/Enterprise対応 2015年度版 (できるシリーズ)