A Framework for Applying Query Hints Without Changing TSQL Code
Introducing the problem
This problem, in most of the cases, usually occurs upon dealing with supplied software packages and not in self written code.
The problem scenario goes like this: you experience many query performance problems with some SQL databases that are being accessed from applications using TSQL source code but you are not permitted to do any code changes in the TSQL since the code is in responsibility of an external software supplier that denies, in contract, any attempts of changing the code.
When encountering bad performance issues that involve queries that you are not able to change, like in compressed or wrapped software, your options for taking care of the problem, can become very limited.
In case of third-party software, there is usually a restriction for to adding new indexes or archiving data from large tables. Affecting the database by applying some DB changes to the vendor’s actual database objects, supplied code or queries is most likely not allowed.
Another reason for not changing the inner TSQL code is the software upgrade rights.
Not changing any code can benefit from the ability of being able to upgrade the software package in a smooth manner and without having the re-write the entire code changes in the new version of the software. So it is really a mutual wish for both the supplier and the customer (in this case, yourself) to not change any code what so ever.
SQL Server’s solution for this problem
SQL Server provides a solution to this problem by using plan guides. The Plan guides object allows you to apply hints to queries inside TSQL code by without having to change the actual query text sent from the application.
The system procedure that helps in this subject is called sp_create_plan_guide. This system stored procedure creates a plan guide that links various query hints with existing queries in the database.
This stored procedure comes with another system procedure, called
sp_control_plan_guide that manages the plan guide that was created by sp_create_plan_guide.
These stored procedure are not new, they first appeared in SQL Server version 2005 that introduced the plan guides idea which allowed you to add hints to specific queries without directly changing the queries themselves.
Using these two procedures can turn out to be very useful but you should use it with care since it is an advanced feature.
There are three kinds of plan guides (object, SQL and template). In my improvement, I will deal with only the first two kinds: object and SQL.
Object plan guides work like this: they match queries that execute in the context of TSQL stored procedures or user defined functions (scalar or table-valued) and also DML triggers. They apply the hints defined in the
hint parameter on these queries. These guides are best suited for queries written inside database object code.
SQL plan guides work like this: they match queries that execute in the context of stand-alone TSQL statements and/or batches that are not part of a database object. These plan guides are best suited for queries that come from an application form or page body (active server page, JavaScript, PHP and so forth).
My suggested improvement
To build a general framework for handling the all the database plan guides. The framework will consist of a plan table that will hold all the SQL queries and the database object names that the plan guide procedure will work on along with the hint option to apply.
The framework will also include a stored procedure (called
plan_guide_handler) that will go through all the rows in the plan table, decide whether it is a query or an object and call
sp_create_plan_guide and
sp_control_plan_guide in order to create and manage the plan guides creation.
The procedure will have a parameter called mode with four possible values:
‘CREATE’ – this parameter tells the procedure to go over the plan table and create and apply all the plans.
‘DROP’ – this parameter tells the procedure to drop all the plan guides which their names appear in the plan table. ‘DISABLE’ – this parameter tells the procedure to disable all the plan guides which their names appear in the plan table. ‘ENABLE’ – this parameter tells the procedure to enable all the plan guides which their names appear in the plan table.
The procedure will check the mode parameter and call the
sp_control_plan_guide procedure with the appropriate operation since there are no appropriate DDL statements for managing plan guides yet.
The sp_control_plan_guide procedure also has two more managing options called ENABLE_ALL and DISABLE_ALL that enable or disable all the plan guides in the databases. I am not using these options in my solution. I prefer specific plan guide handling.
Both plan table and the managing procedure will be created inside the application database.
Here is the plan table structure:
|
Use Northwind go CREATE TABLE PLAN_TABLE ( PLAN_NAME NVARCHAR(50) ,PLAN_STMT NVARCHAR(300) ,PLAN_TYPE NVARCHAR(20) ,PLAN_MODULE NVARCHAR(50) ,PLAN_PARAMS NVARCHAR(100) ,PLAN_HINTS NVARCHAR(200) ) go |
Here is the stored procedure TSQL source code:
|
Use Northwind Go CREATE PROCEDURE plan_guide_handler (@mode NVARCHAR(20)) AS BEGIN DECLARE @plan_name NVARCHAR(50)
DECLARE @plan_stmt NVARCHAR(300)
DECLARE @plan_type NVARCHAR(20)
DECLARE @plan_module NVARCHAR(50)
DECLARE @plan_params NVARCHAR(100)
DECLARE @plan_hints NVARCHAR(200)
DECLARE c_plans CURSOR
FOR
SELECT *
FROM PLAN_TABLE;
OPEN c_plans;
FETCH NEXT
FROM c_plans
INTO @plan_name
,@plan_stmt
,@plan_type
,@plan_module
,@plan_params
,@plan_hints
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (UPPER(@mode) = 'CREATE')
BEGIN
EXEC sp_create_plan_guide @name = @plan_name
,@stmt = @plan_stmt
,@type = @plan_type
,@module_or_batch = @plan_module
,@params = @plan_params
,@hints = @plan_hints
END
ELSE
BEGIN
IF (UPPER(@mode) IN ('ENABLE','DISABLE','DROP'))
BEGIN
EXEC sp_control_plan_guide @operation = @mode
,@name = @plan_name
END
END
FETCH NEXT
FROM c_plans
INTO @plan_name
,@plan_stmt
,@plan_type
,@plan_module
,@plan_params
,@plan_hints
END
CLOSE c_plans
DEALLOCATE c_plans
END GO
|
An example for using the procedure:
The following example uses the sample north wind database.
First, we populate the plan table. For example:
INSERT INTO Northwind.dbo.PLAN_TABLE
VALUES (N’FORCE_HASH_JOIN’,
N’SELECT * FROM PRODUCTS P JOIN CATEGORIES C ON P.CATEGORYID = C.CATEGORYID’,
N’SQL’,
NULL,
NULL,
N’OPTION (HASH JOIN)’)
We check the graphical execution plan for this SQL before executing the procedure and see that the join type in the plan is a nested loop.
See in picture:
We execute the procedure with parameter mode = CREATE
Execute plan_guide_handler @mode = ‘CREATE’
We recheck the graphical execution plan for this SQL after executing the procedure and see that the join type in the plan is changed to a hash type join as seen in the picture:
We execute the procedure with parameter mode = DISABLE
Execute plan_guide_handler @mode = ‘DISABLE’
We verify that the graphical execution plan shows the default nested loop type join and that the
is_disabled column in the
Sys.
plan_guides system view equals 1.
We execute the procedure with parameter mode = DROP
Execute plan_guide_handler @mode = ‘DROP‘
We verify by querying the plan guides system view like this:
Select * from sys.plan_guides
We check either by that we get an empty result set or by counting the number of rows in that view in order to get a zero count.
Some relevant references:
Here are some suggested references on the article’s subject, recommended for further reading:
1. The MSDN link for plan guides:
https://msdn.microsoft.com/en-us/library/ms190417.aspx
2. An article from TechNet: "How to create a plan guide."
https://technet.microsoft.com/en-us/library/bb895390(v=sql.105).aspx
3. An article about plan guides and plan freezing:
http://blogs.msdn.com/b/turgays/archive/2013/03/01/using-plan-guides-and-plan-freezing.aspx
Author’s Bio:
Eli
Leiba is a senior application DBA at Israel Electric Company, a teacher at Microsoft CTEC, and senior database consultant with 23 years of experience working with both SQL Server and Oracle RDBMS. He is certified in Oracle and SQL Server database administration and implementation and has a B.S. in Computer Science. He can be reached at:
iecdba@hotmail.com.