SQL Server Query Plan Cache Behavior
-
Long story short, after reviewing server statistics after a large data conversion, I noticed that parameterized queries were being stored in plan cache thousands of times. Isn't parametrizing your queries supposed to prevent that? Example offending query:
INSERT INTO AddressTable (ObjectId, IsMailing, Street1, Street2, City, State, PostalCode, County, Country)
VALUES (@ObjectId, @IsMailing, @Street1, @Street2, @City, @State, @PostalCode, @County, @Country);The ObjectId is a Foreign Key (INT), IsMailing is a boolean (BIT), and the rest are NVARCHAR(X). There were 38K+ records inserted into the table (one at a time) and there are ~3400 plans of this query cached in the plan cache. The conversion procedure is a custom application written in C# that generates the insert statements on the fly depending on what is being inserted but all the ad-hoc queries are identical for a given table. On a side note, the program was executing statements against the database across 70 to 80 separate threads concurrently (yay multi-threading). Basically, my question is why would identical parameterized queries executed thousands of times create thousands of separate query plans? And is there any settings to change to prevent that?
if (Object.DividedByZero == true) { Universe.Implode(); }
-
Long story short, after reviewing server statistics after a large data conversion, I noticed that parameterized queries were being stored in plan cache thousands of times. Isn't parametrizing your queries supposed to prevent that? Example offending query:
INSERT INTO AddressTable (ObjectId, IsMailing, Street1, Street2, City, State, PostalCode, County, Country)
VALUES (@ObjectId, @IsMailing, @Street1, @Street2, @City, @State, @PostalCode, @County, @Country);The ObjectId is a Foreign Key (INT), IsMailing is a boolean (BIT), and the rest are NVARCHAR(X). There were 38K+ records inserted into the table (one at a time) and there are ~3400 plans of this query cached in the plan cache. The conversion procedure is a custom application written in C# that generates the insert statements on the fly depending on what is being inserted but all the ad-hoc queries are identical for a given table. On a side note, the program was executing statements against the database across 70 to 80 separate threads concurrently (yay multi-threading). Basically, my question is why would identical parameterized queries executed thousands of times create thousands of separate query plans? And is there any settings to change to prevent that?
if (Object.DividedByZero == true) { Universe.Implode(); }
Let me guess, you're using AddWithValue() for your parameters? You should NEVER do that in production code. There will be a real parameter created from it where the settings of type and length will be taken from the object supplied. In other words, you'll get a new plan for every new length of an @address or @street and so on. Create proper parameters where the types and lengths are specified according to the fields in the tables. More info here[^].
Wrong is evil and must be defeated. - Jeff Ello
-
Let me guess, you're using AddWithValue() for your parameters? You should NEVER do that in production code. There will be a real parameter created from it where the settings of type and length will be taken from the object supplied. In other words, you'll get a new plan for every new length of an @address or @street and so on. Create proper parameters where the types and lengths are specified according to the fields in the tables. More info here[^].
Wrong is evil and must be defeated. - Jeff Ello
Not at all. Here's the watered down, abreviated version of the code that generates the INSERT statement. There's a lot more going on but this is the meat of it.
// The values to insert are contained in the Properties of container class, I'll call it IData for this example
List columns = new List();
List variables = new List();
string sqlStatement = null;// Dynamically create and execute a parameterized SqlStatement
using (SqlCommand command = new SqlCommand() { CommandType = CommandType.Text, Connection = connection })
{
// IData is a container class that holds the properties and the name of the table that the object is stored in
// The IProperty object contains the name of the column, the Sql Data Type to use, and the value to insert
foreach (IProperty prop in IData.Properties)
{
columns.Add(prop.TableColumnName);
variables.Add("@" + prop.TableColumnName);// Create the parameter
var currentParameter = new Parameter("@" + prop.TableColumnName, prop.SqlType);
currentParameter.Value = (objProp.Value == null) ? DBNull.Value : objProp.Value;
command.Parameters.Add(currentParameter);
}sqlStatement = String.Join(" ", new string[]
{
"INSERT INTO dbo." + IData.TableName,
"(" + String.Join(", ", columns.ToArray()) + ")",
"VALUES (" + String.Join(", ", variables.ToArray()) + ");"
});command.CommandText = sqlStatement;
command.ExecuteNonQuery();
}if (Object.DividedByZero == true) { Universe.Implode(); }
-
Not at all. Here's the watered down, abreviated version of the code that generates the INSERT statement. There's a lot more going on but this is the meat of it.
// The values to insert are contained in the Properties of container class, I'll call it IData for this example
List columns = new List();
List variables = new List();
string sqlStatement = null;// Dynamically create and execute a parameterized SqlStatement
using (SqlCommand command = new SqlCommand() { CommandType = CommandType.Text, Connection = connection })
{
// IData is a container class that holds the properties and the name of the table that the object is stored in
// The IProperty object contains the name of the column, the Sql Data Type to use, and the value to insert
foreach (IProperty prop in IData.Properties)
{
columns.Add(prop.TableColumnName);
variables.Add("@" + prop.TableColumnName);// Create the parameter
var currentParameter = new Parameter("@" + prop.TableColumnName, prop.SqlType);
currentParameter.Value = (objProp.Value == null) ? DBNull.Value : objProp.Value;
command.Parameters.Add(currentParameter);
}sqlStatement = String.Join(" ", new string[]
{
"INSERT INTO dbo." + IData.TableName,
"(" + String.Join(", ", columns.ToArray()) + ")",
"VALUES (" + String.Join(", ", variables.ToArray()) + ");"
});command.CommandText = sqlStatement;
command.ExecuteNonQuery();
}if (Object.DividedByZero == true) { Universe.Implode(); }
But you're still not defining the size as far as I can see, so it will be inferred from the value of the dbType parameter if it is not explicitly set in the size parameter.[^]
Wrong is evil and must be defeated. - Jeff Ello
-
But you're still not defining the size as far as I can see, so it will be inferred from the value of the dbType parameter if it is not explicitly set in the size parameter.[^]
Wrong is evil and must be defeated. - Jeff Ello
Ah, so the reason for the multitude of cached query plans is that when dealing with variable length text a new plan is created when a parameter's length is different from a previous plan? So basically, if I want the server to not create new plans, I need to define the size of the variable length parameters?
if (Object.DividedByZero == true) { Universe.Implode(); }
-
Ah, so the reason for the multitude of cached query plans is that when dealing with variable length text a new plan is created when a parameter's length is different from a previous plan? So basically, if I want the server to not create new plans, I need to define the size of the variable length parameters?
if (Object.DividedByZero == true) { Universe.Implode(); }
Yes.
Wrong is evil and must be defeated. - Jeff Ello
-
Yes.
Wrong is evil and must be defeated. - Jeff Ello
-
That makes sense. Does the Size represent the size of the data or the size of the field in the database? I would assume that's in bytes also.
if (Object.DividedByZero == true) { Universe.Implode(); }
Bytes for binary types; characters for string types: SqlParameter.Size Property (System.Data.SqlClient)[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Bytes for binary types; characters for string types: SqlParameter.Size Property (System.Data.SqlClient)[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Got it. Now all I have to do is weigh the overhead of calculating sizes (when and where) vs additional query plans in the cache. Thanks for helping to clear this up.
if (Object.DividedByZero == true) { Universe.Implode(); }
It would be pretty hard to calculate, since the correct size would be what the size of the field in the database table is defined as. So there are two ways to go if you want to keep generating your DML. Either you ask the database what the field sizes are, Or you define them in your classes using an Attribute on your properties. You get the SizeAttribute for a member using ((SizeAttribute)Member.GetCustomAttributes(typeof(SizeAttribute), true)[0]).FieldName;
Wrong is evil and must be defeated. - Jeff Ello
-
It would be pretty hard to calculate, since the correct size would be what the size of the field in the database table is defined as. So there are two ways to go if you want to keep generating your DML. Either you ask the database what the field sizes are, Or you define them in your classes using an Attribute on your properties. You get the SizeAttribute for a member using ((SizeAttribute)Member.GetCustomAttributes(typeof(SizeAttribute), true)[0]).FieldName;
Wrong is evil and must be defeated. - Jeff Ello
Not so much in this instance. For the above example, the IProperty object also includes a definition class which already has a MaxLength field, along with other database specific information, which is the size of the database field. I used that size first on text fields but noticed that some fields were getting extra empty characters appended to the end of the text to fill the size of the field, so I stopped using it. If I understand correctly, I should be passing the length of the data to the SqlParameter. The extra overhead I was referring to was casting the IData object to it's appropriate type (boxing and unboxing overhead) then getting the length from string.Length or byte[].Length. Trying to weigh which overhead has greater impact, code conversion or database query plans.
if (Object.DividedByZero == true) { Universe.Implode(); }
-
Not so much in this instance. For the above example, the IProperty object also includes a definition class which already has a MaxLength field, along with other database specific information, which is the size of the database field. I used that size first on text fields but noticed that some fields were getting extra empty characters appended to the end of the text to fill the size of the field, so I stopped using it. If I understand correctly, I should be passing the length of the data to the SqlParameter. The extra overhead I was referring to was casting the IData object to it's appropriate type (boxing and unboxing overhead) then getting the length from string.Length or byte[].Length. Trying to weigh which overhead has greater impact, code conversion or database query plans.
if (Object.DividedByZero == true) { Universe.Implode(); }
Foothill wrote:
I should be passing the length of the data to the SqlParameter.
Maxlength is what you want to pass[^], that you got empty character appended is something I've never heard of before. You didn't by any chance use Char instead of Varchar? Char gets padded to the size specified.
Foothill wrote:
Trying to weigh which overhead has greater impact, code conversion or database query plans.
Database query plans, without any doubts.
Wrong is evil and must be defeated. - Jeff Ello
-
Foothill wrote:
I should be passing the length of the data to the SqlParameter.
Maxlength is what you want to pass[^], that you got empty character appended is something I've never heard of before. You didn't by any chance use Char instead of Varchar? Char gets padded to the size specified.
Foothill wrote:
Trying to weigh which overhead has greater impact, code conversion or database query plans.
Database query plans, without any doubts.
Wrong is evil and must be defeated. - Jeff Ello
MaxLength it is and all the text fields are NVarchar(N) by default. Since I should use the MaxLength field, it would be trivial to add a couple of lines in the code that executes the INSERT statement to handle the variable length data. Thanks again.
if (Object.DividedByZero == true) { Universe.Implode(); }
