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

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

　色々なシステムの 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();
    }
}

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

　これを解消するには、下記の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)