Hi together I know that issue has been discussed for several times, but we face an insane weird issue on our new SQL Server 2014 SP3 right now, and we just can't get behind.
We switched over from Oracle to MS SQL Server last year. Our mayor DB is quite big, abt. 800GB, big tables, PDM system. +1000 active users. 16 cores, 192 GB Memory, SSD SAN Storage. ESX 6.5
Settings in SQL Server: -> Create Auto Statistics Enabled -> optimize for Ad Hoc Queries = true -> Snapshot Isolation enabled -> Max Parallel = 4 -> Threshold 50 -> Statistics Updates async in TempDB, normally enabled on our main DBs.
Any how, we have some queries which are handled extremly bad. All leads to the fact that the SQl Server Optimizer creates an execution plan, thinks its okay but on execution it does an inner join (or multiples) with Millions instead of expected 1-2 rows. And of cours millions of logical reads. And I can't get behind whats ongoing in this. These statements ran minutes then.
So basically, we have already 3 databases. All same version and hardware, Prod Test and Dev Environment. My tests can be done pretty easily. All dbs are configured the same and show the same behaviour, but on different queries. Lets say somethimes the test environment does the same statement multipletimes in 47s, the prod db in just a second. Others take seconds in Prod, are instand in Test. What the heck is going on? I always retry the statements muliple times to ensure its cached.
1s vs 47s. Both of these DBs share the same SQL Instance.
Any ideas what could have been wrong here? How to correct single execution plans? How can it be that the sql server doenst learn from that extremly bad statement and to correct it the next time it runs?
Thanks for your help and ideas.