For an instructor lead, in-depth look at learning SQL click below.
When it comes to optimizing an SQL Server workload, one of the most valuable skills you can equip yourself with is the ability to understand and manipulate execution plans. These are essentially the “blueprints” SQL Server’s query optimizer uses to execute SQL queries. In this post, we will focus on Plan Guides, a powerful feature in SQL Server that allows us to optimize query execution plans in a non-invasive and flexible way.
What is a Plan Guide?
A Plan Guide is a database object that you can use to specify query optimizer behaviour for arbitrary SQL statements. The primary use of Plan Guides is for tuning the performance of queries by attaching query hints or fixing some queries by overriding the optimizer choice. This is of particular significance when it is not possible to directly modify the query text — for example, in off-the-shelf software.
Creating a Plan Guide
To understand this better, let’s consider an example, where you have a specific query you want to optimize. Let’s say that your query is running in parallel and causing high CPU usage, and you want to force the query optimizer to run this query sequentially.
|
1 2 3 4 5 6 |
-- The original query is: SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM [Northwind].[dbo].[Orders] WHERE EmployeeID = 1 |
You would create a Plan Guide that looks like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Create a Plan Guide EXEC s<a href="mailto:p_create_plan_guide @name" >p_create_plan_guide @name</a> = N'Guide1', @stmt = N'SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM [Northwind].[dbo].[Orders] WHERE EmployeeID = 1', @type = N'SQL', @module_or_batch = NULL, @params = NULL, @hints = N'OPTION (MAXDOP 1)' |
The sp_create_plan_guide stored procedure creates a new plan guide. The @hints option is used to specify the hint that you want to attach to this query, in this case, forcing SQL Server to use only one CPU (MAXDOP 1).
Validating and Using Plan Guides
Once you’ve created your Plan Guide, it’s important to validate that it’s working as expected. You can use the sys.plan_guides catalogue view to view information about all the plan guides in your database.
|
1 2 3 4 5 |
-- View Plan Guides SELECT * FROM sys.plan_guides |
This command will return a row for each plan guide in the database, allowing you to verify the hints that are being applied.
In conclusion, Plan Guides are a powerful tool for optimizing query performance in SQL Server. By understanding and skillfully applying Plan Guides, you can significantly enhance your database’s efficiency and performance without directly altering your application’s SQL code.
Remember that any powerful tool must be used carefully – always validate your plan guides and monitor their effects to ensure they are having the desired positive impact on performance.