このトピックはまだ評価されていません - このトピックを評価する

SQL Server 2005 での SQLCLR を使用した XML プラン表示の処理

Arun Marathe
Boris Baryshnikov

Microsoft Corporation

April 2005
日本語版最終更新日 2005 年 11 月 10 日

対象 :
Microsoft SQL Server 2005
Microsoft Visual C# .NET

概要 : XML プラン表示からクエリの推定実行コストを抽出するアプリケーションをビルドします。ユーザーは、コストが SQL Server 2005 を実行しているサーバーに対して事前に決めたしきい値未満になるクエリしか送信できません。その結果、コストが高く、実行時間の長いクエリによってサーバーが過負荷状態にならないことが保証されます。

はじめに
 目標と対象ユーザー
 問題の定義
 ソリューション 1: CLR ストアドプロシージャとインプロセスデータアクセスを使用したクエリコストの抽出
 ソリューション 2: CLR ストアドプロシージャと XQuery 式を使用したクエリコストの抽出
 まとめ
 付録 A: "ShowplanXPath.cs" (ソリューション 1) のコードリスト
 付録 B: "ReturnShowplanXML.cs" (ソリューション 2) のコードリスト

はじめに

Microsoft SQL Server 2005 では、XML 形式で使用できるクエリ実行プラン ("プラン表示" とも呼ばれます) が作成されます。この XML プラン表示は、XPath、XQuery、 XSLT などの XML テクノロジを使用して処理できます。この資料では、XML プラン表示からクエリの推定実行コストを抽出するアプリケーションについて説明します。抽出したコストは Transact-SQL ウィンドウで使用できます。この技法を使用することにより、ユーザーはコストが SQL Server を実行しているサーバーに対して事前に決めたしきい値未満になるクエリしか送信できなくなります。その結果、コストが高く、実行時間の長いクエリによってサーバーが過負荷状態にならないことが保証されます。

ページのトップへ

目標と対象ユーザー

この資料は、SQL Server の開発者とデータベース管理者 (DBA) の両方を対象としています。データベース管理者向けに、共通言語ランタイム (SQLCLR) についても簡単に説明しています。プラン表示を抽出するアプリケーションでは、小さな Microsoft Visual C# .NET プログラムを 2 つ使用します。この資料では、これらのプログラムをコンパイルすることによって生成される DLL を、SQL Server から呼び出す方法について詳しく説明します。アプリケーションでは、クエリ送信と XML データからの情報の抽出を行うのに使用できる XPath と XQuery テクノロジも使用します。SQL Server 2005 には、これら 2 つのクエリ言語に対するサポートが組み込まれています。この資料では、これらの言語と Transact-SQL をシームレスに相互運用する方法を例を挙げて示します。

ページのトップへ

問題の定義

SQL Server の DBA は、業務がピークの時間帯にユーザーが実行時間の長いクエリをサーバーに送信することで、サーバーの応答が遅くなっている状況に気付くことがあります。このような状況は、次の 2 つの方法で防ぐことができます。

DBA は sp_configure を使用して、query governor cost limit オプションに特定のしきい値を設定することができます (これは詳細設定オプションです)。このしきい値はサーバー全体に影響します。
DBA は SET QUERY_GOVERNOR_COST_LIMIT ステートメントを使用して、接続に影響するしきい値を適用できます。

さらに細かい制御を必要とするシナリオも想像することができます。たとえば、機能が同じで構文が異なる 3 つのクエリがあり、最も高速に実行できそうな形式のクエリを自動的に送信します。また、推定実行コストが特定のしきい値を超えるクエリはすべて実行できないようにします。プログラムからクエリコストにアクセスし、推定実行コストに基づいてクエリ送信プロセスを制御することによって、ユーザーはサーバーに影響の少ないアプリケーションをビルドできます。

この資料で説明する技法を使えば、SQLCLR のユーザー定義プロシージャ、XPath、XQuery、Visual C# などのテクノロジを使用して、プログラムからクエリの推定実行コストにアクセスできます。ここで説明する、ユーザー定義プロシージャから SQLCLR を使って SQL Server 2005 にアクセスする基本的な技法は、他のアプリケーションでも使用できます。

SQL Server 2005 では、Microsoft Visual Basic .NET や Visual C# など、.NET Framework で使用できる任意のプログラミング言語を使用して、ユーザー定義型、関数、プロシージャ、および集計を定義することができます。ユーザー定義のエンティティを定義すると、概念的には SQL Server 自体で提供されるエンティティと同じように SQL Server でそのエンティティを使用できます。たとえば、ユーザー定義型 T を定義した後、型 T の列を含むリレーショナルテーブルを定義することができます。ユーザー定義プロシージャ P を定義した後、Transact-SQL プロシージャと同じように、EXEC P を使用してこのプロシージャを呼び出すこともできます。

ページのトップへ

ソリューション 1: CLR ストアドプロシージャとインプロセスデータアクセスを使用したクエリコストの抽出

このソリューションを実装する方法

.NET Framework 言語 (この資料では Visual C# を使用します) で、特定のクエリの XML プラン表示からクエリコストを取得するストアドプロシージャを定義します。
SQL Server を実行しているサーバーにプロシージャを登録します。これには次の 2 つの手順が必要です。
1. SQL Server にアセンブリを登録します。
2. 外部の CLR メソッドを参照するストアドプロシージャを作成します。
  
  図 1. SQLCLR でのユーザー定義ストアドプロシージャの実装と登録の手順

ユーザー定義 CLR ストアドプロシージャを作成する際の概略図を図 1 に示します。ソリューションは次の手順で順を追って説明します。

「付録 A」には、SQL Server を実行しているサーバーから XML 形式でプラン表示を抽出する Visual C# プログラム (ShowplanXPath.cs) を含めました。このプログラムでは、取得したプラン表示で XPath 式を実行し、クエリの推定実行コストを抽出します。最初の手順では、Visual C# コンパイラを使用してプログラムをコンパイルし、DLL (ShowplanXPath.dll) を生成します。コンパイルには、次のコマンドラインを使用できます。結果として、ShowplanXPath.dll という DLL が生成されます。
```
<path-to-.NET-framework>\csc.exe 
/out:ShowplanXPath.dll
/target:library 
/reference:<path-to-.NET-framework>\System.dll
/reference:<path-to-.NET-framework>\System.Data.dll
/reference:<path-to-SQL-Server-installation>\sqlaccess.dll
ShowplanXPath.cs
```
<path-to-.NET-framework> の部分は、次のように Microsoft .NET Framework の場所への正しいパスに変更します。

C:\WINNT\Microsoft.NET\Framework\v2.0.40607

または、システム環境変数の PATH を追加します。"v2.0.40607" は、コンピュータにインストールされている .NET Framework のバージョンによって異なるので注意してください。<path-to-SQL-Server-installation> の部分は、次のように SQL Server 2005 のバイナリがインストールされている場所への正しいパスに変更します。

"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\"

パスにスペースが含まれている場合は、上記のように二重引用符で囲みます。
次に、SQL Server 2005 Management Studio などのクライアントから、次の Transact-SQL コマンドを実行して、そのアセンブリ (ShowplanXPath.dll) を SQL Server 2005 から認識できるようにします。
```
use AdventureWorks 
go
 CREATE ASSEMBLY ShowplanXPath 
FROM '<path-to-compiled-DLL>\ShowplanXPath.dll' 
go
```
<path-to-compiled-DLL> の部分を、最初の手順でコンパイルした DLL の場所へのパスに変更します。
登録済みのアセンブリ (ShowplanXPath.dll) 内の外部 CLR メソッドを参照するユーザー定義ストアドプロシージャを作成します。
```
CREATE PROCEDURE dbo.GetXMLShowplanCost
(
   @tsqlStmt   NVARCHAR(MAX),
   @queryCost   NVARCHAR(MAX) OUT
)
AS EXTERNAL NAME ShowplanXPath.xmlshowplanaccess.GetXMLShowplan
go
```
外部名は、assembly_name.class_name.method_name のように論理的に形成されることに注意してください。@tsqlStmt パラメータにクエリを含めると、そのクエリのコストが @queryCost OUT パラメータに返されます。

次のコードを使用して、クライアントから CLR ユーザー定義ストアドプロシージャを呼び出します。

DECLARE @query nvarchar(max)   -- the query
DECLARE @cost nvarchar(max)    -- its estimated execution cost
-- set this to your query
set @query = N'select * from person.address'
-- execute the procedure
EXEC dbo.GetXMLShowplanCost @query, @cost OUTPUT
select @cost      -- print the cost
-- note that @cost is nvarchar, we use explicit comparison in case of an error 
-- and implicit conversion for actual cost
if (@cost != '-1') and (@cost <= 0.5)   -- if query is cheap to execute,
  EXEC (@query)         -- execute it; else don't execute
                  -- replace 0.5 with your own threshold
go

@query 変数で一連のクエリ (バッチ) を送信すると、そのバッチの合計コストが返されることに注意してください。クエリまたはバッチにエラーが含まれる場合は、コストに "-1" が返されます。「付録 A」の例外処理コードを適宜変更して、エラーが発生した場合のニーズに対応してください。

クエリの推定実行コストは、出力パラメータ @cost を使用してクライアントに返されます。これを手順 4. のコード例で示します。
手順 4. のコードで示すように、クライアントは @cost の値に基づいて実行するクエリを選択し、SQL Server に送信します。

ms345130.xmlshowplans_02(ja-jp,SQL.90).gif

図 2. CLR ストアドプロシージャ実行時の処理手順の概略

ストアドプロシージャ実行中に行う主な手順を図 2 に示します。詳細についてはこの後説明します。

プロシージャが呼び出されると、コストを推定するクエリを受け取ります。
CLR ストアドプロシージャで SHOWPLAN_XML モードを ON に設定します。この接続で送信されたステートメントはどれも実行されず、代わりにプラン表示が作成されます。クエリ自体は SQL Server に送信されます。
サーバーからはプラン表示が XML 形式で 1 つずつ返されるので、Visual C# プログラムでこれを 1 つにまとめます。
プロシージャで SHOWPLAN_XML モードを OFF に設定します。
CLR ストアドプロシージャでは、XML 形式のプラン表示で XPath 式を準備および実行し、クエリのコストを抽出します。バッチ内のすべてのステートメントについて、すべてのクエリプランが抽出され、集計されます。
クエリの推定実行コストが呼び出し元に返されます。SQL コードでエラーが発生した場合は、コストに "-1" が返されます。

注意 DLL は SQL Server プロセスにリンクされるので、SQL Server との DLL 通信はインプロセスデータアクセスと呼ばれます。DLL は SQL Server プロセスと動的にリンクされたので、交換されるデータは SQL Server プロセスの境界を越えることはありません。インプロセスデータアクセスを行うときは、XPath クエリのみを SQL Server に送信できます。XQuery クエリではインプロセスデータアクセスを使用できません。

ページのトップへ

ソリューション 2: CLR ストアドプロシージャと XQuery 式を使用したクエリコストの抽出

このソリューションのプロセスの各手順は、前のソリューション (ソリューション 1) に似ていますが、いくつか大きな違いがあります。ソリューション 2 では、追加の処理を行わなくても特定のクエリのプラン表示が XML 形式で CLR ストアドプロシージャから返されます。クライアントでは XQuery 式を使用して、返された XML プラン表示からクエリの推定コストを抽出します。

このソリューションを実装する方法

「付録 B」には、プラン表示を SQL Server から XML 形式で抽出してクライアントに返す Visual C# プログラムを含めています。ソリューション 1 の最初の手順と同様に、次のコマンドラインを使用して、このプログラムを DLL にコンパイルできます。このコマンドで、ReturnShowplanXML.dll という DLL が生成されます。
```
<path-to-.NET-framework>\csc.exe 
/out:ReturnShowplanXML.dll
/target:library 
/reference:<path-to-.NET-framework>\System.dll
/reference:<path-to-.NET-framework>\System.Data.dll
/reference:<path-to-SQL-Server-installation>\sqlaccess.dll
ReturnShowplanXML.cs
```
前のソリューションの最初の手順と同様に、<path-to-.NET-framework> と <path-to-SQL-Server-installation> はそれぞれ、Microsoft .NET Framework の場所と SQL Server 2005 のバイナリがインストールされた場所への正しいパスに変更する必要があります。
続いて、次の Transact-SQL コマンドを使用して、アセンブリ (ReturnShowplanXML.dll) を SQL Server 2005 から認識できるようにします。この Transact-SQL コマンドは、SQL Server 2005 Management Studio などのクライアントから実行します。
```
use AdventureWorks
go
CREATE ASSEMBLY ReturnShowplanXML 
FROM '<path-to-compiled-DLL>\ReturnShowplanXML.dll'
go
```
<path-to-compiled-DLL> を、この説明の手順 1. で DLL をコンパイルした場所へのパスに変更します。
登録済みアセンブリ (ReturnShowplanXML.dll) 内の外部 CLR メソッドを参照するユーザー定義ストアドプロシージャを作成します。
```
CREATE PROCEDURE dbo.ReturnXMLShowplan
(
   @tsqlStmt   NVARCHAR(MAX), 
   @retPlanXML   NVARCHAR(MAX) OUT
)
AS EXTERNAL NAME ReturnShowplanXML.xmlshowplanaccess.GetXMLShowplan
go
```
@tsqlStmt パラメータにクエリを含めると、XML 形式のプラン表示が OUT パラメータ @retPlanXML に返されます。

次のようなコードを使用して、クライアントから CLR ユーザー定義プロシージャを呼び出します。

-- @shplan will contain the showplan in XML format
DECLARE @shplan nvarchar(max)
-- @query will contain the query whose cost is to be estimated
DECLARE @query nvarchar(max)
-- set this to your query
set @query = N'select * from person.address'
EXEC dbo.ReturnXMLShowplan @query, @shplan OUTPUT
DECLARE @querycost float
DECLARE @threshold float
set @threshold = 0.5
-- extract query cost using XQuery
select @querycost = cast(@shplan as xml).value 
('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
    (//p:RelOp)[1]/@EstimatedTotalSubtreeCost', 'float')
select @querycost
if ( @querycost <= @threshold ) -- if the cost is within limit, 
  EXEC (@query)           -- execute the query; else don't
go

クエリにエラーが含まれている場合は、プラン表示の代わりに XML コードとして <error>text of the exception</error> が返されます。「付録 B」のコードの例外処理の部分を、必要に応じて適宜変更します。

XML 形式のプラン表示が、出力パラメータ @shplan を使用してクライアントに返されます。その後クライアントでは、このプラン表示と、推定実行コストをプラン表示から抽出する XQuery 式を SQL Server に送信します。
サーバーから応答が返され、変数 @querycost にクエリコストが返されます。
コストがしきい値未満の場合は、クライアントからサーバーに実行用にクエリを送信します。

図 3. ソリューション 2 の処理手順の概略 (図 2 と比較してください。手順番号は必ずしも一致していません。)

図 3 にこのソリューションの処理手順の概要を示します。この方法では、次の 2 つの重要な点に注目する必要があります。

XML プラン表示の抽出は、ソリューション 1 と同様にインプロセスです。

注意プラン表示がクライアントプロセスに送信されたので、XQuery 式を使用したクエリコストの抽出ではインプロセスデータアクセスを使用しません。そのため、クライアントではプラン表示からクエリコストを抽出する XQuery 式と共にそのプラン表示を再送信します。
アウトプロセスのデータアクセスの場合は、SQL Server では XPath クエリだけでなく XQuery クエリもサポートされます。したがって、より表現の豊かなクエリを XML プラン表示の処理に使用できます。この方法ではプラン表示が接続に 2 回送信されるので、ソリューション 1 ほど効果的ではありません。

ページのトップへ

まとめ

SQL Server 2005 の SQLCLR 機能を使用することで、XPath や XQuery 言語を使用して、XML 形式のプラン表示を処理することができます。XPath エンジンと XQuery エンジンは SQL Server 2005 に組み込まれているので、これらのエンジンと Transact-SQL のシームレスな統合が可能になります。Transact-SQL と XPath や XQuery との間のリンクとして機能する CLR ユーザー定義プロシージャを実装する Visual C# コードは比較的単純になります。SQLCLR は Transact-SQL の機能を大幅に拡張します。CPU を集中的に使用する計算を Visual C# や Visual Basic .NET などの手続き型の言語を使用して効率的に実装できます。

ページのトップへ

付録 A: "ShowplanXPath.cs" (ソリューション 1) のコードリスト

using System;
using System.IO;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Xml;
using System.Xml.XPath;
public class xmlshowplanaccess 
{
    public static void GetXMLShowplan(string tsqlStmt, ref string tsqlStmtCost) 
    {
        // tsqlStmt contains the query whose cost needs to be calculated
        // tsqlStmtCost will contain the tsqlStmt's cost
        // Open a connection and create a command
        SqlConnection conn = new SqlConnection("context connection = true");
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = "set showplan_xml on";
        cmd.ExecuteNonQuery();  // turns showplan_xml mode on
        cmd.CommandText = tsqlStmt;
        try {
            // thePlan will contain the showplan in XML format
            String thePlan = String.Empty;
            SqlDataReader sdr = cmd.ExecuteReader();
            // In case the result set is chunked, we concatenate
            while (sdr.Read()) thePlan += sdr.GetSqlString(0).ToString();
            sdr.Close();
            cmd.CommandText = "set showplan_xml off";
            cmd.ExecuteNonQuery();  // turns showplan_xml mode off
            // Now the showplan in XML format is contained in thePlan.
            // We shall now evaluate an XPath expression against the showplan.
            StringReader strReader = new StringReader(thePlan);
            System.Xml.XmlTextReader xreader = 
                new System.Xml.XmlTextReader(strReader);
            XPathDocument doc = new XPathDocument(xreader, XmlSpace.Preserve);
            System.Xml.XPath.XPathNavigator navigator = doc.CreateNavigator();
            XmlNamespaceManager nsmgr = new XmlNamespaceManager(navigator.NameTable);
            nsmgr.AddNamespace("sql", "http://schemas.microsoft.com/sqlserver/2004/07/showplan");
            // The exact namespace will depend on the showplan's version.
            // Please modify the year and month appropriately.
            XPathExpression xpression;
            // The XPath that points to the estimated execution cost of the query
            xpression = 
                navigator.Compile("//sql:Batch/sql:Statements/sql:StmtSimple/" 
                + "sql:QueryPlan[1]/sql:RelOp[1]/@EstimatedTotalSubtreeCost");
            xpression.SetContext(nsmgr);
            XPathNodeIterator iterator = navigator.Select(xpression);
            String val = String.Empty;
            System.Single totalCost = 0;
            // sum costs of all query plans in this batch
            while(iterator.MoveNext()) totalCost += Single.Parse(iterator.Current.Value);
            tsqlStmtCost = totalCost.ToString();  // set the return value
        } catch (SqlException) { // return -1 if there are any errors in SQL code
            tsqlStmtCost = "-1";
        }
    }  // GetXMLShowplan ends
}  // xmlshowplanaccess class ends

ページのトップへ

付録 B: "ReturnShowplanXML.cs" (ソリューション 2) のコードリスト

using System;
using System.IO;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
public class xmlshowplanaccess 
{
    public static void GetXMLShowplan(string tsqlStmt, ref string tsqlStmtShowplan) 
    {
        // tsqlStmt contains the statement whose showplan needs to be returned
        // tsqlStmtShowplan will return the showplan of tsqlStmt in XML format
        // Open a connection and create a command
        SqlConnection conn = new SqlConnection("context connection = true");
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = "set showplan_xml on";
        cmd.ExecuteNonQuery();  // turn the showplan_xml mode on
        cmd.CommandText = tsqlStmt;
        try 
        {
            // thePlan will contain the showplan in XML format
            String thePlan = String.Empty;
            SqlDataReader sdr = cmd.ExecuteReader();
            // In case the result set is chunked, concatenate
            while (sdr.Read()) 
                thePlan += sdr.GetSqlString(0).ToString();
            sdr.Close();
            cmd.CommandText = "set showplan_xml off" ;
            cmd.ExecuteNonQuery();  // turn the showplan_xml mode off
            tsqlStmtShowplan = thePlan;  // return the showplan in XML format
        } 
        catch (SqlException e) // return well formed xml document with the text of exception
        {
            tsqlStmtShowplan = "<error>" + e.ToString() + "</error>";
        }
    }  // GetXMLShowplan ends
}  // xmlshowplanaccess ends

著作権

このドキュメントは暫定版であり、このソフトウェアの最終的な製品版の発売時に実質的に変更されることがあります。

このドキュメントに記載されている情報は、このドキュメントの発行時点におけるマイクロソフトの見解を反映したものです。変化する市場状況に対応する必要があるため、このドキュメントは、記載された内容の実現に関するマイクロソフトの確約とはみなされないものとします。また、発行以降に発表される情報の正確性に関して、マイクロソフトはいかなる保証もいたしません。

このホワイトペーパーに記載された内容は情報の提供のみを目的としており、明示、黙示または法律の規定にかかわらず、これらの情報についてマイクロソフトはいかなる責任も負わないものとします。

お客様ご自身の責任において、適用されるすべての著作権関連法規に従ったご使用を願います。このドキュメントのいかなる部分も、米国 Microsoft Corporation の書面による許諾を受けることなく、その目的を問わず、どのような形態であっても、複製または譲渡することは禁じられています。ここでいう形態とは、複写や記録など、電子的な、または物理的なすべての手段を含みます。ただしこれは、著作権法上のお客様の権利を制限するものではありません。

マイクロソフトは、このドキュメントに記載されている内容に関し、特許、特許申請、商標、著作権、またはその他の無体財産権を有する場合があります。別途マイクロソフトのライセンス契約上に明示の規定のない限り、このドキュメントはこれらの特許、商標、著作権、またはその他の無体財産権をお客様に許諾するものではありません。

別途記載されていない場合、このソフトウェアおよび関連するドキュメントで使用している会社、組織、製品、ドメイン名、電子メールアドレス、ロゴ、人物、場所、出来事などの名称は架空のものです。実在する商品名、団体名、個人名などとは一切関係ありません。

Microsoft、Visual Basic .NET、および Visual C# は、米国 Microsoft Corporation の米国およびその他の国における登録商標または商標です。

記載されている会社名、製品名には、各社の商標のものもあります。

ページのトップへ

この情報は役に立ちましたか。はいいいえ

不正確

内容が浅い

もっとコード例が必要

翻訳品質の向上が必要

(残り 1500 文字)

SQL Server 2005 での SQLCLR を使用した XML プラン表示の処理

目次

はじめに

目標と対象ユーザー

問題の定義

ソリューション 1: CLR ストアド プロシージャと インプロセス データ アクセスを使用したクエリ コストの抽出

このソリューションを実装する方法

ソリューション 2: CLR ストアド プロシージャと XQuery 式を使用したクエリ コストの抽出

このソリューションを実装する方法

まとめ

付録 A: "ShowplanXPath.cs" (ソリューション 1) のコード リスト

付録 B: "ReturnShowplanXML.cs" (ソリューション 2) のコード リスト

著作権

ソリューション 1: CLR ストアドプロシージャとインプロセスデータアクセスを使用したクエリコストの抽出

ソリューション 2: CLR ストアドプロシージャと XQuery 式を使用したクエリコストの抽出

付録 A: "ShowplanXPath.cs" (ソリューション 1) のコードリスト

付録 B: "ReturnShowplanXML.cs" (ソリューション 2) のコードリスト