都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

都内でサラリーマンやってます。SQL Server を中心とした (2023年からは Azure も。) マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。英語の勉強のため、英語の

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

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

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

 8℃って春....
f:id:koogucc11:20151215183109p:plain

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

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

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

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

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

 次に下記のクエリを 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年度版 (できるシリーズ)

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