I have a very large query (~630 lines) that involves a lot of nested
SELECT statements and pulls from multiple views. Our SQL server has parallelism set to 2 with a threshold of 95 (set that way because our DBA was optimizing it based on some other applications). This query has recently started taking 5-10 minutes to complete, up from usually less than a minute. While investigating the cause, we noticed that it never seems to trigger parallelism, always running serial, and suspect that may have something to do with its performance. What's weird is while experimenting, we even dropped the threshold back down to the default value of 5 seconds, and it still won't run parallel. What could be preventing it?
We've been doing the testing on a non-production environment that nobody else was using at the time, so this was the only query being run. Our DBA also tried things like clearing caches and plans, and even recycling the system, but it had no effect.
Update 1: Per comments, I've verified that stats are updated nightly but the problem persists. We're actually rolling back the code to an earlier version that didn't have such serious performance issues, but will continue to test this code because it was supposed to improve performance over the old code, and actually did in initial testing. Will update here accordingly.