Since SQL Server 2005, the platform has offered functionality similar to Oracle's "fixed execution plan." However, this feature, known as the Plan Guide in SQL Server, seems to be underutilized. While not referred to as "fixed execution plan" in SQL Server, the Plan Guide essentially serves the same purpose. The difference in terminology is mainly a result of branding. Personally, I find "fixed execution plan" to be a more intuitive and straightforward term, whereas "Plan Guide" can feel somewhat abstract. For simplicity, I will use both terms interchangeably in this article as they refer to the same concept.
I rarely used this feature in the past. However, I recently encountered some performance issues in a SQL Server 2014 database and resolved them by using fixed execution plans. This prompted me to summarize and share my insights on the topic.
Why Pin an Execution Plan?
The following are some reasons to use a fixed execution plan (Plan Guide). If there are any gaps, feel free to point them out—this is based on my personal exploration:
1. Resolving Performance Issues Caused by Poor Plans
Occasionally, certain factors—such as parameter sniffing, changes in statistics, low sampling rates causing skewed statistics, or new features like SQL Server 2014’s Cardinality Estimator—can lead the optimizer to choose suboptimal execution plans.
For instance, I once dealt with a frequently executed SQL query in SQL Server 2014. Occasionally, the optimizer would select a poor execution plan, causing severe performance degradation. Using a Plan Guide, I was able to pin the optimal execution plan and eliminate these issues.
2. Optimizer Complexity and Suboptimal Plans
The query optimizer’s process of generating execution plans is inherently complex and often opaque. While developers can use query hints to influence plans, they can’t fully control the optimizer’s decisions. In some cases, the optimizer may not generate the most efficient plan due to algorithmic or cost-based considerations.
With Plan Guides, DBAs can step in and lock in a superior plan. However, such scenarios are rare in production environments.
3. Handling Vendor-Provided Systems
In systems provided by third-party vendors, it’s common to encounter numerous similar SQL queries that could be parameterized to reduce parsing overhead. If modifying the source code is not an option due to the lack of vendor support, Plan Guides can help optimize such queries without altering the application code.
4. Comparing and Optimizing Plans
Plan Guides are also useful for evaluating and comparing different execution plans. By locking in specific plans, DBAs can test their impact on performance.
There are likely other use cases that I have not encountered yet.
How to Pin an Execution Plan?
The following system stored procedures are key to using Plan Guides. Detailed documentation is available in SQL Server’s official resources, so I won’t repeat them here:
sys.sp_create_plan_guide sys.sp_create_plan_guide_from_handle sys.sp_control_plan_guide
Let’s take a look at some application scenarios. Creating a suitable and relevant example from scratch takes significant time and effort, and examples from production environments cannot be shared directly. Therefore, let’s refer to an example from the official documentation.
In the AdventureWorks2014 test database, the following SQL query uses a Nested Loop to join two tables.
SELECT COUNT(*) AS c FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate >= '20000101' AND h.OrderDate <='20050101';
Assume (this is a hypothetical situation) that the query would perform more efficiently if the two tables were joined using a MERGE JOIN. In this case, we can use sp_create_plan_guide to create a Plan Guide (fixed execution plan), as shown below.
EXEC sp_create_plan_guide
@name = N'my_table_jon_guid',
@stmt = N'SELECT COUNT(*) AS c
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.OrderDate >= ''20000101'' AND h.OrderDate <=''20050101'';',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (MERGE JOIN)';
After applying the Plan Guide, when the query is executed again, you will notice that the execution plan switches to the MERGE JOIN method.
This approach is preferable to using HINTs directly in SQL Server. Why? It’s possible that the query is hardcoded in the application, and if the execution plan later turns into a suboptimal one, the maintenance cost could be very high.
Firstly, if there are no records, a lot of effort may be required to locate and identify the SQL query.
Secondly, DBAs may not have permission to access or modify the application’s source code. This would necessitate communication and coordination with developers or operations staff, consuming significant time and effort.
Additionally, future maintainers may not be aware of the situation or its context.
By using a Plan Guide, you can address the issue simply by locating, disabling, or deleting the Plan Guide. This approach is highly convenient and efficient—you might resolve the issue in a matter of minutes. In contrast, resolving the issue with a HINT could take days, as many have experienced in such time-consuming scenarios of communication and coordination.
SELECT COUNT(*) AS c FROM Sales.SalesOrderHeader AS h INNER MERGE JOIN Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate >= '20000101' AND h.OrderDate <='20050101';
