Please help me interpreting this statement and plan:
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.
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' )
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