Since we updated from SQL Server 2008 to 2019, the queries have become much slower.
The following query ran for ten minutes on version 2008. On version 2019 it runs for over 30 minutes.
Until yesterday, we always limited the WHERE-clause: where date > '20180101'
To make the script at least a bit faster, I narrowed it down from January 2019: where date > '20190101'
Now it's even slower than before. No joke! Because one year less has to be evaluated, the running time has increased by a few minutes.
I have checked all the indexes in detail and I think we have created the correct indexes.
But I don't know if this is really an index problem, because in version 2008 we had the same indexes.
In version 2019 I even deleted and re-created a few important indexes. But that didn't help at all.
MS-SQL Server 2019 got very slow and I don't know why.
Best Answer
From the query plan you provided it runs a minute:
But it compiles 33 minutes (1992254 ms ~= 1992 s ~= 60 * 33 = 33 minutes):
So basically you had a problem on 2008 already. It's just exaggerated in 2019. Never experienced such an extreme case in my life, but I would try to simplify the views you are using and drop not used indexes on underlying tables. Or maybe split the query: first select view into temp table and then use the temp table in your original query.