Sql-server – SQL Server statement takes forever while running instantly in Oracle

execution-planperformancequery-performancesql serversql server 2014

Please help me interpreting this statement and plan:

https://www.brentozar.com/pastetheplan/?id=Bysy6YtEV

We migrated from Oracle to SQL Server and we have some quite strange behaviours. It might be related to a problem during migration.

I found it hard to interpret the execution plans. Both environments should have the same structure and indexes. Statistics should be up to date.
Settings in SQL Server:

Create Auto Statistics Enabled
optimize for Ad Hoc Queries = true
Snapshot Isolation enabled
Max Parallel = 4
Threshold 50

DB is 600 Gb in size, 16 cores, 160 Gb memory.

The query:

SELECT
    COUNT ( t_01.rsecondary_objectu ) AS selectExpr
FROM
    PIMANRELATION t_01 ,
    PITEM t_02 ,
    PITEMREVISION t_03
WHERE
    (   (   (   ( t_01.rprimary_objectu = t_02.puid )
            OR  ( t_01.rprimary_objectu = t_03.puid )
            )
       AND
            ( t_01.rrelation_typeu = 'w8INy241VJFL2B' )
        )
   AND  t_01.rsecondary_objectu = '2yLJkWqiVJFL2B'
    )

Oracle

We found out that the issue is / was related and depend on the data. If I select a different item to copy in the GUI (it's actually a copy thing and any how the app does these statements) it works instant. The query then once working fine looks a little different: https://www.brentozar.com/pastetheplan/?id=SJo-h2c44

SELECT  COUNT ( t_01.rsecondary_objectu ) AS selectExpr FROM PIMANRELATION t_01 , PITEM t_02 , PITEMREVISION t_03 WHERE ( ( ( ( t_01.rprimary_objectu = t_02.puid ) OR ( t_01.rprimary_objectu = t_03.puid ) )  AND ( t_01.rrelation_typeu = 'w8INy241VJFL2B'  ) ) AND t_01.rsecondary_objectu IN  ('wBHZpD0uVJFL2B' , 'V2PlBGlAVJFL2B' , 'lnHlBGlAVJFL2B'  ) )

SQl Server seems to completely struggle with the one I mentioned before (running infinite) while providing the second one almost instantly. That's so crazy weird. Like a buggy product.

I don't have influence on the statements as they are produced by the app.

CE 110 / 70 results in the same plan. Even if disable all custom indexes and only keep the ones suggested by the app it behaves the same. All primary keys are clustered indexes. But maybe something messed during our migration. But it's quite strange. Most stuff runs fine, but the query at topic is extreme. I let it run für 45 minutes in SQL Server. It simply does not complete.

An other example. Our Prod Environment gets unuable once the highest rated index is created:

Index:
CREATE INDEX EN_PIPRELEASESTATUS_1 ON [TCEUP01].[dbo].[PRELEASESTATUS] ([pname], [pdate_released]) INCLUDE ([puid])

Results in this query:
https://pastebin.com/Ax3qTUjd
===>Took 137.142 seconds

our Test Environment, which should be the same, behaves differently:
https://pastebin.com/0PTZTJpr
===>Took 3.884 seconds

the problematic plan looks like this:
https://www.brentozar.com/pastetheplan/?id=Skvy0qRNE

and it seems it can run like infinite.

Best Answer

The Oracle optimizer uses OR Expansion to improve efficiency of the query. Quoting from the documentation:

In OR expansion, the optimizer transforms a query with a WHERE clause containing OR operators into a query that uses the UNION ALL operator.

The database can perform OR expansion for various reasons. For example, it may enable more efficient access paths or alternative join methods that avoid Cartesian products.

You can think of the new query as being written like this:

SELECT
(
    SELECT
        COUNT ( t_01.rsecondary_objectu ) AS selectExpr
    FROM
        PIMANRELATION t_01
        INNER JOIN PITEMREVISION t_03 ON t_01.rprimary_objectu = t_03.puid
        CROSS JOIN PITEM t_02    
    WHERE
    t_01.rrelation_typeu = 'w8INy241VJFL2B'
    AND t_01.rsecondary_objectu = '2yLJkWqiVJFL2B'
) 
+ 
(  
    SELECT
        COUNT ( t_01.rsecondary_objectu ) AS selectExpr
    FROM
        PIMANRELATION t_01
        INNER JOIN PITEM t_02 ON t_01.rprimary_objectu = t_02.puid
        CROSS JOIN PITEMREVISION t_03
    WHERE
    t_01.rrelation_typeu = 'w8INy241VJFL2B'
    AND t_01.rsecondary_objectu = '2yLJkWqiVJFL2B'
    AND LNNVL(t_01.rprimary_objectu = t_03.puid)
)
from dual;

Now there is an equality condition for both parts of the query, so Oracle can use indexes to perform an efficient nested loop join for both. It still needs to do a cross join for both parts of the query, but the size of the intermediate result sets is significantly reduced compared to doing two cross joins in the same query. For example, if PIMANRELATION has 1 relevant row and PITEMREVISION and PITEM both have a million rows then you get a trillion rows if you cross join them together. However, if you split the queries up then you only end up with a million rows for both.

The SQL Server query optimizer has a rule that can transform OR into UNION ALL: SelToIdxStrategy. There's no documentation for this and the only reference I could find is this answer. However, that rule will not be applied in this context. Instead, you get two cross joins which can only be implemented through nested loop joins. For each relevant row in PIMANRELATION, SQL Server will cross join to all rows in PITEM, then cross join to all rows in PITEMREVISION, and will finally filter out rows after that. You can easily end up with trillions of rows getting filtered down.

I have bad news for you. If you truly cannot change any part of the query text and you need that query to perform well then SQL Server probably isn't the right platform for your application. Databases have different strengths and weaknesses and you may need to change your queries to accommodate those differences.

Related Solutions

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

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.

Sql-server – CPU impact, Scan count 9, logical reads 1686, physical reads 0 vs Scan count 1, logical reads 10253, physical reads 20

The context is important when trying to compare the IO statistics of queries. Let's just start by describing what the queries do based on their plans.

The first query scans the heap in parallel until it finds 1000 rows that need to be updated. The update is then performed on the table. In the worst case scenario for performance, the entire table will need to be scanned.

The second query does an index seek until it finds 1000 rows that need to be updated. Both the table data and the index needs to be updated, because you're changing a column in the index. The query's cost is so low that it isn't eligible for parallelism.

Now let's go back to the IO statistics to see if they make more sense. For the first query:

Table 'hspendingitems'. Scan count 9, logical reads 1686, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The scan count of 9 means that the query executed with a DOP of 8. The logical read count represents the number of reads needed to find the first 1000 rows and activity required to update the table's data. I'm assuming that the UPDATE query runs in a loop because it only updates 1000 rows. As the query runs it's likely that the logical read count will increase because SQL Server will need to scan more of the table to find the next 1000 rows to update.

For the second query:

Table 'hspendingitems'. Scan count 1, logical reads 10253, physical reads 20, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The query doesn't run in parallel so the scan count is 1. The nonclustered index that you created is stored as a separate object so both the table and the index's data need to be updated. I would expect the performance of this query to remain roughly constant as the query runs in a loop.

Should the index be created? It depends. Is there a performance problem right now with the current code? Does the index solve the performance problem in the best way? Are there other queries that could benefit from the index? Will the index slow down other DML on the table too much? We can't answer those questions for you, but I will say that updating 1000 rows against an unindexed heap in a loop often isn't a good strategy in terms of performance.

Best Answer

Related Solutions

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

Sql-server – CPU impact, Scan count 9, logical reads 1686, physical reads 0 vs Scan count 1, logical reads 10253, physical reads 20

Related Question