Sql-server – A query submitted from different applications has differing DOP

parallelismquerysql-server-2008

I have a query which selects from multiple views and is fairly I/O heavy. If I execute this query using Management Studio it uses parallelism across most of the 16 CPUs and completes in under 10 seconds. However, when executing it from SpotFire (a product from Tibco) it uses only 1 CPU and can take hours to complete.

I am able to replicate the issue in Management Studio if I using the query hint OPTION (MAXDOP 1).

I was thinking that it may be a SpotFire problem, but it uses the Microsoft JDBC driver for connection and I see no connection properties regarding parallelism. I have confirmed using Profiler that the queries from SpotFire and from Management Studio look exactly. the. same. The only difference appears to be parallel execution.

Can anyone offer insight as to why this might occur?

!SOLVED! [kind of]

I reran the SQL Profiler trace and found that the queries are indeed the same, but the way that they are executed is different.

From SpotFire:
declare @p1 int
set @p1=0
declare @p2 int
set @p2=0
declare @p7 int
set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,NULL,N'SELECT a, b, c, d FROM MyView',16,8193,@p7 output
select @p1, @p2, @p7

From Management Studio:
SELECT a, b, c, d FROM MyView

I then added two new events to SQL Profiler and reran the queries:
– Performance / Degree of Parallelism
– Performance / Showplan All

This showed the the query from SpotFire was using DOP 0 (BinaryData = 0x00000000) and from Management Studio was DOP 12 (BinaryData = 0x1200000). Next, the SHOWPLAN showed me that the SpotFire query was inserting the results from the view into a temporary table before returning the data to the client.

So why does it do this? Possibly because of the sp_cursorprepexec statement. But why should that cause the DOP to drop to 0? I don't know.

I think that the "solution" is going to be working with SpotFire and possibly tuning the JDBC connection string.

Here is an article from MSDN regarding all connection parameters for JDBC.

Here is another article from someone who had the same exact problem I have, also with no resolution.

Best Answer

There are 2 standard ways to affect this

  1. MAXDOP hint per query
  2. max degree of parallelism Option for the whole server

The SQL Server 2008 resource governor and some trace flags will affect it. I doubt Tibco is using the resource governor so it could be a trace flag.

One more option I've seen is SET IMPLICIT TRANSACTIONS ON.

If no trace flags, no implicit transactions, and no MAXDOP hints, then the queries are not the same.

Edit, after question update

You should be able to change some prepare and/or cursor setting. Example: http://www.streamreader.org/serverfault/questions/177391/impact-on-sql-2000-performance-of-jdbc-selectmethodcursor