Is Parallel Processing Causing Problems?
Looking the query plan I noticed that the query is being run in a parallel thread mode. A bit of googling lead me to believe that this was the cause of the query taking so long to run.
I then added
option (maxdop 1)
to the end of my query to force into single processor mode. The query now takes about 8 minutes to run.
I believe that the Server Manager needs to take some time out and look at the settings he's used to configure the server, including the thresholds for parallel processing to kick in.
Any pointers on recommended settings for parallelism would be grateful.
I've also seen this in the past where a simple query gets itself into a mess during parallelism. Turning MAXDOP to 1 for me also worked.
While I don't have any pointers directly, what we did do was a bit of trial and error on our test system to see where things started to get messy and found a happy medium.
In the end the decision was to set the server to MAXDOP 1 and left it at that. We've not had any trouble since.
There actually could be several reasons for something like this. If you could run the query in PE and attach the actuals in the form of a .queryanalysis or .pesession file we may be able to tell you more.
Beyond that I would also recommend a bit of caution with regard to using MAXDOP(1) especially at the instance level. If you get a chance, have a look at item #3 on this blog post.