Speeding up database access - part 6 Fixing execution plan reuse

by Matt Perdeck 30. November 2011 21:32

This is part 6 of an 8 part series of articles about speeding up access to a SQL Server database. This series is based on chapter 8 "Speeding up Database Access" of my book ASP.NET Site Performance Secrets, available at amazon.com and other book sites.

In part 2, we saw how to identify suboptimal reuse of execution plans. In this part 6, we'll look at improving this.

  • Part 1 Pinpointing missing indexes and expensive queries
  • Part 2 Pinpointing other bottlenecks
  • Part 3 Fixing missing indexes
  • Part 4 Fixing expensive queries
  • Part 5 Fixing locking issues
  • Part 6 Fixing execution plan reuse
  • Part 7 Fixing fragmentation
  • Part 8 Fixing memory, disk and CPU issues

You can boost execution plan reuse in your site by making it easier for SQL Server to work out which bits of a query's execution plan can be reused by a similar query.

Ad hoc queries

Take this simple ad hoc query:

SELECT b.Title, a.AuthorName 
FROM dbo.Book b JOIN dbo.Author a ON b.LeadAuthorId=a.Authorid 
WHERE BookId=5 

When SQL Server receives this query for the very first time, it will compile an execution plan, store the plan in the plan cache, and execute the plan.

If SQL Server then receives this query again, it will reuse the execution plan if it is still in the plan cache, provided that:

  • All object references in the query are qualified with at least the schema name - dbo.Book instead of Book. Adding the database would be even better.

  • There is an exact match between the text of the queries. This is case sensitive, and any white space differences also prevent an exact match.

As a result of the second rule, if you use the same query as above but with a different BookId, there will be no match:

SELECT b.Title, a.AuthorName 
FROM dbo.Book b JOIN dbo.Author a ON b.LeadAuthorId=a.Authorid 
WHERE BookId=9 -- Doesn't match query above, uses 9 instead of 5

Obviously, this is not a recipe for great execution plan reuse.

Simple Parameterization

To make it easier for ad hoc queries to reuse a cached plan, SQL Server supports simple parameterization. This automatically figures out the variable bit of a query. Because this is hard to get right and easy to get wrong, SQL Server attempts this only with very simple queries with one table. For example,

SELECT Title, Author FROM dbo.Book WHERE BookId=5

can reuse the execution plan generated for

SELECT Title, Author FROM dbo.Book WHERE BookId=9


Instead of getting SQL Server to guess which bits of a query can be turned into parameters, you can use the system stored procedure sp_executesql to simple tell it yourself. Calling sp_executesql takes this form:

sp_executesql @query, @parameter_definitions, @parameter1, @parameter2, ... 

For example:

EXEC sp_executesql 
	N'SELECT b.Title, a.AuthorName
	  FROM dbo.Book b JOIN dbo.Author a ON b.LeadAuthorId=a.Authorid
	  WHERE BookId=@BookId',
	N'@BookId int',

Note that sp_executesql expects nvarchar values for its first two parameters, so you need to prefix the strings with N.

Stored Procedures

Instead of sending individual queries to the database, you can package them in a stored procedure that is permanently stored in the database. That gives you the following advantages:

  • Just as with sp_executesql, stored procedures allow you to explicitly define parameters to make it easier for SQL Server to reuse execution plans.

  • Stored procedures can contain a series of queries and T-SQL control statements such as IF THEN. This allows you to simply send the stored procedure name and parameters to the database server, instead of sending individual queries - saving networking overhead.

  • Stored procedures make it easier to isolate database details from your web site code. When a table definition changes, you may only need to update one or more stored procedures, without touching the web site.

  • You can implement better security, by only allowing access to the database via stored procedures. That way, you can allow users to access the information they need through stored procedures, while preventing them from taking unplanned actions.

To create a stored procedure in SQL Server Management Studio, expand your database, expand Programmability and then expand Stored Procedures. Right click Stored Procedures and choose New Stored Procedure. A new query window opens where you can define your new stored procedure.

A stored procedure to execute the query you saw in the previous section would look like this:

	@BookId int

	SELECT Title, Author FROM dbo.Book WHERE BookId=@BookId

This creates a stored procedure with name GetBook, and a parameter list with one parameter @BookId of type int. When SQL Server executes the stored procedure, occurrences of that parameter in the body of the stored procedure get replaced by the parameter value that you pass in.

Setting NOCOUNT to ON improves performance by preventing SQL Server from sending a message with the number of rows affected by the stored procedure.

To add the stored procedure to the database, press F5 to execute the CREATE PROCEDURE statement.

To verify that the stored procedure has been created, right click Stored Procedures and choose Refresh. Your new stored procedure should turn up in the list of stored procedures. To modify the stored procedure, right click the stored procedure and choose Modify.

To execute the stored procedure in a query window, use:

EXEC dbo.GetBook @BookId=5

or simply:

EXEC dbo.GetBook 5

Using a stored procedure from your C# code is similar to using an ad hoc query, as shown below.

string connectionString = "...";
using (SqlConnection connection =
    new SqlConnection(connectionString))
    string sql = "dbo.GetBook";
    using (SqlCommand cmd = new SqlCommand(sql, connection))
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@BookId", bookId));

        // Execute database command ...

Make sure that the command text has the name of the stored procedure, instead of the text of a query. Set the CommandType property of the SqlCommand object to CommandType.StoredProcedure, so SQL Server knows you're calling a stored procedure. Finally, add parameters to the command that match the parameters you used when you created the stored procedure (more about stored procedures).

Now that you've seen how to improve reuse of execution plans, let's see how to prevent plan reuse, and why you would want to do that.

Preventing Reuse

You may not always want to reuse an execution plan. When the execution plan of a stored procedure is compiled, that plan is based on the parameters used at the time. When the plan is reused with different parameters, the plan generated for the first set of parameters is now reused with the second set of parameters. However, this is not always desirable.

Take for example this query:

SELECT SupplierName FROM dbo.Supplier WHERE City=@City 

Assume that the Supplier table has an index on City. Now assume half the records in Supplier have City "New York". The optimal execution plan for "New York" will then be to use a table scan, rather incurring the overhead of going through the index. If however "San Diego" has only a few records, the optimal plan for "San Diego" would be to use the index. A good plan for one parameter value may be a bad plan for another parameter value. If the cost of using a suboptimal query plan is high compared with the cost of recompiling the query, you would be better off to tell SQL Server to generate a new plan for each execution.

When creating a stored procedure, you can tell SQL Server not to cache its execution plan with the WITH RECOMPILE option:

CREATE PROCEDURE dbo.GetSupplierByCity
	@City nvarchar(100)

Or you can have a new plan generated for a specific execution:

EXEC dbo.GetSupplierByCity 'New York' WITH RECOMPILE

Finally you can cause a stored procedure to be recompiled the next time it is called with the system stored procedure sp_recompile:

EXEC sp_recompile 'dbo.GetSupplierByCity'

To have all stored procedures that use a particular table recompiled the next time they are called, call sp_recompile with that table:

EXEC sp_recompile 'dbo.Book'


In this part, we saw how to improve execution plan reuse, such as through simple parameterization and stored procedures.

In the next part, we'll see how to fix excessive fragmentation.

Add comment

  Country flag

  • Comment
  • Preview


Book: ASP.NET Site Performance Secrets

ASP.NET Site Performance Secrets

By Matt Perdeck

Details and Purchase

About Matt Perdeck

Matt Perdeck PresentingMatt has written extensively on .Net and client side software development.

more >>