June 4, 2010 at 3:03 pm
I am preparing for our production upgrade from SQL Server 2005 to SQL Server 2008, and I formulated some queries to run as a benchmark test to be used before and after I upgrade. Fortunately (or unfortunately) I picked the right query to use. It is actually a view that I query by itself and it causes a different execution plan in SQL Server 2008 than in SQL Server 2005, and it runs in 15 seconds in 2005 and I stopped after 15 minutes in 2008.
It is the exact same database, and the exact same compatibility level (90). After the upgrade, I rebuilt indexes, updated statistics (even for the underlying tables), and updated usage stats. I always received a long-running query no matter when I tried the query, and the query execution plan was always different. I am attaching the different execution plans.
The execution plan in 2008 did mention a missing index, and after building that new index my timing problem was solved. It didn't change the execution plan.
Are query parsing routines and query optimization plans separate entities? I was expecting compatibility level 90 in SQL 2008 to guarantee the same performance as in SQL 2005.
Also, this issue affects an application that has a database on our server. Those application support people were told their application was not supported on SQL 2008, but we convinced them compatibility level 90 would mean there would be no affect to their database functionality. Is a SQL 2005-certified application just not going to be guaranteed to run the same in compatibility mode 90 in SQL 2008?
Thanks,
Scott
SQL Server DBA (more like SQL Server Admin than DBA to be honest, because our DBOs play the DBA role in a way - anyone else have that problem?)
June 4, 2010 at 6:22 pm
sgalbra (6/4/2010)
The execution plan in 2008 did mention a missing index, and after building that new index my timing problem was solved. It didn't change the execution plan.
Shall I understand that with or without that index exec plan was the same? :blink: and, after creating the "missing" index peformance got better? ... with the same exec plan performance was misserable before?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 7, 2010 at 1:22 pm
PaulB-TheOneAndOnly (6/4/2010)
Shall I understand that with or without that index exec plan was the same? :blink: and, after creating the "missing" index peformance got better? ... with the same exec plan performance was misserable before?
Yes that is correct. The execution plan in 2008 was the same before and after I created the index, and it ran much faster after then index was created. However, this was mostly irrelevant information regarding my main issues. I was not expecting the behavior within compatibility level 90 to be different in SQL 2005 and SQL 2008.
June 8, 2010 at 12:02 am
sgalbra (6/4/2010)
It is the exact same database, and the exact same compatibility level (90). After the upgrade, I rebuilt indexes, updated statistics (even for the underlying tables), and updated usage stats. I always received a long-running query no matter when I tried the query, and the query execution plan was always different. I am attaching the different execution plans.
It is difficult to reconcile the fact that you rebuilt indexes and statistics with the plans produced. The 2005 plan estimates a large number of rows and produces a parallel plan with an index spool. The 2008 plan estimates 1 row from both index scans and logically produces a serial plan with no spool. The inescapable conclusion is that good statistics were not available when the 2008 plan was captured.
I was expecting compatibility level 90 in SQL 2008 to guarantee the same performance as in SQL 2005.
Compatibility levels are simply there to ensure that queries produce the same results (using the same syntax) as on the compatible version selected. The query is still executed using the new engine. None of that changes the fact that a poor plan was produced in your case due to inadequate or missing statistics.
Also, this issue affects an application that has a database on our server. Those application support people were told their application was not supported on SQL 2008, but we convinced them compatibility level 90 would mean there would be no affect to their database functionality.
You were mistaken. Setting the compatibility level increases the chances that the 2005-targeted code will run, and produce correct results, it does not guarantee that it will do so, or that performance will be optimal. Further, if the 2005 code relies on undocumented behaviour, or elements that are not covered by the compatibility mode, it might not run at all, or produce incorrect results. Books Online contains a lot of information about all this. A good place to start is: Considerations for Upgrading the Database Engine (and linked pages).
Is a SQL 2005-certified application just not going to be guaranteed to run the same in compatibility mode 90 in SQL 2008?
Correct. Setting the compatibility mode to 90 increases the chances of success, but does not guarantee it. It is important to test thoroughly before any upgrade.
I would like to stress that I don't think the compatibility mode thing was the source of the problem here. The poor plan was produced due to missing or inadequate statistics.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 8, 2010 at 3:41 pm
Thank you, Paul, for your time and your response. You cleared up my confusion about compatibility levels. If nothing I wanted this topic to be discussed and should have titled my discussion as such. I was believing the compatibility levels would each have an entirely different engine for parsing and executing the queries because each has its own folder in the Windows OS. However, I think your response plus this example reinforces that query optimization is entirely tied to the SQL Server instance and not the database compatibility level.
Paul White NZ (6/8/2010)
I would like to stress that I don't think the compatibility mode thing was the source of the problem here. The poor plan was produced due to missing or inadequate statistics.
Paul
I'm not sure I understand this statement. The statistics were re-created on the new server for the database, and the underlying table. I also flushed out the query plan cache and updated dbusage. Regardless, I always received the same query plan in 2008 whether stats were created or not.
So I think the poor performance is due to changes in the query optimization behavior in 2008. To further test this out I tried to force the 2008 query to use the 2005 execution plan. After generating the XML text of the execution plan in 2005, I inserted it into a hint in 2008 as such:
select count(*)
from DBTRN.dbo.added_objects_past_month_vw
OPTION (USE PLAN N'<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.4053.00"><BatchSequence><Batch><Statements><StmtSimple StatementText="select * from dbo.added_objects_past_month_vw" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="251.262" StatementEstRows="276562" StatementOptmLevel="FULL"><StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false"/><QueryPlan CachedPlanSize="92" CompileTime="57" CompileCPU="50" CompileMemory="1632"><RelOp NodeId="0" PhysicalOp="Parallelism" LogicalOp="Gather Streams" EstimateRows="276562" EstimateIO="0" EstimateCPU="4.19954" AvgRowSize="323" EstimatedTotalSubtreeCost="251.262" Parallel="1" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="Run_day"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="dbname"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="name"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="object_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="principal_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="parent_object_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="type"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="type_desc"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="create_date"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="modify_date"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_ms_shipped"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_published"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_schema_published"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_name"/></OutputList><Parallelism><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Left Anti Semi Join" EstimateRows="276562" EstimateIO="0" EstimateCPU="0.578014" AvgRowSize="323" EstimatedTotalSubtreeCost="247.062" Parallel="1" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="Run_day"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="dbname"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="name"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="object_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="principal_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="parent_object_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="type"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="type_desc"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="create_date"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="modify_date"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_ms_shipped"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_published"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_schema_published"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_name"/></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="Run_day"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="dbname"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="name"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_id"/></OuterReferences><RelOp NodeId="3" PhysicalOp="Top" LogicalOp="Top" EstimateRows="276562" EstimateIO="0" EstimateCPU="0.00554908" AvgRowSize="323" EstimatedTotalSubtreeCost="27.3909" Parallel="1" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="Run_day"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="dbname"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="name"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="object_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="principal_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="parent_object_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="type"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="type_desc"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="create_date"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="modify_date"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_ms_shipped"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_published"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_schema_published"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_name"/></OutputList><Top RowCount="0" Rows="1" IsPercent="0" WithTies="1"><TieColumns><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="dbname"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="name"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="Run_day"/></TieColumns><RelOp NodeId="4" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="278346" EstimateIO="0" EstimateCPU="0.0221963" AvgRowSize="323" EstimatedTotalSubtreeCost="27.3853" Parallel="1" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="Run_day"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="dbname"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="name"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="object_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="principal_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="parent_object_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="type"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="type_desc"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="create_date"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="modify_date"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_ms_shipped"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_published"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_schema_published"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_name"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="dbname"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="name"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="Run_day"/><ColumnReference Column="Segment1023"/></OutputList><Segment><GroupBy><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="dbname"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="name"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_id"/></GroupBy><SegmentColumn><ColumnReference Column="Segment1023"/></SegmentColumn><RelOp NodeId="5" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="278346" EstimateIO="0.00563063" EstimateCPU="11.5538" AvgRowSize="323" EstimatedTotalSubtreeCost="27.3631" Parallel="1" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="Run_day"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="dbname"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="name"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="object_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="principal_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="parent_object_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="type"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="type_desc"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="create_date"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="modify_date"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_ms_shipped"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_published"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_schema_published"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_name"/></OutputList><MemoryFractions Input="1" Output="1"/><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="0"><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="dbname"/></OrderByColumn><OrderByColumn Ascending="0"><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="name"/></OrderByColumn><OrderByColumn Ascending="0"><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_id"/></OrderByColumn><OrderByColumn Ascending="0"><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="Run_day"/></OrderByColumn></OrderBy><RelOp NodeId="6" PhysicalOp="Parallelism" LogicalOp="Repartition Streams" EstimateRows="278346" EstimateIO="0" EstimateCPU="6.09807" AvgRowSize="323" EstimatedTotalSubtreeCost="15.8037" Parallel="1" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="Run_day"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="dbname"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="name"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="object_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="principal_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="parent_object_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="type"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="type_desc"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="create_date"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="modify_date"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_ms_shipped"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_published"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_schema_published"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_name"/></OutputList><Parallelism PartitioningType="Hash"><PartitionColumns><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="dbname"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="name"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_id"/></PartitionColumns><RelOp NodeId="7" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="278346" EstimateIO="9.19061" EstimateCPU="0.301362" AvgRowSize="323" EstimatedTotalSubtreeCost="9.49197" Parallel="1" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="Run_day"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="dbname"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="name"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="object_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="principal_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="parent_object_id"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="type"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="type_desc"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="create_date"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="modify_date"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_ms_shipped"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_published"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_schema_published"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_name"/></OutputList><TableScan Ordered="0" ForcedIndex="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="Run_day"/></DefinedValue><DefinedValue><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="dbname"/></DefinedValue><DefinedValue><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="name"/></DefinedValue><DefinedValue><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="object_id"/></DefinedValue><DefinedValue><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="principal_id"/></DefinedValue><DefinedValue><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_id"/></DefinedValue><DefinedValue><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="parent_object_id"/></DefinedValue><DefinedValue><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="type"/></DefinedValue><DefinedValue><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="type_desc"/></DefinedValue><DefinedValue><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="create_date"/></DefinedValue><DefinedValue><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="modify_date"/></DefinedValue><DefinedValue><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_ms_shipped"/></DefinedValue><DefinedValue><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_published"/></DefinedValue><DefinedValue><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="is_schema_published"/></DefinedValue><DefinedValue><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_name"/></DefinedValue></DefinedValues><Object Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" TableReferenceId="-1"/><Predicate><ScalarOperator ScalarString="dateadd(day,(27),[DBTRN].[dbo].[SYS_ALL_OBJECTS_ARCHIVE].[Run_day] as .[Run_day])>getdate()"><Compare CompareOp="GT"><ScalarOperator><Intrinsic FunctionName="dateadd"><ScalarOperator><Const ConstValue="(4)"/></ScalarOperator><ScalarOperator><Const ConstValue="(27)"/></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="Run_day"/></Identifier></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="ConstExpr1017"><ScalarOperator><Intrinsic FunctionName="getdate"/></ScalarOperator></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate></TableScan></RelOp></Parallelism></RelOp></Sort></RelOp></Segment></RelOp></Top></RelOp><RelOp NodeId="10" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="219.093" Parallel="1" EstimateRebinds="276561" EstimateRewinds="0"><OutputList/><NestedLoops Optimized="0"><OuterReferences><ColumnReference Column="Expr1014"/></OuterReferences><RelOp NodeId="11" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="1.1e-006" AvgRowSize="15" EstimatedTotalSubtreeCost="150.794" Parallel="1" EstimateRebinds="276561" EstimateRewinds="0"><OutputList><ColumnReference Column="Expr1014"/></OutputList><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1014"/><ScalarOperator ScalarString="MAX([DBTRN].[dbo].[SYS_ALL_OBJECTS_ARCHIVE].[Run_day] as [d].[Run_day])"><Aggregate Distinct="0" AggType="MAX"><ScalarOperator><Identifier><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="[d]" Column="Run_day"/></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="12" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="7.8e-007" AvgRowSize="15" EstimatedTotalSubtreeCost="150.49" Parallel="1" EstimateRebinds="276561" EstimateRewinds="0"><OutputList><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="[d]" Column="Run_day"/></OutputList><Filter StartupExpression="0"><RelOp NodeId="13" PhysicalOp="Index Spool" LogicalOp="Eager Spool" EstimateRows="1" EstimateIO="49.3645" EstimateCPU="0.548118" AvgRowSize="15" EstimatedTotalSubtreeCost="150.274" Parallel="1" EstimateRebinds="276561" EstimateRewinds="0"><OutputList><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="[d]" Column="Run_day"/></OutputList><Spool><SeekPredicate><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="[d]" Column="dbname"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="[d]" Column="name"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="[d]" Column="schema_id"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[DBTRN].[dbo].[SYS_ALL_OBJECTS_ARCHIVE].[dbname] as .[dbname]"><Identifier><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="dbname"/></Identifier></ScalarOperator><ScalarOperator ScalarString="[DBTRN].[dbo].[SYS_ALL_OBJECTS_ARCHIVE].[name] as .[name]"><Identifier><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="name"/></Identifier></ScalarOperator><ScalarOperator ScalarString="[DBTRN].[dbo].[SYS_ALL_OBJECTS_ARCHIVE].[schema_id] as .[schema_id]"><Identifier><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_id"/></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekPredicate><RelOp NodeId="14" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="547860" EstimateIO="5.5535" EstimateCPU="0.602803" AvgRowSize="99" EstimatedTotalSubtreeCost="6.1563" Parallel="1" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="[d]" Column="Run_day"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="[d]" Column="dbname"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="[d]" Column="name"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="[d]" Column="schema_id"/></OutputList><IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="[d]" Column="Run_day"/></DefinedValue><DefinedValue><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="[d]" Column="dbname"/></DefinedValue><DefinedValue><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="[d]" Column="name"/></DefinedValue><DefinedValue><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="[d]" Column="schema_id"/></DefinedValue></DefinedValues><Object Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Index="[missing_index_51084]" Alias="[d]"/></IndexScan></RelOp></Spool></RelOp><Predicate><ScalarOperator ScalarString="dateadd(day,(27),[DBTRN].[dbo].[SYS_ALL_OBJECTS_ARCHIVE].[Run_day] as [d].[Run_day])<[DBTRN].[dbo].[SYS_ALL_OBJECTS_ARCHIVE].[Run_day] as .[Run_day]"><Compare CompareOp="LT"><ScalarOperator><Intrinsic FunctionName="dateadd"><ScalarOperator><Const ConstValue="(4)"/></ScalarOperator><ScalarOperator><Const ConstValue="(27)"/></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="[d]" Column="Run_day"/></Identifier></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="Run_day"/></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></StreamAggregate></RelOp><RelOp NodeId="19" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="9" EstimatedTotalSubtreeCost="67.1432" Parallel="1" EstimateRebinds="276561" EstimateRewinds="0"><OutputList/><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0"><DefinedValues/><Object Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Index="[missing_index_51084]" Alias="[c]"/><SeekPredicates><SeekPredicate><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="[c]" Column="Run_day"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="[c]" Column="dbname"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="[c]" Column="name"/><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="[c]" Column="schema_id"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[Expr1014]"><Identifier><ColumnReference Column="Expr1014"/></Identifier></ScalarOperator><ScalarOperator ScalarString="[DBTRN].[dbo].[SYS_ALL_OBJECTS_ARCHIVE].[dbname] as .[dbname]"><Identifier><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="dbname"/></Identifier></ScalarOperator><ScalarOperator ScalarString="[DBTRN].[dbo].[SYS_ALL_OBJECTS_ARCHIVE].[name] as .[name]"><Identifier><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="name"/></Identifier></ScalarOperator><ScalarOperator ScalarString="[DBTRN].[dbo].[SYS_ALL_OBJECTS_ARCHIVE].[schema_id] as .[schema_id]"><Identifier><ColumnReference Database="[DBTRN]" Schema="[dbo]" Table="[SYS_ALL_OBJECTS_ARCHIVE]" Alias="" Column="schema_id"/></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekPredicate></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></NestedLoops></RelOp></Parallelism></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>');
The result that I received is expected according to the documentation you sent to me. I think it is a clue that the query optimization behavior for this plan changed between 2005 and 2008:
Msg 8698, Level 16, State 0, Line 1
Query processor could not produce query plan because USE PLAN hint contains plan that could not be verified to be legal for query. Remove or replace USE PLAN hint. For best likelihood of successful plan forcing, verify that the plan provided in the USE PLAN hint is one generated automatically by SQL Server for the same query.
June 9, 2010 at 8:29 pm
Hey,
Thanks for that. Rather than debate how the statistics are being used, let's take a look at the expression you are asking the optimiser to plan for. Both index seeks contain an construction like:
dateadd(day, 27, run_day)>getdate()
Now a human can quite easily see that this is the same as writing run_day > DATEADD(DAY, -27, GetDate()), but SQL Server does not currently contain logic to make that deduction. Why is that important? Well, SQL Server can evaluate DATEADD(DAY, -27, GetDate()) once at runtime, and use the resulting value to seek into an index on the run_day column. It can also use the value to get a good idea of the number of rows that will qualify from the statistics associated with the same index.
With the query as it is now, SQL Server cannot seek the index, so it must perform a full index scan, compute the expression dateadd(day, 27, run_day) for every row, and return rows as appropriate. This is clearly much less efficient that a seek operation, assuming the condition is reasonably selective overall.
We could try to establish why the 2008 optimiser is estimating one row from the index scan. There will be an entirely logical reason, but none of that would take us much further forward, and would require quite a lot of to-and-fro as I ask you to run different diagnostic tests.
My suggestion then, is to change the view definition to use the run_day > DATEADD(DAY, -27, GetDate()) form of the query. Notice the sign change from +27 to -27 there. If you would like a fuller analysis of your query, I'm happy to take a look. You will make my life easier by providing CREATE TABLE and CREATE INDEX statements, and providing a small amount of test data using INSERT statements. The CREATE VIEW definition would be essential as well, naturally.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 10, 2010 at 2:27 pm
Hi Paul,
Thanks for pointing out the inefficient SQL code. I copied the statement from another place in the code and replaced a column with GETDATE().
If you are interested in dissecting exactly why the execution plan was determined and in hopefully determining the behavioral changes of the query optimizer, I'll be glad to pass on the code here.
I am 95% satisfied that the cause of the difference in Execution Plan is due to differences in the query optimizer in 2008 than 2005. Do you agree with this?
Scott
June 11, 2010 at 12:59 am
sgalbra (6/10/2010)
Thanks for pointing out the inefficient SQL code. I copied the statement from another place in the code and replaced a column with GETDATE().
So it needs fixing more than once then 😉
If you are interested in dissecting exactly why the execution plan was determined and in hopefully determining the behavioral changes of the query optimizer, I'll be glad to pass on the code here.
It's not the code so much as the statistics you have and how they're being used. I would start by using DBCC SHOW_STATISTICS to check that everything is the same (or very similar) in 2005 and 2008. Then I would enter a simple SELECT using the predicate I discussed in my last post, and check that the estimated plan produced a reasonable guess at the number of rows that would qualify. Of course all this is rather moot if you plan to correct the view definition anyway...
I am 95% satisfied that the cause of the difference in Execution Plan is due to differences in the query optimizer in 2008 than 2005. Do you agree with this?
It might be...there certainly were changes in optimiser behaviour between 2005 and 2008. As I said before, if you delve into the details of the available statistics and trace through what rules the optimiser is applying, you will find the reason for the poor cardinality estimate. I really don't think it's worth doing, unless purely as a learning exercise, since the query is poorly expressed as it is, and needs fixing.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 11, 2010 at 8:56 am
Is it just me or does Microsoft documentation just not explain compatibility levels very well? Usually the documentation just tells which options you can use, but does not explain these kind of technical details that make a difference to deciding an upgrade.
June 11, 2010 at 11:57 am
sgalbra (6/11/2010)
Is it just me or does Microsoft documentation just not explain compatibility levels very well? Usually the documentation just tells which options you can use, but does not explain these kind of technical details that make a difference to deciding an upgrade.
No doubt documentation can always be better but in general terms I do like Microsoft documentation - main problem is to find the right document.
In regards to compatibility levels I think they did a good job in document referenced next http://msdn.microsoft.com/en-us/library/bb510680.aspx
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 11, 2010 at 2:59 pm
PaulB-TheOneAndOnly (6/11/2010)
No doubt documentation can always be better but in general terms I do like Microsoft documentation - main problem is to find the right document.
In regards to compatibility levels I think they did a good job in document referenced next http://msdn.microsoft.com/en-us/library/bb510680.aspx
That documentation was more in depth than other stuff that I see. However, it doesn't fully explain the philosophy of its use. My assumption was that the compatibility runs the old query processing engine from parsing the query, to determining the optimized execution plan, and through executing the query. However, if I"m not the only one wrong with the assumption, then maybe the documentation would be better to have a disclaimer about what the compatibility level does not guarantee or imply.
June 11, 2010 at 3:09 pm
Paul White NZ (6/11/2010)
So it needs fixing more than once then 😉Paul
You can be a pesky Kiwi. I can't fix the other statement I don't think because it has columns in both sides of the expression.
It turns out I must have made a mistake when I corrected the view definition the first time. I tried again and the query performance improved and ran in about 14 seconds.
Sooooo.......I did not want to get into the query's details in this thread. You gotta love when someone posts a big query in an online forum and asks it to be fixed. That wasn't what I was asking.
However, it is likely that our machine-dumb SQL Server query optimization routines treated my God-awfully offensive query expression differently in 2005 than in 2008. So 2005 was either lucky, better, or had a glitch? Probably lucky because it chose parallelization which cancelled out the bad expression on a query that self-joins a single table 4 times.
If you would like to try and see the affect of dateadd(day, 27, run_day)>getdate()
in your environment I can send all of the necessary code for recreating the problem.
Scott
June 11, 2010 at 3:28 pm
sgalbra (6/11/2010)
Is it just me or does Microsoft documentation just not explain compatibility levels very well? Usually the documentation just tells which options you can use, but does not explain these kind of technical details that make a difference to deciding an upgrade.
I agree that historically the documentation and explanation of the purpose of compatibility levels has been poor. There are definitely pretty widespread misconceptions in this area as a result. The improved page kindly provided by Pablo represents a big step forward (for 2008). It is still not perfect.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 11, 2010 at 3:36 pm
sgalbra (6/11/2010)
...My assumption was that the compatibility runs the old query processing engine from parsing the query, to determining the optimized execution plan, and through executing the query. However, if I"m not the only one wrong with the assumption, then maybe the documentation would be better to have a disclaimer about what the compatibility level does not guarantee or imply.
It is an interesting point about including different versions of the 'engine' and it certainly wouldn't do any harm to document that things don't in fact work that way. You might consider giving your feedback to Microsoft on that - there's a facility to do that on each BOL page, or you can submit a documentation bug or suggestion via Connect. It does work - though slowly - I've had many BOL entries changed successfully.
Maybe one of the reasons details are sketchy is that Microsoft really don't want people to rely on compatibility levels too much, nor stay in that configuration for very long. I would imagine that trying to ensure that a product as complex as SQL Server behaves anything like the previous version(s) is a bit of a nightmare, and there's almost certainly limited testing performed on it.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 11, 2010 at 3:48 pm
sgalbra (6/11/2010)
You can be a pesky Kiwi.
:laugh: !!!
Pesky?! I kinda like that.
I can't fix the other statement I don't think because it has columns in both sides of the expression.
Well that makes it less trivial, of course, but not necessarily impossible. In some circumstances, it is possible to create a computed column (usually not persisted) to represent the expression. This has two benefits: first, you can create statistics on a computed column (this is a very powerful way to give the optimiser some clues about cardinality over the expression); secondly you can often create an index on a non-persisted computed column (also a very powerful way to optimise certain queries).
It turns out I must have made a mistake when I corrected the view definition the first time. I tried again and the query performance improved and ran in about 14 seconds.
Sweet.
Sooooo.......I did not want to get into the query's details in this thread. You gotta love when someone posts a big query in an online forum and asks it to be fixed. That wasn't what I was asking.
Absolutely.
However, it is likely that our machine-dumb SQL Server query optimization routines treated my God-awfully offensive query expression differently in 2005 than in 2008. So 2005 was either lucky, better, or had a glitch? Probably lucky because it chose parallelization which cancelled out the bad expression on a query that self-joins a single table 4 times.
There are a couple of clever things in the 2005 plan, including one of my favourite optimisations: the Segment + Top iterators. It is likely that the optimiser was able to apply a couple of neat rewrites to the original view definition...but ultimately you are right: there is a good deal of luck.
If you would like to try and see the affect of dateadd(day, 27, run_day) > getdate() in your environment I can send all of the necessary code for recreating the problem.
Hey Scott, I think I was just being lazy here and not thinking it through. It's quite easy for me to set up a test rig on my 2008 and 2005 installations just to check for cardinality-estimation behaviour changes. One thing though: optimiser and query processor code changes all the time - not just in major version releases. I am running the following exact versions (today) for example:
2005 = 9.0.4285
2008 = 10.0.2775
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply