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:
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:
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
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,
SELECT Title, Author FROM dbo.Book WHERE LeadAuthorId=5
can reuse the execution plan generated for:
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:
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',
@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:
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:
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));
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:
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)
WITH RECOMPILE
AS
BEGIN
...
END
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'
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.
