Sql-server – Same query, same workstation, different execution plans

execution-planperformancesql serversql server 2014

I ran the same query on the same workstation against two different databases that yielded completely different execution plans. The structure and indexes are the same. The only difference is the data. The strange thing is the data in “database1” is much less than the data in “database2” and the performance is worse (database2 takes ½ the time as database1). You’ll notice an obvious design flaw in the ItemProperties table and we plan on changing the structure of this poorly designed table, however before that takes place I’m looking for optimization ideas. Here is the info for both databases.

Database1:
[Actual Execution Plan] https://www.brentozar.com/pastetheplan/?id=HyVv5-PE-

Database2:
[Actual Execution Plan] https://www.brentozar.com/pastetheplan/?id=rJrzjbDVW

Best Answer

Differences noticed:

  • The slow plan uses version 120 cardinality estimator
  • The fast plan uses version 70 cardinality estimator

You could try using query trace flag 9481 to force use of version 70 - just append OPTION (QUERYTRACEON 9481).

Next, the ItemProperties table is handled differently by both cases. The fast plan assumes that there will be at most one property in each join, while the slow plan assumes that there will be two. Assuming that there is in fact only one property value for all cases in question, you could replace the left joins with inner queries. This should result in identical plans on both servers.

EDIT Another optimization opportunity involves using PIVOT operator to fetch all properties at once, like so:

SELECT
vw_Items.ID
,vw_Items.ResolvedEntityID
,vw_Items.ResolvedDescription
,vw_Items.ItemProductNumber
,vw_Items.ItemDescription
,vw_Items.FilteringType
,vw_Items.ItemCategory
,vw_Items.IsDefault
,vw_Items.IsInventoryType
,vw_Items.Active
,vw_Items.PlantFloorVisible
,[dbo].f_ParseDistanceValue([Length]) AS 'Length'
,[dbo].f_ParseDistanceValue(Width) AS Width
,ISNULL(MaterialDescription.[Description], '(none)') AS 'Material'
,ISNULL(GradeDescription.[Description], '(none)') AS 'Grade'
,ISNULL(MillingDescription.[Description], '(none)') AS 'Milling'
,ISNULL(StateDescription.[Description], '(none)') AS 'State'
FROM
vw_Items
-- Join to get properties
left join (
    select [ItemEntityID],[Property],[Value] from [dbo].[ItemProperties]) p
    PIVOT (MAX(VALUE) for [Property] in (Material,Grade,Milling,State,Length,Width))properties on vw_Items.[EntityID] = properties.[ItemEntityID]
LEFT JOIN DescriptionEntity  AS MaterialDescription ON properties.Material = MaterialDescription.EntityID
LEFT JOIN DescriptionEntity  AS GradeDescription ON properties.Grade = GradeDescription.EntityID
LEFT JOIN DescriptionEntity  AS MillingDescription ON properties.Milling = MillingDescription.EntityID
LEFT JOIN #DescriptionEntity  AS StateDescription ON properties.[State] = StateDescription.EntityID

With PIVOT the server will access the ItemProperties table only once, which will likely result in reduced IO.

Related Question