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

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

SQL Server のチューニングについてまとめてみる - その20 - ( きちんとパラメータ化してみる )

 本日、大阪に来ています。天気は良くありません。お昼から雨のようですね。
f:id:koogucc11:20160924100003j:plain

 色々なシステムの SQL Server に関するレビューをしていると、まだまだパラメータ化されていないクエリが多数存在します。それによる影響としては、

  • クエリのパターンが毎回変化するため、その都度 SQL Server 上でリコンパイルが走ってしまい、サーバに負荷をかけてしまう。
  • キャッシュプランがクエリのパターン数分キャッシュされることになり、キャッシュとして無駄なものをサーバに抱えてしまう。

などが挙げられます。

 よくあるパターンとしては、下記のような関数を作成し、クエリをプログラムで連結したものを渡すというパターンだと思います。
SqlCommand.ExecuteNonQuery メソッド (System.Data.SqlClient)

private static void CreateCommand(string queryString,
    string connectionString)
{
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}

 上記のようなことをやってしまうと、キャッシュプランが下図のように恐ろしい状態になります。(延々と続く同じようなクエリ....)
f:id:koogucc11:20160924102358p:plain

 これを解消するには、下記の3パターンあります。

  • SqlCommand.Parameters プロパティ (System.Data.SqlClient)

     SqlParameterCollection クラス (System.Data.SqlClient) を使用します。サンプルは下記の通りです。

    private static void UpdateDemographics(Int32 customerID,
        string demoXml, string connectionString)
    {
        // Update the demographics for a store, which is stored 
        // in an xml column. 
        string commandText = "UPDATE Sales.Store SET Demographics = @demographics "
            + "WHERE CustomerID = @ID;";
    
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(commandText, connection);
            command.Parameters.Add("@ID", SqlDbType.Int);
            command.Parameters["@ID"].Value = customerID;
    
            // Use AddWithValue to assign Demographics.
            // SQL Server will implicitly convert strings into XML.
            command.Parameters.AddWithValue("@demographics", demoXml);
    
            try
            {
                connection.Open();
                Int32 rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine("RowsAffected: {0}", rowsAffected);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
    

  • sp_executesql (Transact-SQL)

     sp_executesql を使用します。サンプルは下記の通りです。

    DECLARE @IntVariable int;  
    DECLARE @SQLString nvarchar(500);  
    DECLARE @ParmDefinition nvarchar(500);  
      
    /* Build the SQL string one time.*/  
    SET @SQLString =  
         N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID  
           FROM AdventureWorks2012.HumanResources.Employee   
           WHERE BusinessEntityID = @BusinessEntityID';  
    SET @ParmDefinition = N'@BusinessEntityID tinyint';  
    /* Execute the string with the first parameter value. */  
    SET @IntVariable = 197;  
    EXECUTE sp_executesql @SQLString, @ParmDefinition,  
                          @BusinessEntityID = @IntVariable;  
    /* Execute the same string with the second parameter value. */  
    SET @IntVariable = 109;  
    EXECUTE sp_executesql @SQLString, @ParmDefinition,  
                          @BusinessEntityID = @IntVariable;  
    

※パラメータについては、ここを参照してください。

データベースのすべての SELECT、INSERT、UPDATE、および DELETE ステートメントをパラメータ化するように指定することで、SQL Server の「簡易パラメータ化」の既定動作を無効にできますが、いくつかの制約があります。強制パラメータ化は、ALTER DATABASE ステートメントで PARAMETERIZATION オプションを FORCED に設定することで有効になります。強制パラメータ化を行うと、クエリをコンパイルおよび再コンパイルする頻度を緩和できるので、データベースによってはパフォーマンスが向上します。一般的に POS (point-of-sale) などのアプリケーションから大量のクエリが同時に実行されるデータベースは、強制パラメータ化によりパフォーマンスが向上します。

 
 プログラムを変更してなくても、パラメータ化されます。しかし、万能ではないので、きちんとプログラムで考慮することをおすすめします。強制パラメータ化の副作用については過去にもいくつか触れていますので、参考にしてください。
ryuchan.hatenablog.com
ryuchan.hatenablog.com

 パラメータ化することにより、CPU、メモリの負荷を下げることができるので、積極的に対応しましょう。

大阪出張最近多いかも。

大阪さんぽ (散歩の達人MOOK)

大阪さんぽ (散歩の達人MOOK)