Performance Tuning Series: Part 4

In the Part 3 of the series, we have learned about the Sorts, Hash matches and Serial nested loops. We have seen when they can be useful and when they needs to be avoided with examples. In this session let’s learn about Seek and Residual Predicates with examples.

Lets take an example of a very simple query running on the BigTransactionHistory table and check for the execution plan.

Credits: Adam Machanic

SELECT * FROM 
bigTransactionHistory as bth
WHERE
ProductId BETWEEN 1001 AND 50001
AND Actualcost>5000000

When you check the execution plan, Estimates are off here. Estimated number of rows were 80 but the actual number of rows were 0. Did you observe, we have parallelism here? Parallelism is used for high cot query plans but why for this simple query?

Run the same query by checking the statistics this time

SET STATISTICS IO ON
GO
SELECT * FROM 
bigTransactionHistory as bth
WHERE
ProductId BETWEEN 1001 AND 50001
AND Actualcost>5000000

SQL Server has to read 131444 logical reads which is a lot.

There are two types of predicates in SQL Server. Index predicates and Residual predicates. Residual predicates are the predicates which are not satisfied by the index but they are still be pushed to the storage engine. When you observe the predicate information in the execution plan here, you see the plan shows the index predicates (seek predicate) on the ProductID and the storage engine instead of the query processor gave us the residual predicate. It filtered the actual cost in the storage engine and did not pass the rows to the query processor.

So what are we missing here? How to track where the issue is? We can see the line passing from the Index seek to parallelism operator, it is so thin. These lines can be really useful in tracking down where the issues are when your execution plans are big so we do not have to go and look each and every iterator in the plan. Though the residual predicate make us harder to debug the query plan faster but it is really helpful to run the query faster. But, if you wanted to test the query performance in lower environments, enabling the TraceFlag 9130 can be really helpful in debugging the plan easily. Now what this trace flag does it, it will disable the residual predicates and force the query optimizer to process the residual predicates as a filter in the query processor. When you enable this Traceflag and run the same query, look at the difference in the line. Enabling this traceflag is useful when you have lot of I/O input and we cannot figure out where the issue is.

Warning: Do not enable this Traceflag 9130 in production because this will cause query slowness. Enable this in non-production servers to quickly debug the queries. This is a Gem for Query tuning developers and DBA’s.

Sometimes when you try to run your queries searching on the predicates more than once on the same column of the table, then SQL server will go ahead and use the seek predicate on one of the predicate and do the residual predicate on the other. This may not be the good thing to do. Instead, we can use the case expressions and modify the code.

Below is one of the examples using the same predicate on same column:

CREATE TABLE #Purchaseorders
(
[SalesOrderID] INT,
[OrderDate] DATETIME,
[DueDate] DATETIME,
--Primary key ([SalesOrderID])
)

INSERT #Purchaseorders
SELECT 
[SalesOrderID],
CASE
	WHEN [SalesOrderID] BETWEEN 43659 AND 43701 THEN '20110531'
	WHEN [SalesOrderID] BETWEEN 43702 AND 43705 THEN '20110601'
	WHEN [SalesOrderID] BETWEEN 54990 AND 55990 THEN '20081110'
	END,
	'20110612'
FROM [Sales].[SalesOrderDetail]
GO

dbcc dropcleanbuffers

DECLARE @OrderDate DATE='20110531'
SELECT  r.*
FROM #Purchaseorders as r
CROSS APPLY
(
SELECT TOP (1000)
SOH.[OrderDate],
SOH.[SalesOrderID],
SOH.[TotalDue]
FROM [Sales].[SalesOrderHeader] AS SOH
WHERE SOH.SalesOrderID=r.SalesOrderID
AND SOH.[OrderDate] BETWEEN r.OrderDate AND r.DueDate
AND SOH.[OrderDate] >= @OrderDate
ORDER BY 
SOH.[OrderDate]
) AS a
GO

Instead of using both the predicates on same column, we can replace the query to use case expression by modifying the code and check for the execution plan.

DECLARE @OrderDate DATE='20110531'
SELECT  r.*
FROM #Purchaseorders as r
CROSS APPLY
(
SELECT TOP (1000)
SOH.[OrderDate],
SOH.[SalesOrderID],
SOH.[TotalDue]
FROM [Sales].[SalesOrderHeader] AS SOH
WHERE SOH.SalesOrderID=r.SalesOrderID
AND SOH.[OrderDate] BETWEEN r.OrderDate AND r.DueDate
AND SOH.[OrderDate] >= @OrderDate
ORDER BY 
SOH.[OrderDate]
) AS a
GO

Check the execution plan now, optimizer decided to use both of them as the residual predicate in this scenario but its not we who made sql optimizer confuse to use seek predicate or residual predicate. Its the optimizer choice to use residual predicate here.

Summary:

In this post of the Performance tuning series, we have gone through the Seek and the Residual predicates and learned about how enabling the Traceflag 9130 can be really helpful in debugging the plan easily on your non-production servers as it will disable the residual predicates and force the query optimizer to process the residual predicates as a filter in the query processor. You can see the thick lines in your query plans to quickly investigate into where the issue is.

Thanks for reading and don’t forget to subscribe to this website, you have WANT TO BE NOTIFIED? section on right side of the main page where you can enter your email address to receive new blog posts notifications to your email address every week!

2 thoughts on “Performance Tuning Series: Part 4

  1. You are correct! We do not have the ability to enable any trace flags on platform as a service. We get the error even if we try to use query hint QUERYTRACEON at the query level. As everything related to the server is taken care of by the Microsoft, I believe we do not have any option for now.
    For Azure SQL Database Managed Instance, we do have the ability to enable the global trace flags. Hope this helps.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s