We are running SQLServer 2008 Enterprise. I have a SQL query that includes multiple batch numbers in the WHERE clause. When I have 16 or few batches, the optimizer selects a serial processing plan and runs in 30 seconds. When I add a 17th batch, it selects a parallel plan and jumps to 32 minutes. If I add a MAXDOP(1) hint, it runs again in 30 seconds.
My issue is this: I am running the query from a 3rd party vendor, which runs against an Oracle view, that in turn runs across a database link to SQLServer views. MAXDOP cannot exist in the view definition, so I have to find an alternate method of forcing it into serial processing.
After some research, I found that Resource Governor is another way to control parallel processing, and I am able to set up a workgroup by user.
With SSMS, I enabled resource governor, set up a resource pool, set up a workload group, and a classifier function to route to my workgroup by user, and reconfigured it so my changes would take effect.
I know that the query is being routed to the correct workload group, as I can see the assignment in activity monitor. Even though my workload group is set to MAXDOP 1, it seems to still be using a parallel plan as it runs over 30 minutes rather than 30 seconds.
What is the issue? Does resource governor MAXDOP work differently than the same hint in a SQL statement? Is it not reading / utilizing the workload group setting?
Thanks in advance!