Database

SQL Server Query Plan Cache Behavior

30-Jan-16 23:57

Here's[^] a research document from Microsoft on the subject.
The tl;dr is that it's size dependent.
For smaller files it's better using a database, for larger files it's better using the file system. And the breakeven is somewhere around 0.1 - 1 MB, depending on your system. Possibly larger nowadays since there has been a bit of development since the paper was written.

Wrong is evil and must be defeated. - Jeff Ello

Foothill28-Jan-16 10:18

28-Jan-16 10:18

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:

SQL

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(); }

Jörgen Andersson29-Jan-16 3:49

29-Jan-16 3:49

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

Foothill29-Jan-16 4:14

29-Jan-16 4:14

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<string> columns = new List<string>();
List<string> variables = new List<string>();
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(); }

Jörgen Andersson29-Jan-16 4:25

29-Jan-16 4:25

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

Foothill29-Jan-16 4:32

29-Jan-16 4:32

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(); }

Jörgen Andersson29-Jan-16 4:33

29-Jan-16 4:33

Yes.

Wrong is evil and must be defeated. - Jeff Ello

Foothill29-Jan-16 4:45

29-Jan-16 4:45

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(); }

Richard Deeming29-Jan-16 5:10

Richard Deeming

29-Jan-16 5:10

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

Foothill29-Jan-16 5:15

29-Jan-16 5:15

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(); }

Jörgen Andersson29-Jan-16 10:45

29-Jan-16 10:45

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

Foothill29-Jan-16 11:17

29-Jan-16 11:17

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(); }

Jörgen Andersson29-Jan-16 11:42

29-Jan-16 11:42

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

Foothill29-Jan-16 12:51