Different Execution Plan With Similar Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Different Execution Plan With Similar Query

Author

Topic

AFTIadmin
Starting Member

22 Posts

Posted - 2010-01-13 : 13:16:09

I have a query that is giving me two completely different execution plans when I change the following line:

SELECT * FROM MYTABLE
WHERE (MYTABLE.DATE BETWEEN '1/6/2010' AND '1/14/2010')
WITH
WHERE (MYTABLE.DATE BETWEEN '1/7/2010' AND '1/14/2010')

By changing the date by one day, the new query has a completely different execution plan. I know that if I rebuild all indexes in the database, the execution plan for both query's will be identical. But I can't rebuild often because it takes the DB offline. So instead of INDEX REBUILD's I've been running UPDATE STATISITCS and REORGANIZE INDEX.

How can can I isolate which index is causing the difference in execution, so that I can rebuild only the necessary ones? Or can I just get away with UPDATE STATISITCS and REORGANIZE INDEX?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 13:20:58

can you specify what difference in execution plan you're seeing?

SQL Server MVP

AFTIadmin
Starting Member

22 Posts

Posted - 2010-01-13 : 13:38:45

I tried to hide the nitty gritty details but if it'll help:

//Execution plan that runs as expected using '1/6/2010'
|--Compute Scalar(DEFINE:([Expr1012]=[AFTI_PROD].[dbo].[fnDEVICE_UNIT_CONVERT]([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_DATE],[Expr1014],[AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[UNIT_ID],(72.),[Expr1015]), [Expr1013]=[AFTI_PROD].[dbo].[fnGET_JOURNAL_ENTRY]((2644),[Expr1016],[AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_DATE])))
|--Sort(ORDER BY:([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_DATE] ASC))
|--Compute Scalar(DEFINE:([Expr1014]=CONVERT_IMPLICIT(decimal(19,5),[AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_VALUE],0)))
|--Nested Loops(Inner Join, OUTER REFERENCES:([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIP_VARIABLE_ID]))
|--Hash Match(Inner Join, HASH:([AFTI_PROD].[dbo].[T_VARIABLE_GROUP].[FLOW_VARIABLE_ID])=([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIP_TYPE_VARIABLE_ID]), RESIDUAL:([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIP_TYPE_VARIABLE_ID]=[AFTI_PROD].[dbo].[T_VARIABLE_GROUP].[FLOW_VARIABLE_ID]))
| |--Compute Scalar(DEFINE:([Expr1015]=CONVERT_IMPLICIT(decimal(18,0),[AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIPMENT_ID],0)))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIPMENT_ID]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([AFTI_PROD].[dbo].[T_VARIABLE_GROUP].[EQUIP_TYPE_ID]))
| | | |--Clustered Index Scan(OBJECT:([AFTI_PROD].[dbo].[T_VARIABLE_GROUP].[PK_T_VARIABLE_GROUP]))
| | | |--Index Seek(OBJECT:([AFTI_PROD].[dbo].[T_EQUIPMENT].[IDX_T_EQUIPMENT_EQUIP_TYPE_ID]), SEEK:([AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIP_TYPE_ID]=[AFTI_PROD].[dbo].[T_VARIABLE_GROUP].[EQUIP_TYPE_ID] AND [AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIPMENT_ID]=(2644.)) ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECT:([AFTI_PROD].[dbo].[T_EQUIPMENT].[PK_T_EQUIPMENT]), SEEK:([AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIPMENT_ID]=[AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIPMENT_ID]) LOOKUP ORDERED FORWARD)
| |--Compute Scalar(DEFINE:([Expr1016]=CONVERT_IMPLICIT(int,[AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIP_TYPE_VARIABLE_ID],0)))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIP_VARIABLE_ID], [Expr1017]) WITH UNORDERED PREFETCH)
| |--Index Seek(OBJECT:([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[IDX_T_EQUIPMENT_VARIABLE_4]), SEEK:([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIPMENT_ID]=(2644.)) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[PK_T_EQUIPMENT_VARIABLE]), SEEK:([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIP_VARIABLE_ID]=[AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIP_VARIABLE_ID]) LOOKUP ORDERED FORWARD)
|--Index Seek(OBJECT:([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[IDX_T_EQUIPMENT_LOG_DATA_6]), SEEK:([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[EQUIP_VARIABLE_ID]=[AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIP_VARIABLE_ID] AND [AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_DATE] >= '2010-01-06 00:00:00.000' AND [AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_DATE] <= '2010-01-14 00:00:00.000'), WHERE:([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_VALUE] IS NOT NULL) ORDERED FORWARD)

//Different execution plan, date is set to '1/7/2010'
|--Compute Scalar(DEFINE:([Expr1012]=[AFTI_PROD].[dbo].[fnDEVICE_UNIT_CONVERT]([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_DATE],[Expr1014],[AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[UNIT_ID],(72.),[Expr1015]), [Expr1013]=[AFTI_PROD].[dbo].[fnGET_JOURNAL_ENTRY]((2644),[Expr1016],[AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_DATE])))
|--Compute Scalar(DEFINE:([Expr1015]=CONVERT_IMPLICIT(decimal(18,0),[AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIPMENT_ID],0)))
|--Nested Loops(Inner Join, OUTER REFERENCES:([AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIPMENT_ID]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([AFTI_PROD].[dbo].[T_VARIABLE_GROUP].[EQUIP_TYPE_ID]))
| |--Nested Loops(Inner Join, WHERE:([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIP_TYPE_VARIABLE_ID]=[AFTI_PROD].[dbo].[T_VARIABLE_GROUP].[FLOW_VARIABLE_ID]))
| | |--Compute Scalar(DEFINE:([Expr1016]=CONVERT_IMPLICIT(int,[AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIP_TYPE_VARIABLE_ID],0)))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[EQUIP_VARIABLE_ID]))
| | | |--Compute Scalar(DEFINE:([Expr1014]=CONVERT_IMPLICIT(decimal(19,5),[AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_VALUE],0)))
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[EQUIPMENT_LOG_DATA_ID]))
| | | | |--Index Seek(OBJECT:([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[IDX_T_EQUIPMENT_LOG_DATA_2]), SEEK:([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_DATE] >= '2010-01-07 00:00:00.000' AND [AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_DATE] <= '2010-01-14 00:00:00.000'), WHERE:([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[READ_VALUE] IS NOT NULL) ORDERED FORWARD)
| | | | |--Clustered Index Seek(OBJECT:([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[PK_T_EQUIPMENT_LOG_DATA]), SEEK:([AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[EQUIPMENT_LOG_DATA_ID]=[AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[EQUIPMENT_LOG_DATA_ID]) LOOKUP ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[PK_T_EQUIPMENT_VARIABLE]), SEEK:([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIP_VARIABLE_ID]=[AFTI_PROD].[dbo].[T_EQUIPMENT_LOG_DATA].[EQUIP_VARIABLE_ID]), WHERE:([AFTI_PROD].[dbo].[T_EQUIPMENT_VARIABLE].[EQUIPMENT_ID]=(2644.)) ORDERED FORWARD)
| | |--Clustered Index Scan(OBJECT:([AFTI_PROD].[dbo].[T_VARIABLE_GROUP].[PK_T_VARIABLE_GROUP]))
| |--Index Seek(OBJECT:([AFTI_PROD].[dbo].[T_EQUIPMENT].[IDX_T_EQUIPMENT_EQUIP_TYPE_ID]), SEEK:([AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIP_TYPE_ID]=[AFTI_PROD].[dbo].[T_VARIABLE_GROUP].[EQUIP_TYPE_ID] AND [AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIPMENT_ID]=(2644.)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([AFTI_PROD].[dbo].[T_EQUIPMENT].[PK_T_EQUIPMENT]), SEEK:([AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIPMENT_ID]=[AFTI_PROD].[dbo].[T_EQUIPMENT].[EQUIPMENT_ID]) LOOKUP ORDERED FORWARD)

Kristen
Test

22859 Posts

Posted - 2010-01-13 : 13:53:11

My guess is that this is a table that you are adding data too, chronologically, and the new dates refer to things that are not yet in the Statistics (or the Statistics were made when there were very few records in that range so are actually going to provide a poor hint to the query planner because a lot more data has been added [around that value] since).

I experience this too; we have a logging table that adds millions of rows a day. The plan for the INSERT of a new row (lets face it how hard can that be?!) looks rubbish after a couple of days because all additions have been at one end of the table. (My example is SQL 2000, so probably fixed/improved in later versions). Stop/Start the database, or recompile the Sproc and its fine. But update the Stats and the SProc takes no notice

I don't know the answer, but I'd be interested to learn of things one can do.

We defrag the indexes, and rebuild Stats (using FULL SCAN, not a sample) every night.

(We do have a smart solution: elapsed runtime of all Sprocs is monitored, and once an Sproc falls before an average execution threshold it is recompiled automatically; it was a PITA to build, but there! its done now)

"I can't rebuild often because it takes the DB offline"

is there any need to REBUILD? I think Defrag (as was in SQL 2000) or ALTER INDEX REORGANIZE (SQL 2005 / 2008) does a perfectly acceptable job. Indeed, REBUILD tends to move the index to the end of the MDF, often extending the file, and leaving a huge hole in the middle of the MDF, and then some well-meaning twit comes along and panics and Shrinks the MDF which completely re-frags the Index

I think UPDATE STATS will sort this, but the query will need to be Recompiled (unless SQL 2008 is a whole lot better [than SQL 2000 was] at cascading the fact that the change to Stats gets cascaded to all the cached query plans which need to be re-planned.)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-13 : 14:56:05

It's common where many inserts by date or by identity value. As Kristen said, the newer records are not reflected in the stats yet.

Update with fullscan will resolve this

AFTIadmin
Starting Member

22 Posts

Posted - 2010-01-13 : 15:36:04

The UPDATE STATISTICS FULLSCAN worked. Now both execution plans are identical.
Thanks!

Kristen
Test

22859 Posts

Posted - 2010-01-14 : 03:45:04

Trouble is ... the Statistics will go out of date

But I know you know that ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources