The first thing you should do is use the query plan to see if new indexes might help. This often helps. My best win on this was when I reduced a query from taking over 12 hours to taking seconds just by looking at the query plan and adding the right index.
If indexes do not do all that is needed, then try to reduce the complexity of the final query. A good way to reduce complexity is to do some pre-aggregation. Not all queries need pre-aggregation, but joins using NOT or OR can benefit from this. The query plan will help you decide if pre-aggregation might be of use.
Look at how you can split the query into smaller units, with each unit populating it's own table. These tables can then be indexed to improve the performance of your main query.
One way to package the pre-aggregation is to get whatever runs your query to instead call a stored procedure. You can then do all the needed pre-aggregation in the SP. There are many other ways to package pre-aggregation, such as indexed views, triggers, or separate table builds. You have to choose what is best in your situation.
When I have done pre-aggregation, I have sometimes got sub-second response from queries that previously took minutes or hours. There have also been situations where pre-aggregation was no help at all.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara