Estimated vs. Actual
-
Does anyone have a query that can reasonably consistently generate two different plans between estimated and actual? I'm trying to come up with an example and I just can't find one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
You could maybe force one if you have outdated statistics? Can't say I have ever noticed one though, but not like I been keeping an eye open for it...
-
I'm thinking I'll try dropping the statistics on a table and see what happens. I'm trying to get a different plan, not just subtle differences in the plan (different row counts for example).
Any one else have any guesses?
I'll post the results of dropping the statistics.
Thanks for the help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
OK. Weak theory completely destroyed by reality. You can't drop statistics created by indexes and the other statistics just don't seem to be affecting the plan much. More ideas needed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Just tossing out ideas here mind you....
How about a query that has a where clause on an XML data type? Those seems to have the most messed up query plans I have ever seen....
-
Ha!
Well, I'm not going for messed up, but it's worth a shot. I'll let you know.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Not sure this is what you are looking for... but I don't think you find a more dramatic difference...
DECLARE @Counter INT --Just a loop counter
DECLARE @Bitbucket INT --Just a place to store data
DECLARE @StartTime DATETIME --Just to measure duration
--===== Do the WHILE loop test of counting to 1 million
PRINT 'WHILE LOOP TEST...'
SET @StartTime = GETDATE() --Start the duration timer
SET @Counter = 1
SET @Bitbucket = 0
WHILE @Counter <= 1000000
BEGIN
SELECT @Bitbucket = @Bitbucket + 1
SET @Counter = @Counter + 1
END
-- Display the stats
PRINT STR(@BitBucket) + ' The final count (just to show work was done)'
PRINT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Duration in milliseconds'
PRINT REPLICATE('-',78)
It's one of the reasons I avoid loops
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Two identical plans:
<
ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.3042.00">
<
BatchSequence>
<
Batch>
<
Statements>
<
StmtSimple StatementText="DECLARE @Counter INT --Just a loop counter DECLARE @Bitbucket INT --Just a place to store data DECLARE @StartTime DATETIME --Just to measure duration --===== Do the WHILE loop test of counting to 1 million PRINT 'WHILE LOOP TEST...' " StatementId="1" StatementCompId="1" StatementType="PRINT" />
<
StmtSimple StatementText=" SET @StartTime = GETDATE() --Start the duration timer " StatementId="2" StatementCompId="2" StatementType="ASSIGN" />
<
StmtSimple StatementText=" SET @Counter = 1 " StatementId="3" StatementCompId="3" StatementType="ASSIGN" />
<
StmtSimple StatementText=" SET @Bitbucket = 0 " StatementId="4" StatementCompId="4" StatementType="ASSIGN" />
<
StmtCond StatementText=" WHILE @Counter <= 1000000 " StatementId="5" StatementCompId="5" StatementType="COND">
<
Condition />
<
Then>
<
Statements>
<
StmtSimple StatementText=" BEGIN SELECT @Bitbucket = @Bitbucket + 1 " StatementId="6" StatementCompId="6" StatementType="ASSIGN" />
<
StmtSimple StatementText=" SET @Counter = @Counter + 1 " StatementId="7" StatementCompId="7" StatementType="ASSIGN" />
</
Statements>
</
Then>
</
StmtCond>
<
StmtSimple StatementText=" END -- Display the stats PRINT STR(@BitBucket) + ' The final count (just to show work was done)' " StatementId="8" StatementCompId="10" StatementType="PRINT" />
<
StmtSimple StatementText=" PRINT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Duration in milliseconds' " StatementId="9" StatementCompId="11" StatementType="PRINT" />
<
StmtSimple StatementText=" PRINT REPLICATE('-',78) " StatementId="10" StatementCompId="12" StatementType="PRINT" />
</
Statements>
</
Batch>
</
BatchSequence>
</
ShowPlanXML>
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I'm a little spacy this morning (production problems overnight), so maybe I'm doing something stupid here.
In desperation to try to find an answer this question, I hit up a person and asked her. She suggested that having two batches, one that updates a ton of data and one that selects from that data will work. So, I fired up AdventureWorks. If run these two queries, estimated or actual, I get two different plans:
SELECT
[OrderQty], [CarrierTrackingNumber]
FROM [Sales].[SalesOrderDetail]
WHERE [ProductID] = 897
Gets an index seek and a key lookup joined by a loop join
SELECT [OrderQty], [CarrierTrackingNumber]
FROM [Sales].[SalesOrderDetail]
WHERE [ProductID] = 870
This gets a clustered index scan.
So, I wrote up this little bit of code to manipulate the data such that the query against 897 should result in a scan (I had hoped).
BEGIN
TRAN
INSERT INTO [Sales].[SpecialOfferProduct]
([SpecialOfferID],[ProductID])
VALUES (2,897)
INSERT
INTO [Sales].[SpecialOfferProduct]
([SpecialOfferID],[ProductID])
VALUES (3,897)
INSERT INTO [Sales].[SpecialOfferProduct]
([SpecialOfferID],[ProductID])
VALUES (4,897)
UPDATE
[Sales].[SalesOrderDetail]
SET [ProductID] = 897
WHERE [ProductID] = 870
SELECT
[OrderQty], [CarrierTrackingNumber]
FROM [Sales].[SalesOrderDetail]
WHERE [ProductID] = 897
--ROLLBACK TRAN
I've got the rollback there so that I can throw away the updates after I'm done. The execution plan for the select is the same even though instead of accessing two rows as it did in the original, it now access 4,000+ rows. The only other bit of data I can provide is that the queries are doing simple parameterization... Maybe I should mess with that a bit... I'll try it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Crud. Now parameter sniffing is taking part. If I take away the constant values & substitute a parameter I get clustered scans for everything...
**^& %^*%&% *%&$^& $*^%& ##%#!
And I really mean that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
May I be as bold as asking why you are trying to do this? Just to prove that you should not trust the estimated query plan when you are working on optimizations?
-
Basically, yes. I'm working on something and rather than simply caution that differences could arise, I'd like to show one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Maybe in SQL Server 2005... in 2000 the estimated execution plan show "something" and the actual execution plan shows absolutely nothing.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It's funny you say that. The reason I posted an XML plan was that the graphical plans were acting all wonky with that script. There's a mystery I'm going to skip over.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
With some help from a person, here's one that works (by works I mean we get two different plans).
IF
EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[NewOrders]')
AND type in ( N'U' ) )
DROP TABLE [NewOrders]
GO
SELECT
*
INTO NewOrders
FROM Sales.SalesOrderDetail
GO
CREATE INDEX IX_NewOrders_ProductID on NewOrders ( ProductID )
GO
SET SHOWPLAN_XML ON
-- Estimated Plan
GO
SELECT [OrderQty]
,[CarrierTrackingNumber]
FROM NewOrders
WHERE [ProductID] = 897
GO
SET SHOWPLAN_XML OFF
GO
BEGIN TRAN
UPDATE NewOrders
SET [ProductID] = 897
WHERE [ProductID] between 800 and 900
GO
SET STATISTICS XML ON
-- Actual Plan
GO
SELECT [OrderQty]
,[CarrierTrackingNumber]
FROM NewOrders
WHERE [ProductID] = 897
ROLLBACK TRAN
GO
SET STATISTICS XML OFF
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply