Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server

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

Rate me:
Please Sign up or sign in to vote.
4.73/5 (10 votes)
19 Dec 2011CPOL7 min read 32.4K   26   4
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.

If you like this article, please vote for it.

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:

SQL
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:

Ad hoc queries

Take this simple ad hoc query:

SQL
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:

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

SQL
SELECT Title, Author FROM dbo.Book WHERE LeadAuthorId=5

can reuse the execution plan generated for:

SQL
SELECT Title, Author FROM dbo.Book WHERE LeadAuthorId=9

To make SQL Server more aggressively parameterize queries, consider switching on Forced Parameterization.

More about simple and forced parameterization can be found at:

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:

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

For example:

SQL
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',
   @BookId=5

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:

SQL
CREATE PROCEDURE GetBook
    @BookId int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT Title, Author FROM dbo.Book WHERE BookId=@BookId
END
GO

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:

SQL
EXEC dbo.GetBook @BookId=5

or simply:

SQL
EXEC dbo.GetBook 5

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

SQL
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));
        connection.Open();
        // 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:

SQL
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:

SQL
CREATE PROCEDURE dbo.GetSupplierByCity
    @City nvarchar(100)
WITH RECOMPILE
AS
BEGIN
...
END

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

SQL
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:

SQL
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:

SQL
EXEC sp_recompile 'dbo.Book'

Conclusion

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
Australia Australia
Twitter: @MattPerdeck
LinkedIn: au.linkedin.com/in/mattperdeck
Current project: JSNLog JavaScript Logging Package

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

 
GeneralMy vote of 4 Pin
User 482203312-Dec-11 22:27
User 482203312-Dec-11 22:27 
GeneralMy vote of 4 Pin
rm82212-Dec-11 18:40
rm82212-Dec-11 18:40 
GeneralRe: My vote of 4 Pin
Matt Perdeck19-Dec-11 14:32
Matt Perdeck19-Dec-11 14:32 
GeneralRe: My vote of 4 Pin
rm82219-Dec-11 17:41
rm82219-Dec-11 17:41 
You just don't understand query optimizer logic.
In ms case
SQL
SELECT * FROM AdventureWorks.Production.Product
WHERE ProductSubcategoryID = 1;

ProductSubcategoryID is non-pk non-unique column, so different plans can be optimal depending on data distribution

you case is like
SQL
SELECT * FROM AdventureWorks.Production.Product
WHERE ProductId = 1;

ProductId - is primary key. Optimal plan is obvious and no optimization performed at all. When the first generated plan is the best - autoparametization can only slow-down execution.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.