Speeding up database access - Part 6: Fixing execution plan reuse

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

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.

In part 2, you saw that before a query is executed, the SQL Server query optimizer compiles a cost effective execution plan,
which takes many CPU cycles. 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.

Trivial execution plans

For some queries, it is trivial for the query optimizer to pick the most optimal execution plan. In that case, SQL Server does not store the plan, making execution plan reuse not an issue.
Take for example:

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

Provided that BookId is the primary key, the query optimizer always uses an execution plan that uses the primary key, irrespective of for example data distribution.
Because it doesn't have to work out different plans and perform a cost-based comparison between them, SQL Server
doesn't gain by storing the execution plan. Only when working out the execution plan is non-trivial does execution plan reuse come into play.

As can be expected with SQL Server, determining whether an execution plan is trivial and whether it is cached is ... non-trivial. For more information, see:

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 by default attempts this only with very simple
queries with one table. For example,

sp_executesql

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 simply
tell it yourself. Calling sp_executesql takes this form:

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:

This creates a Stored Procedure with the 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.

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:

Share

About the Author

Matt has over 9 years .NET and SQL Server development experience. Before getting into .Net, he worked on a number of systems, ranging from the largest ATM network in The Netherlands to embedded software in advanced Wide Area Networks and the largest ticketing web site in Australia. He has lived and worked in Australia, The Netherlands, Slovakia and Thailand.

He is the author of the book ASP.NET Performance Secrets (www.amazon.com/ASP-NET-Site-Performance-Secrets-Perdeck/dp/1849690685) in which he shows in clear and practical terms how to quickly find the biggest bottlenecks holding back the performance of your web site, and how to then remove those bottlenecks. The book deals with all environments affecting a web site - the web server, the database server and the browser.

Matt currently lives in Sydney, Australia. He recently worked at Readify and the global professional services company PwC. He now works at SP Health, a global provider of weight loss web sites such at CSIRO's TotalWellBeingDiet.com and BiggestLoserClub.com.

Comments and Discussions

It is well presented, but slightly obsolete. In modern programming where most interactions with the database (I say most) pass through the ORM, it's quite interesting to see updated suggestions and explain the ORM's choices when it generates sql, or at least have a reference on that.

According to Microsoft, it can:
http://msdn.microsoft.com/en-us/library/ms186219(v=SQL.100).aspx

More about Simple Parameterization:
http://beyondrelational.com/blogs/ana/archive/2011/10/31/sql-server-simple-parameterization.aspx
http://blog.sqlauthority.com/2007/10/25/sql-server-2005-forced-parameterization-and-simple-parameterization-t-sql-and-ssms/