By: Daniel Farina | Comments (4) | Related: > Performance Tuning
Problem
You have a pooring performing SQL Server query. After extensive research you find a KB article that describes the issue as a Query Optimizer bug. You download and apply the hotfix, but nothing seems to happen. In this tip we look at the possible issues and how to solve optimizer issues with QUERYTRACEON flags.
Solution
SQL Server’s cost based query optimizer is very wise, but there are situations in which you need to override its behavior. Fortunately this doesn’t happen too often. An example could be the performance impact of Parameter Sniffing which I explained in a previous tip Different Approaches to Correct SQL Server Parameter Sniffing. As described, you probably don’t want to disable it instance-wide, but just for a particular query or stored procedure. In that case, you should enable a trace flag for the given query.
Back in the SQL Server 2000 days, Microsoft adopted a policy to use trace flags to enable hotfixes that can change the way optimizer generates query plans. Just imagine what would happen if after an upgrade to a service pack or a cumulative update an optimizer feature you relied on is disabled. Things can get out of control and maybe a query that used to take less than a second now takes forever.
From SQL Server 2005 Service Pack 2 and later you had two ways to enable trace flags:
- Adding a startup parameter -T followed by the corresponding trace number using SQL Server Configuration Manager to enable the flags at the instance level.
- Executing DBCC TRACEON() to enable the flags, followed by DBCC TRACEOFF() to disable them.
Then Microsoft realized the need to enable plan affecting flags at the query level and introduced the option hint QUERYTRACEON that allows us to use trace flags for specific queries. The advantage of QUERYTRACEON is that we are able to enable plan affecting flags on code where we cannot change the plan by creating plan guides.
The official documentation of this feature is in KB 940128 (Enable plan-affecting SQL Server query optimizer behavior that can be controlled by different trace flags on a specific-query level) and includes a list of supported trace flags to use with the QUERYTRACEON hint. The article remarks that if an unsupported trace number is used it will be ignored.
For the purposes of this tip I am using the AdventureWorks2012 database which you can download for free from CodePlex at this link http://msftdbprodsamples.codeplex.com/releases/view/55330.
Fixing Cardinality Estimation Bug
Suppose we have an application performing bad in our SQL Server instance and we cannot change the underlying code. To make things worse imagine this application is affected by the bug described in KB 2222998 (FIX: An incorrect cardinality estimate is returned when you run a query in SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2). This KB article refers to an issue on the query optimizer’s ability to estimate cardinality when a query has a (Left | Right) Anti Semi Join logical operator with a redundant WHERE condition. Take a look at the next Stored Procedure.
IF OBJECT_ID('SampleProcedure', 'P') IS NOT NULL
BEGIN
DROP PROCEDURE SampleProcedure
END
GO
CREATE PROCEDURE SampleProcedure
@ShipMethodID INT ,
@TerritoryID INT
AS
SELECT c.CustomerID ,
c.PersonID ,
c.StoreID ,
c.TerritoryID ,
c.AccountNumber ,
c.rowguid ,
c.ModifiedDate
FROM Sales.Customer C
INNER JOIN Sales.SalesOrderHeader OH ON OH.CustomerID = C.CustomerID
WHERE OH.ShipMethodID = @ShipMethodID
AND C.CustomerID NOT IN (
SELECT C2.CustomerID
FROM Sales.Customer C2
WHERE C2.TerritoryID = @TerritoryID
AND C2.CustomerID = C.CustomerID )
GO
What this Stored Procedure is aiming to do is to list the customers who have used a given shipping method and are not in a determined Territory. Of course this isn’t the best way to achieve this, but for the purposes of this example it's good enough. Remember, we are supposed to have an application that we cannot change its code no matter how bad the code.
Let’s execute this Stored Procedure.
USE [AdventureWorks2012]
GO
EXECUTE dbo.SampleProcedure @ShipMethodID = 1,
@TerritoryID = 2
Looking at the execution plan we can see that it uses the logical operator Right Anti Semi Join because of the NOT IN condition and it is implemented by a Merge Join physical Operator.
If we zoom into the Right Anti Semi Join we can see that the Estimated Number of Rows is 1 whereas the Actual Number of Rows is 19707. Since all conditions are met, we can conclude we are victim of the cardinality estimation bug.
To fix this we have to use trace flag 4199 as stated in KB 974006 (Trace flag 4199 is added to control multiple query optimizer changes previously made under multiple trace flags). Due to the fact that we cannot change the code and not even add a QUERYTRACEON hint to the code we will create a Plan Guide as follows using QUERYTRACEON.
USE [AdventureWorks2012]
GO
EXEC sp_create_plan_guide @name = N'SampleProcedure_PlanGuide',
@stmt = N' SELECT c.CustomerID ,
c.PersonID ,
c.StoreID ,
c.TerritoryID ,
c.AccountNumber ,
c.rowguid ,
c.ModifiedDate
FROM Sales.Customer C
INNER JOIN Sales.SalesOrderHeader OH ON OH.CustomerID = C.CustomerID
WHERE OH.ShipMethodID = @ShipMethodID
AND C.CustomerID NOT IN (
SELECT C2.CustomerID
FROM Sales.Customer C2
WHERE C2.TerritoryID = @TerritoryID
AND C2.CustomerID = C.CustomerID )',
@type = N'OBJECT', @module_or_batch = N'[dbo].[SampleProcedure]',
@hints = N'OPTION (querytraceon 4199 )'
GO
Now let’s execute this Stored Procedure again and take a look at the query plan.
USE [AdventureWorks2012]
GO
EXECUTE dbo.SampleProcedure @ShipMethodID = 1,
@TerritoryID = 2
We can see that the plan has changed and zooming into the Right Anti Semi Join operator we can see that the optimizer now produces an accurate estimation of rows.
Using More Than One Trace Flag
We can add as many Trace Flags as we want by adding another QUERYTRACEON hint with the trace number we want and separating each hint by a comma.
For example if we also want to disable Parameter Sniffing for the previous Stored Procedure then we can add another QUERYTRACEON hint with Trace Flag 4136. Here is the modified Plan Guide.
USE [AdventureWorks2012]
GO
EXEC sp_control_plan_guide @operation = N'drop', -- nvarchar(60)
@name = 'SampleProcedure_PlanGuide' -- sysname
EXEC sp_create_plan_guide @name = N'SampleProcedure_PlanGuide',
@stmt = N' SELECT c.CustomerID ,
c.PersonID ,
c.StoreID ,
c.TerritoryID ,
c.AccountNumber ,
c.rowguid ,
c.ModifiedDate
FROM Sales.Customer C
INNER JOIN Sales.SalesOrderHeader OH ON OH.CustomerID = C.CustomerID
WHERE OH.ShipMethodID = @ShipMethodID
AND C.CustomerID NOT IN (
SELECT C2.CustomerID
FROM Sales.Customer C2
WHERE C2.TerritoryID = @TerritoryID
AND C2.CustomerID = C.CustomerID )',
@type = N'OBJECT',
@module_or_batch = N'[dbo].[SampleProcedure]',
@hints = N'OPTION (QUERYTRACEON 4199, QUERYTRACEON 4136 )'
GO
Notice the syntax used to add more than one trace flag: OPTION (QUERYTRACEON 4199, QUERYTRACEON 4136 ).
After re-executing our Stored Procedure we can see that the plan has changed again by looking at the operators cost. Just to look at one, the Hash Match now has an operator cost of 28% instead of the 52% of the previous execution.
USE [AdventureWorks2012]
GO
EXECUTE dbo.SampleProcedure @ShipMethodID = 1,
@TerritoryID = 2
Looking at the Right Anti Semi Join Operator we can see that the Estimated Number of Rows isn’t 1 as without Trace Flag 4199, but differs from the estimation we got with Flag 4199 alone because of Flag 4136 that prevents the optimizer of sniffing parameter values.
Next Steps
- If you want to run the code in this tip you need to download the AdventureWorks2012 database from CodePlex for free at this link http://msftdbprodsamples.codeplex.com/releases/view/55330.
- Read this tip if you don’t know much about SQL Server Queries With Hints.
- To get more information and learn about Plan Guides you can read Armando Prato's tip about How to use a SQL Server Plan Guide to Tune Queries.
- If you want to perform query testing then you will need this: Clearing Cache for SQL Server Performance Testing.
- Take a look at Performance Tuning Tips category.
- In case you are new to the concept of Parameter Sniffing, in my previous tip about Different Approaches to Correct SQL Server Parameter Sniffing you can get an explanation about it as well as different ways to correct it.
- If you want to dig further into query plans this will be of help: How to read SQL Server graphical query execution plans.
About the author
Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
