Prev: Return a specific string based on positions of Y/N data
Next: Merge 2 tables
From: light_wt on 3 Dec 2009 17:40
I have one query and executed in two SQL Server, local to its own server.
data size are almost identical ( to the hundred of rows)

One has execution plan has Parallelism while the other doesn't, why is that?
How to make them both using Parallelism?

(The one with Parallelism is much faster. 200x)

Thanks.

From: Erland Sommarskog on 3 Dec 2009 18:00
light_wt (lightwt(a)discussions.microsoft.com) writes:
> I have one query and executed in two SQL Server, local to its own server.
> data size are almost identical ( to the hundred of rows)
>
> One has execution plan has Parallelism while the other doesn't, why is
> that?
> How to make them both using Parallelism?
>
> (The one with Parallelism is much faster. 200x)

First thing to check is to check the number of CPU cores there are
available:

master..xp_msver 'ProcessorCount'

Next is to verify that parallelism is enabled

sp_configure 'max degree of parallelism'

If run_value is 1, parallelism is turned off. 0 means "use as many CPUs
there are".

You can also check

sp_configure 'affinity mask'

If this is value is a pure power of 2, then only one CPU is available
for the instance.

But there may be other reasons. Even if the databases are very similar,
costing estimates may come out differently. There is also a run-time
decsision whether to use a parallel plan depending on the load on the
machine.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Return a specific string based on positions of Y/N data
Next: Merge 2 tables