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.
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!