I have a document management system (like SharePoint), which works on top of Microsoft SQL Server. It generates SQL queries where I do not have much control, so I cannot change SQL queries directly in most of the cases.
In the past, this system was running on top of SQL Server 2014 and performance was ok. Later another instance were created with SQL Server 2017 and performance of some queries is very bad (like 1 minute compare to 0.2 seconds in the past). Query is a select from 8 tables, which are joined by foreign/primary key. It does not make much sense to put query here, because it is 140Kb in size and query execution plan is also huge. I guess nobody will have time to understand and analyze it. My database is not empty and has some data, around 100K records in some tables and up to 4M records in another table, which are joined in this query. The final query result is around 8000 rows. if I add ALL TOP 30 hint, with the value less than 30, then it slows down the query a lot. Any values more than 30 or removing ALL TOP hint does not affect the performance, except time to fetch 8000 rows, but I see the first records very fast.
I tried to switch new server into compatibility mode the same as old one, but it did not help. I found that if I add to the query these hints
OPTION (LOOP JOIN, FORCE ORDER)
then it becomes much faster. If I add one of these hints it does not help. I cannot modify the queries generated by the system, which means I cannot add these hints to every query it generates. I only extracted query from trace and add hints for debugging purposes, when running them in SQL Server Management Studio.
My question: Can this fact, that adding these 2 hints improve the performance tell me something about what is wrong in my SQL Server and gives some ideas what I can change in the SQL Server configuration to improve performance?