One of my clients uses a commercial application and he has performance issues with some of the queries. As I already manage his other databases, he asks me if I can take a look at it.

The problem is that I can't modify the faulty queries.

We have several possibilities to optimize a query without modifying it.

In this article, I will introduce the "Plan Guide" with for two scenarios :

  1. To enable a trace trace flag to a specific query;
  2. To optimize a query by forcing SQL to recompile it.

Plan Guides influence the optimization of queries by attaching query flags or a fixed query plan to the query. In the plan guide, you specify the SQL code you want to optimize and an OPTION clause containing the query flags or a specific query plan to use to optimize the query. When the query runs, SQL Server maps the SQL code to the plan guide and attaches the OPTION clause to the query at run time or invokes the specified query plan.

Since SQL Server 2005, Microsoft has vastly improved the query optimizer. The problem is that many of these enhancements are not enabled by default in the newer versions of the product. For example, several improvements have been grouped under trace flag 4199, but this flag is active by default only in SQL Server 2014.

For the curious, here is the list of fixes for trace flag 4199.

1) Enable a trace flag for a specific query :

Suppose we are victim of the BUG described in Microsoft Knowledge Base KB 2222998. This KB refers to a problem on the ability of the query optimizer to estimate cardinality when a query has an Anti-Semi-Join logical logical operator (Left | Right) with a redundant WHERE condition. This bug has been fixed in a CU for SQL Server 2008 R2, but requires the activation of a trace flag.

Here is a simple stored procedure:

CREATE PROCEDURE TF4199_sp (@ShipMethodID INT,@TerritoryID INT)
AS
SELECT        COUNT(*) AS total
FROM            Sales.Customer AS C INNER JOIN
                         Sales.SalesOrderHeaderEnlarged AS OH ON OH.CustomerID = C.CustomerID
WHERE        (OH.ShipMethodID = @ShipMethodID) AND (C.CustomerID NOT IN
                   (SELECT        CustomerID
                     FROM            Sales.Customer AS C2
                     WHERE        (TerritoryID = @TerritoryID) AND (CustomerID = C.CustomerID)))
GO

The purpose of this stored procedure is to count customers who have used a particular shipping method and are not in a specific territory.

Let's execute this stored procedure.

USE [AdventureWorks2012]
GO
EXECUTE dbo.TF4199_sp @ShipMethodID = 1,@TerritoryID = 2

 plan1

The query took 5 seconds to run. It also generated 3.8 million logical reads !!

read1


Looking at the execution plan, we can see that the "Right Anti Semi Join" is used because of the NOT IN condition of our query.

If we look at the detail of the "Right Anti Semi Join" we can see that the estimated number of rows is 1 while the actual number of rows is from 19707.

stats1

Given that we can not change the code, because the simple thing would have been to add the "hint" OPTION (QUERYTRACEON 4199) directly to the query.

We will use a plan guide :

USE [AdventureWorks2012]
GO
EXEC sp_create_plan_guide @name = N'TF4199_PlanGuide',
  @stmt = N'SELECT        COUNT(*) AS total
FROM            Sales.Customer AS C INNER JOIN
                         Sales.SalesOrderHeaderEnlarged AS OH ON OH.CustomerID = C.CustomerID
WHERE        (OH.ShipMethodID = @ShipMethodID) AND (C.CustomerID NOT IN
                             (SELECT        CustomerID
                               FROM            Sales.Customer AS C2
                               WHERE        (TerritoryID = @TerritoryID) AND (CustomerID = C.CustomerID)))',
     @type = N'OBJECT', @module_or_batch = N'[dbo].[TF4199_sp]',
     @hints = N'OPTION (querytraceon 4199)'
GO

Now, re-run this stored procedure and look at the execution plan.

plan2


We can see that the plan has changed and the number of logical reads dropped dramatically to 13228.

read2


If we look at the detail of the "Right Anti Semi Join", we can see that the optimizer now produces an accurate estimate of rows.

stats2

 

2) Optimize a query by forcing SQL to recompile it :


Now, let's look at an example that is not a bug. The following queries work well when they are executed with numeric constants.

SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = 870
SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = 897

read3


Let's look at their execution plans :

plan3

The optimizer created a different plan for each query.

The above example is futile because it is unlikely that we would use numeric literals in our SQL queries. The goal here is simply to show you what the execution plan would be when the optimiser "knows" the value of the variable.

Now, use sp_executesql to execute the same query. The use of sp_ executesql is very common in commercial applications because it allows reuse of execution plans.

EXEC sp_executesql 
@stmt = N'SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID',
@params = N'@ProductID int', @ProductID = 870
GO
EXEC sp_executesql 
@stmt = N'SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID',
@params = N'@ProductID int', @ProductID = 897
GO

The first query returns 4688 rows and generates 1241 logical reads. The second, 2 rows but also generates 1241 logical reads. Normally, the query should have generated 10 !!!

read4

Let's look at the two execution plans :

plan4


Both plans are identical !!! What happened? At the first run, an execution plan was generated for constant 870. When run again with constant 897, the query optimizer wants to reuse the execution plan generated for the first run.

By coincidence, the first query used 870 as the constant for the variable @ProductID. Let us now try to reverse the execution.

EXEC sp_executesql 
@stmt = N'SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID',
@params = N'@ProductID int', @ProductID = 897
GO
EXEC sp_executesql 
@stmt = N'SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID',
@params = N'@ProductID int', @ProductID = 870
GO


The first query returns 2 rows and generates 2 logical reads. The second, 4688 rows but 14377 logical reads. The query generated more logical reads than there are a pages in the table.

read5


The solution is to use a plan guide :

EXEC sp_create_plan_guide 
@name = N'SalesOrderDetail_Correction_RECOMPILE',
@stmt = N'SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@ProductID int',
@hints = N'OPTION (RECOMPILE)'
GO

With the use of a plan guide the two queries use the best plans!

read6

plan5

 

 

 

 

Eric Vaillancourt
Author: Eric Vaillancourt
Eric Vaillancourt is an SQL enthusiast. He started his career as a database programmer in 1989. He has been teaching SQL at Technologia since 2002 and has trained more than 5,600 professionals. He leads more than fifteen training sessions focused on Microsoft products. Specializing in high availability environments supporting a large number of transactions per second.

Comments (0)

There are no comments posted here yet

Leave your comments

Posting comment as a guest. Sign up or login to your account.
Attachments (0 / 3)
Share Your Location