How to force a plan in SQL Server and why not
Certifications:
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
Sometimes a query may run with a different value depending on the value we have given to a parameter plan (execution plan) and have a different duration. This phenomenon is called parameter sniffing. This may be due to different number of records of the result, in outdated statistics or in not using a correct one non-clustered index. In the article we will see how we can do it force a shot on a procedure or a sql statement and why we usually shouldn't do it.
What is parameter sniffing?
Parameter sniffing is called the SQL Server process that when we run a procedure or an sql statement it accepts an unknown parameter, reads it in order to create a shot according to the price we have set. Where is the problem; That depending on the parameter, the query can return 1 row or 100,000 and SQL Server will use the plan that was made with the first parameter it received. This will cause the second query to run with non-efficient (optimized) way.
What is the solution
The solution could be to do recompile the plan every time but it would lead to high usage cpu along with high memory usage. This would happen because with each parameter another plan would be created which would take up space in cache. We could also use query hint with the command to optimize according to another parameter so that the plan of the specific parameter is always used.
Sometimes the problem is due to statistics, as when statistics were taken on the table, the table had a different number of records than when the query was executed.
Finally, the problem could be the lack of a correct one non-clustered index.
Force it hard way aka Plan Guides!
If for some reason we can't mess with our code in the application or we don't have permission to add our own indexes and we want a solution right away there is a way. The use Plan Guides!
Plan Guides allow us to do optimize queries without messing with their code.
We have 3 categories of Plan Guides:
- Object: To optimize queries inside a Stored Procedure.
- Template: To optimize stand-alone queries that accept parameters.
- SQL: To optimize in stand-alone queries its execution mode.
Example
The example will be on its trial basis Microsoft WideWorldImporters.
In the case of a stored procedure that accepts one parameter
use WideWorldImporters; go create procedure sp_orders @stockitemid INT as select o.customerid, sum(ol.quantity*ol.unitprice) from sales.orders o inner join sales.orderlines ol on o.orderid=ol.orderid where ol.stockitemid=@stockitemid group by o.customerid order by o.customerid asc; go
We run the procedure twice with a different parameter and with the option set statistics xml on he will also bring us the plan of each one.
set statistics xml on; GO sp_orders 90 with recompile; GO sp_orders 224 with recompile; GO
We see with a compare that for the same query even though we did recompile he came to us with a different plan.
After we decide which of the two plans covers us for both cases we will make it Plan Guide.
Since it is a stored procedure we will define as type='OBJECT', in the stmt the query and at hints we will add it query hint which will always make the query run with the plan it would have if the value was 90 *.
* In case we do not know which value covers the maximum performance in most executions of the query. Instead of putting the value itself as an option, we can set it OPTION (OPTIMIZE FOR UNKNOWN) which will use the statistics data to create a "moderate" plan that will try to cover all the values it has received. But it may not be efficient enough.
EXEC sp_create_plan_guide
@name = N'planguide_orders',
@stmt = N'select
o.customerid,
sum(ol.quantity*ol.unitprice)
from sales.orders o
inner join sales.orderlines ol on o.orderid=ol.orderid
where ol.stockitemid=@stockitemid
group by o.customerid
order by o.customerid asc',
@type = N'OBJECT',
@module_or_batch = N'sp_orders',
@params = NULL,
@hints = N'OPTION (optimize for (@stockitemid = 90))';
After creating it we try the same execution again.
sp_orders 90 with recompile; GO sp_orders 224 with recompile; GO
This time we will see that the plan remained the same as it was with the execution with a value of 90.
If we open it XML of the plan we will see that it mentions that it made use of the planguide_orders we made before.
In the case of a stand-alone query with a parameter
We will execute it sp_get_query_template so that the query we know with values is split as SQL Server knows it with the SQLStatement and the parameter name.
Also in the query hint we will define parameterization forced so that whatever parameter we have given to the query will run with the plan it has with the value 90.
DECLARE @SQLStatement NVARCHAR(MAX);
DECLARE @Parameters NVARCHAR(MAX);
EXEC sp_get_query_template
N'select
o.customerid,
sum(ol.quantity*ol.unitprice)
from sales.orders o
inner join sales.orderlines ol on o.orderid=ol.orderid
where ol.stockitemid=90
group by o.customerid
order by o.customerid asc',
@SQLStatement OUTPUT,
@Parameters OUTPUT
EXEC sp_create_plan_guide
@name = N'planguide_orders_2',
@stmt = @SQLStatement,
@type = N'TEMPLATE',
@module_or_batch = NULL,
@params = @Parameters,
@hints = N'OPTION (PARAMETERIZATION FORCED)';
How do we find the Plan Guides we have?
There is the system view sys.plan_guides which contains all the Plan Guides. We can delete a specific one or all at a moment's notice.
select * from sys.plan_guides;
sp_control_plan_guide 'DROP', 'planguide_orders_2' sp_control_plan_guide 'DROP ALL';
What to watch out for when using Plan Guides
You have to SQL text to be exactly the same, if the query is written differently or even a simple space above it will not work. In the event that the query may come to us with a different text, we must create a second Plan Guide.
The query optimizer usually knows what the best plan is. By doing it we force may have the same plan but on average perform worse.
In addition, the data may change over time and we may have an outdated bad plan.
