Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Enabling SQL Server Trace Flag for a Poor Performing Query Using QUERYTRACEON


By:   |   Read Comments (2)   |   Related Tips: More > Performance Tuning

Attend a SQL Server Conference for FREE >> click to learn more


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.

Sample Stored Procedure Execution Plan

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.

Operator Estimated Number of Rows

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

 

Effect of QUERYTRACEON 4199

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.

Estimated Number of Rows With QUERYTRACEON 4199

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

 

Execution With Trace Flags 4199 and 4136

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.

Estimated Operator Cost With Trace Flags 4199 and 4136
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, April 07, 2015 - 10:23:33 PM - sqL_handLe Back To Top

Excellent! How did you know the kb2222998 fix was enabled by T4199? I'm trying to keep track of the individual trace flags and other fixes rolled into T4199.

http://sql-sasquatch.blogspot.com/2014/01/trace-flag-4199-complex-risk-assessment.html

http://sql-sasquatch.blogspot.com/2014/01/trace-flag-4199-complex-risk-assessment_6.html


Thursday, August 28, 2014 - 1:55:19 PM - Sri Back To Top

Awesomel written!! Thanks Daniel for this great tip!!!!!!


Learn more about SQL Server tools