Query Processing Question
-
I'm trying to wrap my head around this.
Using this sample data:
SELECT x=1 INTO #temp;If I run the following two queries:
DECLARE @n int = -1;-- Query #1
SELECT TOP (@n) * FROM #temp WHERE @n > 0;
-- Query #2
WITH CTE AS (SELECT TOP (@n) * FROM #temp)
SELECT * FROM CTE WHERE @n > 0;
Query #1 errors out with "Msg 127, Level 15, State 1, Line 7
A TOP N or FETCH rowcount value may not be negative."
Query #2 returns 0 rows
When I look at the estimated query plan or change @n to 1 (to keep Query #1 from bombing out) and run the query with "Include Execution Plan turned on" I get the this:
I think I understand why the first query errors out and the second does not. I think query#1 fails because the TOP clause is evaluated first and #2 does not error out because moving the WHERE clause outside of the CTE causes the optimizer to evaluate the WHERE clause first. This is just a guess though.
But then if I run query #1 with OPTION (RECOMPILE) it does not error out and, instead, returns 0 rows.
If have have searched every SQL book I have looking for an explanation including:
SQL Server Execution Plans by Grant Fritchey
Inside the Query Optimizer by Benjamin Nevarez
All my Ben-Gran books (all talk about Logic Query Processing but nothing that sheds light on this)
Every article about Query Processing in Books Online
An extensive google search does not turn up anything about this... .
So I have two questions.
1. Can someone explain this behavior?
and/or
2. Is there a good article or book that explains this specifically?
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
Quick thought, with the negative top directive the estimated execution plan is the same as the input plan (only translation) without any optimization. Change the top directive to n>0 and the output will be the same as the output after the optimization process. This indicates that no optimization is done on the invalid/negative output without the recompile option. If the top directive is set to zero (0) then the output is the same as when negated but without the error.
By adding the recompile, the optimizer takes the plan through the whole process of simplification, join collapsing and Project normalization, resulting in a simple constant scan which does not even touch the table or the top operator, hence no error and no output from the select.
😎
Below is my method of poking slightly under the hood (kudos Ian Jose/Benjamin Nevarez), fairly safe 😉 start by playing around with that.
/* Safe place */USE tempdb;
GO
SET NOCOUNT ON;
GO
/*
Use CTRL+T to redirect both messages and output to
the same tab in SSMS.
*/
IF OBJECT_ID(N'tempdb..#temp') IS NOT NULL DROP TABLE #temp;
SELECT x=1 INTO #temp;
DECLARE @n int = -1;
-- Query #1
SELECT TOP (@n) * FROM #temp WHERE @n > 0
OPTION
(
QUERYTRACEON 3604 -- Enable tree output to the Messages tab
,RECOMPILE
--,QUERYTRACEON 8605 -- Converted tree (input)
--,QUERYTRACEON 8606 -- Optimization process trees
--,QUERYTRACEON 8607 -- Output tree
--,QUERYTRACEON 8675 -- Optimization phases
--,QUERYTRACEON 2372 -- Optimization stage memory utilization
--,QUERYTRACEON 2373 -- Optimization operator memory utilization
--,QUERYTRACEON 8757 -- Skip trivial plan optimization
);
-- Query #2
;WITH CTE AS (SELECT TOP (@n) * FROM #temp)
SELECT * FROM CTE WHERE @n > 0
OPTION
(
QUERYTRACEON 3604 -- Enable tree output to the Messages tab
,RECOMPILE
--,QUERYTRACEON 8605 -- Converted tree (input)
--,QUERYTRACEON 8606 -- Optimization process trees
--,QUERYTRACEON 8607 -- Output tree
--,QUERYTRACEON 8675 -- Optimization phases
--,QUERYTRACEON 2372 -- Optimization stage memory utilization
--,QUERYTRACEON 2373 -- Optimization operator memory utilization
--,QUERYTRACEON 8757 -- Skip trivial plan optimization
);
-
Thanks Eirikur, you always seen to have the answer. Very interesting stuff - I have never really played around any Undocumented Query Optimizer Trace Flags (I used to play around with 8649 until I found Adam Mechanics make_parallel function). What you posted was new to me and has led to some interesting follow-up reading.
It's cool that how I can run that first query (the one that bombs out) but still see what was going on under the hood.
While researching this a little more I actually ran across this video of Benjamin Nevarez talking about these trace flags. I'm going to try to watch it this weekend. Thanks again for replying.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
I ran the code from Eirikur on 2014 instance and did not receive an error.
-
FYI regarding my post. I am using SSMS 2012 but got the error on a 2014 server (probably would have been more appropriate to post this under 2014).
brad.mason5 (9/11/2015)
I ran the code from Eirikur on 2014 instance and did not receive an error.If you ran query #1 with recompile on you won't get an error (that's how it is on my system anyhow). If you comment out the recompile statement and get an error it would be at the end of the text in the message tab.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
Ok Thanks Alan. I do get the error when I comment out Recompile.
-
Further on the topic, the input tree is generated during the parsing and binding phase. Since the query processor does not evaluate variable values during this stage, this step produces no errors. If the @n would be changed to a literal value of (-1), the query would error out here. The negative variable value is presented in the input tree as a variable (XVAR) passed to a constant scan operator (ScaOp_Const). When the logical tree is passed to the optimizer, the optimizer realizes that the query will not produce any meaningful output and simplifies the query into the operation of only fetching the meta-data (column headers) and returning an empty set.
😎
NOTE that this is a breaking behaviour changes from 2005 - 2008 and 2012 and later. In 2008 the logical input tree does not contain the value of the variable, hence the optimizer does not realize that it will produce no valid output during the optimization phases and the query will error out when it attempts to do the plan generation/costing. Any code relying on TRY/CATCH for invalid input can therefore exhibit different behaviour when run on 2012 and later. I found this out the hard way when doing an upgrade work few years back :pinch:
We can observe this using traceflag 8606
-- SQL Server 2012
******************************************* Input Tree: ***
LogOp_Top NoTies
LogOp_Project QCOL: [tempdb].[dbo].[#temp].x
LogOp_Select
LogOp_Get TBL: #temp #temp TableID=-1268127970 TableReferenceID=0 IsRow: COL: IsBaseRow1001
ScaOp_Const TI(bit,Null,ML=1) XVAR(bit,Not Owned,Value=0)
AncOp_PrjList
ScaOp_Const TI(bigint,Null,ML=8) XVAR(bigint,Not Owned,Value=-1)
ScaOp_Const TI(bigint,ML=8) XVAR(bigint,Not Owned,Value=0)
*******************
*** Simplified Tree: ***
LogOp_ConstTableGet (0) COL: Bmk1000 COL: IsBaseRow1001 QCOL: [tempdb].[dbo].[#temp].x
*******************
*** Join-collapsed Tree: ***
LogOp_ConstTableGet (0) COL: Bmk1000 COL: IsBaseRow1001 QCOL: [tempdb].[dbo].[#temp].x
*******************
*** Tree Before Project Normalization ***
LogOp_ConstTableGet (0) COL: Bmk1000 COL: IsBaseRow1001 QCOL: [tempdb].[dbo].[#temp].x
*****************************************
*** Tree After Project Normalization ***
LogOp_ConstTableGet (0) COL: Bmk1000 COL: IsBaseRow1001 QCOL: [tempdb].[dbo].[#temp].x
-- SQL Server 2008R2
******************************************* Input Tree: ***
LogOp_Top NoTies
LogOp_Project QCOL: [tempdb].[dbo].[#temp].x
LogOp_Select
LogOp_Get TBL: #temp #temp TableID=383826814 TableReferenceID=0 IsRow: COL: IsBaseRow1002
ScaOp_Identifier COL: ConstExpr1004
AncOp_PrjList
ScaOp_Identifier COL: ConstExpr1005
*******************
*** Simplified Tree: ***
LogOp_Top NoTies
LogOp_Select
LogOp_Get TBL: #temp #temp TableID=383826814 TableReferenceID=0 IsRow: COL: IsBaseRow1002
ScaOp_Identifier COL: ConstExpr1004
ScaOp_Identifier COL: ConstExpr1005
*******************
*** Join-collapsed Tree: ***
LogOp_Top NoTies
LogOp_Select
LogOp_Get TBL: #temp #temp TableID=383826814 TableReferenceID=0 IsRow: COL: IsBaseRow1002
ScaOp_Identifier COL: ConstExpr1004
ScaOp_Identifier COL: ConstExpr1005
*******************
*** Tree Before Project Normalization ***
LogOp_Top NoTies
LogOp_Select
LogOp_Get TBL: #temp #temp TableID=383826814 TableReferenceID=0 IsRow: COL: IsBaseRow1002
ScaOp_Identifier COL: ConstExpr1004
ScaOp_Identifier COL: ConstExpr1005
*****************************************
*** Tree After Project Normalization ***
LogOp_Top NoTies
LogOp_Select
LogOp_Get TBL: #temp #temp TableID=383826814 TableReferenceID=0 IsRow: COL: IsBaseRow1002
ScaOp_Identifier COL: ConstExpr1004
ScaOp_Identifier COL: ConstExpr1005
Msg 127, Level 15, State 1, Line 12
A TOP N value may not be negative.
-----------------------------------------------
-
Alan.B (9/11/2015)
Thanks Eirikur, you always seen to have the answer. Very interesting stuff - I have never really played around any Undocumented Query Optimizer Trace Flags (I used to play around with 8649 until I found Adam Mechanics make_parallel function). What you posted was new to me and has led to some interesting follow-up reading.It's cool that how I can run that first query (the one that bombs out) but still see what was going on under the hood.
While researching this a little more I actually ran across this video of Benjamin Nevarez talking about these trace flags. I'm going to try to watch it this weekend. Thanks again for replying.
You are welcome Alan! I did see Benjamin's session at the SQLBits, highly recommend it!
😎
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply