Manage Learn to apply best practices and optimize your operations.

Improve query performance with SQL Server 2005 execution plans

SQL Server 2005 allows you to feed a full execution plan to your query. Outsmart query optimizer with a faster query plan. One new tool – the plan guide – allows you to inject query hints without changing the original stored procedure code.

One of the most exciting and useful additions to the query performance tuner's arsenal is its ability to force the database engine to use a pre-generated query execution plan. Previous versions of SQL Server allowed a DBA to specify query hints, to perhaps alter the transaction isolation level or to enforce usage of a given index or a join type. With SQL Server 2005, you can actually create a full execution plan and feed it to a query. You can also inject query hints into an application without modifying the code.

Why use new execution plan options?

At first you might wonder why you'd ever want to force an execution plan. After all, if SQL Server isn't using an efficient execution plan, there must be something wrong with your table design or indexing strategy. If you designed your system well, and it still doesn't perform well, you must be lacking appropriate hardware resources. Very true, but have you ever worked with a third-party application? Many software vendors provide a SQL Server database that you cannot alter in any way. If you worked with such an application, and it was not performing well, your only option was to contact the vendor, complain, and wait until they provided a hot-fix or a service pack. That was the case until SQL Server 2005.

Using a feature called plan guides, you can inject query hints into a stored procedure without modifying the code. You can also exploit a new query hint USE PLAN to specify a full XML query plan for a particular query. With these two options, you can tune an application even when you don't have access to its code base.

Then, if you're "lucky," you might work for a guy (or gal) who thinks his application is the most wonderful thing since sliced bread and you (a mere mortal) are not allowed to modify the application architecture. It is your job, however, to make such an application perform well. In that case query plan guides might help to a degree ( but I sincerely hope you find a better job and quickly).

If you've been a DBA for a while, you know that SQL Server has a cost-based query optimizer. The optimizer examines hardware resources available to SQL

More on this topic:

 Server, as well as index statistics available at the time of the query's execution. Using these inputs, the optimizer determines the best possible set of indexes and join algorithms for resolving the query. Well, almost always. When you're working with a complex query with a multitude of joins, SQL Server has a cornucopia of options to resolve such a query. If the database engine examined every single option for such a complex query, users would have to wait days, if not months, for their result set. Instead of examining millions of different options, the optimizer comes up with a plan that uses a reasonable amount of resources and still returns the results quickly.

If you know your data structures, then in rare cases you can outsmart the query optimizer because you have a query plan that works faster. For example, if a stored procedure is typically executed with a given set of parameters, the optimizer is likely to reuse the same execution plan repeatedly. When the parameters passed to the stored procedure change dramatically, the old query plan may no longer be optimal. In some cases you can add the WITH RECOMPILE option to the stored procedure so the execution plan is recompiled each time the procedure is called. But, reusing execution plans normally provides the best performance. Recompiling plans for a complex procedure might actually take longer than the actual execution of that procedure. A much better option is to find a plan that works reasonably well for all sets of parameters and force SQL Server to reuse that plan at every execution.

Generating the XML execution plan

You have several options for generating the query execution plan in XML format. First you can turn on SET SHOWPLAN_XML or SET STATISTICS XML options within SQL Server Management Studio before executing the query, as follows:


 FROM customers a
INNER JOIN orders b
ON a.customerid = b.customerid

Click here to view the output of the query.

The second option is to query the dynamic management function sys.dm_exec_query_plan; this function accepts a query plan handle from sys.dm_exec_cached_plans object as the only parameter as shown next:

SELECT           query_plan
FROM sys.dm_exec_cached_plans a
      CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) AS b
WHERE objtype = 'prepared'

The final alternative is to run a SQL Profiler trace with Showplan XML event found under the "Performance" category. This event is presented in graphical format within the Profiler, however, you can right-click on the event, choose "extract event data" and save the execution plan in XML format.

Although you can modify the XML files generated using either of the three discussed methods, it is recommended to avoid such practice; if you modify the plan, so that SQL Server can't use it, you will get a warning in that regard next time you try to force plan usage. The USE PLAN hint has some restrictions – if you wish to exploit this feature, be sure to review these in SQL Server's online documentation.

Note: USE PLAN hint is only supported with Enterprise, Developer and Standard Editions of SQL Server 2005.

Once you've found the optimal execution plan and have generated its XML version, you can force the usage of this plan with the following syntax:

Click here to view the syntax.

Using plan guides

As I mentioned, you can use plan guides can be used to inject query hints into an application, without modifying the original query code. Query hints are injected using sp_create_plan_guide system procedure and passing the desired hints within the OPTION clause. In the background the query optimizer first checks for an existing execution plan in cache. If one isn't found the optimizer queries an internal lookup table and substitutes the query with the hints for the original query. Then the query including the hints is compiled and executed.

Plan guides are most commonly used to specify RECOMPILE or OPTIMIZE FOR query hints. However, you can also advise SQL Server to use a particular type of JOIN, use a predefined execution plan (as discussed earlier), retrieve a specific number of rows quickly, and much more. Please reference the online documentation for a full list of query hints you can specify with the plan guides.

To use plan guides, you should first become familiar with two system procedures: sp_create_plan_guide and sp_control_plan_guide. The first procedure creates the plan guide for a code module, whereas the second is used to enable, disable or drop an existing plan guide. The optimizer can only use the plan guides that are enabled. Plan guides can be used for ad-hoc SQL statements, stored procedures, user-defined functions, data modification language triggers and templates.

For example, let's say you inherited a third-party application built on top of Northwind database. This application performs great with a small data set but your enterprise has grown so much that you're taking millions of orders each day. One of the troublesome procedures appears to be dbo.total_sales_per_product. It simply retrieves total sales of a particular product for customers that have paid more than $1500. As a DBA, you know this stored procedure is executed hundreds of times each day and typically your users are retrieving data for products that contain a string "hot" in their name. To speed up the queries retrieving total sales for "hot" products, the following statement creates a plan guide for the stored procedure:

@name = N'total_sales_plan_guide',
@stmt = N' SELECT CompanyName, SUM(c.UnitPrice*Quantity)AS total_sales
FROM Customers a
INNER JOIN Orders b ON a.CustomerID = b.CustomerID
INNER JOIN [order details] c ON b.OrderID = c.OrderID
INNER JOIN products d ON c.ProductID = d.ProductID
WHERE d.ProductName LIKE ''%'' + @product_name + ''%''
GROUP BY d.ProductName, CompanyName
HAVING SUM(c.UnitPrice*Quantity) > 1500'
@type = N'OBJECT',
@module_or_batch = N'dbo.total_sales_per_product',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@product_name = N''hot''))'

With this plan guide in place, the procedure will be executed with the plan that is optimal for the parameter value of "hot." This means the procedure might be slow with other values, but your users can be relatively happy, if 95% of all executions are fast and only 5% are somewhat slower. Although this is a simplistic example, it gives you a taste for what you can do with plan guides.

The following statement enables all Plan Guides within the current database:

sp_control_plan_guide N'ENABLE ALL'

Another case where plan guides can come in handy is when you want to control parallelism. Parallel execution can help performance of queries on a multi-processor server, but this won't always be the case. At times executing the query using a single processor will be faster. You can use a plan guide to effectively turn off parallelism, without modifying the query. This next example does just that by specifying MAXDROP 1 option:

@name = N'Plan_guide_for_adhoc_query',
@stmt = N' SELECT CompanyName, SUM(c.UnitPrice*Quantity)AS total_sales
FROM Customers a
INNER JOIN Orders b ON a.CustomerID = b.CustomerID
INNER JOIN [order details] c ON b.OrderID = c.OrderID
INNER JOIN products d ON c.ProductID = d.ProductID
WHERE d.ProductName =''tea bags''
GROUP BY d.ProductName, CompanyName
HAVING SUM(c.UnitPrice*Quantity) > 1500'
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (MAXDOP 1)'

Baya Pavliashvili is a DBA manager overseeing database operations that support more than one million users. Pavliashvili's primary areas of expertise are performance tuning, replication and data warehousing.
Copyright 2007 TechTarget

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.