Usually, a database is meant to serve some kind of application, and .Net applications have a few possible ways to query the database.

In this post we will see how ADO.Net queries, NHibernate, Linq to SQL and Entity Framework query the database, and how it affects SQL Server Plan Cache (the religious war of whether stored procedures are good or bad is out of the scope of this post).

Stored Procedures vs Application Queries

What Is The Plan Cache?

When SQL Server receives a query to execute, it needs to find and generate an optimal plan for executing it.

The process of finding an optimal plan is expensive, so once the process is done, the generated plan is saved in a place called the Plan Cache for the purpose of reuse.

A lack of plan reuse can cause problems in a few aspects:

• CPU - Plan compilation is a CPU intensive task. It is true that modern CPU’s have made this aspect less problematic than it was a few years ago, but still, a query with a cached plan will execute faster than a query without such plan, and plan reuse will let the CPU handle other important tasks.
• Memory – The cached plans consume memory space. Redundant caching can lead to plans being thrown out of cache. Also, since the Plan Cache is a part of SQL Server’s Buffer Pool, expensive memory is used to cache redundant plans instead of caching data pages.
• Statistics – SQL Server gathers statistics about queries executed on the server, like execution duration, execution count and more. When a plan is dropped from the Plan Cache, we also lose the statistics of the queries that used it. In addition, if we have queries that are basically the same but get different plans because of lack of reuse, we end up with statistics that don’t show the whole picture.

Let’s look at ADO.NET queries, NHibernate, Linq To SQL and Entity Framework and see how they submit their queries to the database. We will focus on cases where string parameters sent from the application can prevent plan reuse, as this is one of the most common pitfalls today.

We will use SQL Server Profiler’s RPC:Completed event to see the queries and commands that are sent from the application.

ADO.NET Queries

This is the most naive and straightforward way to write queries in the application, and I assume there are plenty of applications out there that use it.

Take a look at the following code section:

SqlConnection con = new SqlConnection("Server=xxx;Database=Northwind;Trusted_Connection=True;");
con.Open();
string LastName = "Davolio";
string PostalCode = "98122";
SqlCommand command =
new SqlCommand("SELECT FirstName FROM Employees "
+"WHERE LastName=N'"+LastName
+"' AND PostalCode=N'"+PostalCode+"'");
command.Connection = con;
string FirstName = (string)command.ExecuteScalar();
Console.WriteLine(FirstName);

The query that gets to SQL Server is this:

SELECT FirstName FROM Employees
WHERE LastName=N'Davolio' AND PostalCode=N'98122'

The query is not auto-parameterized, and as a result, changing the last name or the postal code in the Where clause will generate a new plan, instead of using the existing one.

Let’s try another method called ADO.NET Parameterized Queries:

SqlConnection con = new SqlConnection("Server=xxx;Database=Northwind;Trusted_Connection=True;");
 con.Open();
 string LastName = "Davolio";
 string PostalCode = "98122";
SqlCommand command =
 new SqlCommand("SELECT FirstName FROM Employees "
 + "WHERE LastName = @LastName "
 + "AND PostalCode = @PostalCode");
 command.Parameters.Add
(new SqlParameter("@LastName", System.Data.SqlDbType.NVarChar));
 command.Parameters.Add
(new SqlParameter("@PostalCode", System.Data.SqlDbType.NVarChar));
 command.Parameters[0].Value = LastName;
 command.Parameters[1].Value = PostalCode;
command.Connection = con;
 string FirstName = (string)command.ExecuteScalar();
 Console.WriteLine(FirstName);

The command that gets to SQL Server looks like this:

exec sp_executesql
N'SELECT FirstName FROM Employees
WHERE LastName = @LastName
AND PostalCode = @PostalCode',
N'@LastName nvarchar(7),@PostalCode nvarchar(5)',
@LastName=N'Davolio',@PostalCode=N'98122'

When the query is submitted in this method, SQL Server caches it as a Prepared plan (instead of an Adhoc plan as before), but there is still a problem that prevents other queries from reusing this plan: In order for prepared plans to be reused, parameter types and lengths need to be the same every time a query is executed. In this example, the string lengths are 7 for LastName and 5 for PostalCode. If, for example, we submit a query with a shorter name and a longer postal code, the command that will get to SQL Server will have different lengths for the parameters, and reusing the current plan will not be possible.

Adding lengths to the command parameters in the application will solve the problem:

command.Parameters.Add
(new SqlParameter("@LastName", System.Data.SqlDbType.NVarChar, 20));
command.Parameters.Add
(new SqlParameter("@PostalCode", System.Data.SqlDbType.NVarChar, 20));

And now, the query will always get the proper sizes:

exec sp_executesql
N'SELECT FirstName FROM Employees
WHERE LastName = @LastName
AND PostalCode = @PostalCode',
N'@LastName nvarchar(20),@PostalCode nvarchar(20)',
@LastName=N'Davolio',@PostalCode=N'98122'

Subsequent executions of this query, even with different parameters lengths, will reuse the same plan.

NHibernate

By default, NHibernate acts like ADO.Net Parameterized Queries. It generates a call to sp_executesql with the corresponding parameters, but as the first ADO.Net parameterized query we saw, the parameter lengths are supplied according to the values of the specific request. Again, as we saw above, this prevents reuse for subsequent requests.

Fixing this problem is pretty simple. Just go to the NHibernate config file and add the following row:

<property name="prepare_sql">true</property>

After this addition, NHibernate will switch to use sp_prepexec. The parameter lengths are taken from the HBM file of the relevant class in the application or set to 4000 if the lengths are not defined (HBM files are the files which map the application classes and fields to database tables and columns).

Linq To SQL & Entity Framework

Up and including .Net 3.5, Linq To SQL and Entity Framework had the same problem we saw before: Parameter lengths are supplied according to the values of the specific request.

The issue is solved in .Net 4.0: Linq To SQL Parameter lengths are supplied as 8000 (for varchar) or 4000 (for nvarchar), and Entity Framework parameter lengths are supplied according to the maximum possible length of the column as defined in the application’s Entity Framework model.

Conclusion

Getting to know how plan caching works is important for both DBA’s and developers.

Being aware of how queries from the application affect plan caching, and fixing the code accordingly, is a step forward in optimizing your Plan Cache and application performance.