none
SQL Server上で処理中のスクリプトのデータの追跡について RRS feed

  • 質問

  • 失礼します。

    「APPLYの使用」の検証を行うべく、下記のホームページ上に掲載されているスクリプトの実行時のデータの流れを視覚的に確認しながら追跡したいのですが、どのような方法がありますでしょうか?「Anchor Member (AM)」と「Recursive Member (RM)」にどのようなデータがセットされているのかを直接見る方法を調べております。

    「SQL Server Profiler」の使い方も調べているところではありますが、もひとつわかってなくて申し訳ございません。
    よろしくお願いいたします。

    掲載先URL:
    https://technet.microsoft.com/ja-jp/library/ms175156(v=sql.105).aspx

    取分けデータの追跡をしたいスクリプト:
    CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT)
        RETURNS @TREE TABLE
    (
        empid   INT NOT NULL
        ,empname VARCHAR(25) NOT NULL
        ,mgrid   INT NULL
        ,lvl     INT NOT NULL
    )
    AS
    BEGIN
      WITH Employees_Subtree(empid, empname, mgrid, lvl)
      AS
      (
        -- Anchor Member (AM)
        SELECT empid, empname, mgrid, 0
        FROM Employees
        WHERE empid = @empid

        UNION all
       
        -- Recursive Member (RM)
        SELECT e.empid, e.empname, e.mgrid, es.lvl+1
        FROM Employees AS e
          JOIN Employees_Subtree AS es
            ON e.mgrid = es.empid
      )
      INSERT INTO @TREE
        SELECT * FROM Employees_Subtree;

      RETURN
    END
    GO

    2017年7月17日 14:58

すべての返信

  • 今回の質問内容はAPPLYを使用したデータの流れを確認したい
    という件と受け取りました。
    文面を見る限り「SQL Server Profiler」を使用してのデータの流れを
    確認したいと受け取っております。

    私も「SQL Server Profiler」は使用したことはありませんが
    次の手順にてAPPLYを確認してみました。

    想像された通りの確認ができるかは、テストしたキャプチャより、ご判断ください。
    私見となりますが、「SQL Server Profiler」の記述を書いているため
    この質問を見ている他のユーザは「SQL Server Profiler」を主軸として見ていると思われます。
    私はSQL Server Profiler以外のツールなどでも構わないと受け取っておりますが、
    もしかすると、その旨を強調したほうがいいのかもしれません。

    [SQL Server Profilerを併用したAPPLY確認手順概要]
    ------------------------------------------------------------------------
    1.データベースの互換性レベルを90以上に設定します。
    2.SQL Server Profilerを起動します。
    3.トレースのプロパティ画面が開くので実行します。
    4.SQL Queryから今回のマイクロソフトさんのサンプルのAPPLYを順次実行します。
    ------------------------------------------------------------------------

    [テスト環境]
    Windows Server 2016 Standard Evaluation 64bit
    SQL Server 2016 試用版

    ------------------------------------------------------------------------
    1.データベースの互換性レベルを90以上に設定します。
    ------------------------------------------------------------------------
    APPLYの利用にはデータベースの互換性レベルを90が必要と記述がありました。
    私のテスト環境は互換性レベルの最低限が100であったため90を超えていました。
    念のためSQL Server2016の130に値を合わせてテストしています。


    ------------------------------------------------------------------------
    2.SQL Server Profilerを起動します。
    ------------------------------------------------------------------------

    ------------------------------------------------------------------------
    3.トレースのプロパティ画面が開くので実行します。
    ------------------------------------------------------------------------
    (1)トレース名を入力し
    (2)トレースしたい内容にチェックを入れます。
    (3)実行ボタンをクリックします。


    SQLのみを確認したい場合は次のチェックのみでいいようです。
    ・SQL:BatchCompleted
    ・SQL:BatchStarting

    ------------------------------------------------------------------------
    4.SQL Queryから今回のマイクロソフトさんのサンプルのAPPLYを順次実行します。
    ------------------------------------------------------------------------

    2017/07/24 05:40 mod sta -----------------------------------------------
    [変更内容]冗長部分の削除および修正しました。

    SQL QueryからAPPLYのSQLを順次実行します。


    マイクロソフトさんが提示したサンプルをSQLQueryで確認したキャプチャとなります。

    SQLQueryで「set showplan_xml on」を使用したケース
    ※リンクをクリックして実行計画のクエリコストを表示
    2017/07/24 05:40 mod end -----------------------------------------------

            

    2017/07/23 05:40 add sta -----------------------------------------------

    上記キャプチャにあるように、クラスター化インデックスのテーブルのプロパティのシークキー等を
    使用するとAPPLY内部の各メンバーのデータを確認できると思ったのですが
    今回の例でいう「Recursive Member (RM)」の方がわかりませんでした。

    2017/07/24 05:40 add sta -----------------------------------------------
    「Recursive Member (RM)」のクエリコストを確認すると次のように表示されます。


    「実行プランのXMLの表示」をみると次のように表示されています。


    各クエリのプロパティに表示されている定義が実行プランのXMLで定義されていることを確認いただけると思います。
    (例)
    ScalarString="[empid] = [Recr1012],[empname] = [Recr1013],[mgrid] = [Recr1014],[lvl] = RaiseIfNullInsert([Recr1015])"

    実行プランのXMLと視覚化されたクエリの実行計画およびプロパティの関連性を精査して
    fn_getsubtreeで使用されている内部テーブルの
    「Recursive Member (RM)」を確認するSQLを作るのは、ひと手間いると思いました。
    また、ご希望されているのは簡単に内部テーブルを確認する方法ではと思っています。

    2017/07/24 05:40 add end -----------------------------------------------

    そのため、APPLYの内部データ検証としては下記のようにプロシージャを分け、
    SQL Server Profilerを使用せず、
    「Anchor Member (AM)」の差分であることを利用した「Recursive Member (RM)」の結果を取得する方法しか
    思いつきませんでした。

    CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT)
        RETURNS @TREE TABLE
    (
        empid   INT NOT NULL
        ,empname VARCHAR(25) NOT NULL
        ,mgrid   INT NULL
        ,lvl     INT NOT NULL
    )
    AS
    BEGIN
      WITH Employees_Subtree(empid, empname, mgrid, lvl)
      AS
      (
            -- Anchor Member (AM)
            SELECT empid, empname, mgrid, 0
            FROM Employees
            WHERE empid = @empid
    
            UNION all
    
            -- Recursive Member (RM)
            SELECT e.empid, e.empname, e.mgrid, es.lvl+1
            FROM Employees AS e
              JOIN Employees_Subtree AS es
                ON e.mgrid = es.empid
    
          )
          INSERT INTO @TREE
            SELECT * FROM Employees_Subtree;
    
          RETURN
        END
        GO
    
    CREATE FUNCTION dbo.fn_getsubtree2(@empid AS INT)
        RETURNS @TREE TABLE
    (
        empid   INT NOT NULL
        ,empname VARCHAR(25) NOT NULL
        ,mgrid   INT NULL
        ,lvl     INT NOT NULL
    )
    AS
    BEGIN
      WITH Employees_Subtree(empid, empname, mgrid, lvl)
      AS
      (
            -- Anchor Member (AM)
            SELECT empid, empname, mgrid, 0
            FROM Employees
            WHERE empid = @empid
    
          )
          INSERT INTO @TREE
            SELECT * FROM Employees_Subtree;
    
          RETURN
        END
        GO
    
    -- APPLYの結果
    SELECT D.deptid, D.deptname, D.deptmgrid
        ,ST.empid, ST.empname, ST.mgrid
    FROM Departments AS D
        CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST
    ;
    -- Anchor Member (AM)の結果
    SELECT D.deptid, D.deptname, D.deptmgrid
        ,ST.empid, ST.empname, ST.mgrid
    FROM Departments AS D
        CROSS APPLY fn_getsubtree2(D.deptmgrid) AS ST
    ;
    
    -- Recursive Member (RM)の結果
    SELECT tbl1.deptid, tbl1.deptname, tbl1.deptmgrid
        ,tbl1.empid, tbl1.empname, tbl1.mgrid
    from
    (
    SELECT D.deptid, D.deptname, D.deptmgrid
        ,ST.empid, ST.empname, ST.mgrid
    FROM Departments AS D
        CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST
    ) tbl1
    left join
    (
    SELECT D.deptid, D.deptname, D.deptmgrid
        ,ST.empid, ST.empname, ST.mgrid
    FROM Departments AS D
        CROSS APPLY fn_getsubtree2(D.deptmgrid) AS ST
    ) tbl2
    on
    tbl1.empid=tbl2.empid
    and tbl1.deptid=tbl2.deptid
    where
    tbl2.empid is null

    [補足]
    探してみたところ海外で次のようなツールがありました。
    SQL Profilerに似ている機能と思います。
    ただし、APPLYの内部データの確認ができるかについてはわかりかねます。

    SentryOne
    [URL]
    https://www.sentryone.com/plan-explorer

    また、既に見つけられたとは思われますがSQL Server Profilerの参考リンクとして次のものを見つけました。

    【SQL Server】クエリの実行プランとパフォーマンス情報の取得方法Part1

    【SQL Server】クエリの実行プランとパフォーマンス情報の取得方法Part2

    2017/07/23 05:40 add end -----------------------------------------------

    SQL Server Profilerの他の機能および応用はわかりかねるため、必要に応じて
    お調べいただき、不明な点については別スレッドに質問していただくか
    他のユーザの方々にご教授いただければと思います。












    2017年7月21日 20:48