What Queries From The Application Do To Your Plan Cache

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:

  1. 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.

  2. 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.

  3. 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:

[code lang=”csharp”] 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);

[/code]

The query that gets to SQL Server is this:

[code lang=”sql”]

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

[/code]

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:

[code lang=”csharp”]

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

[/code]