469,568 Members | 1,391 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,568 developers. It's quick & easy.

How to get a batchjob to run on more than 1 processor ?

I have an SQL2000 server running on a 4-cpu 4 GB Win2003 system with
hyperthreading. The batch is running on only one virtual processor. I
would like to utilize all the processors in parallell in order to
speed up the batch. How ?
Jul 23 '05 #1
2 1060
Ray
Since you posted in the ms-sql group I'm going to assume the Batch you are
running is a SQL Batch or you have a DOS batch file including calls to bcp
or osql. SQL Server will split a batch (or any sql query) across multiple
threads depending on the number of processors assigned to the SQL Server,
the degree of parrallelism, and how the optimizer calculates the most
efficient path.

If you are referring to DOS batch files, that would be best posted to
another group.

"Andreas" <ah@steria.no> wrote in message
news:cd**************************@posting.google.c om...
I have an SQL2000 server running on a 4-cpu 4 GB Win2003 system with
hyperthreading. The batch is running on only one virtual processor. I
would like to utilize all the processors in parallell in order to
speed up the batch. How ?

Jul 23 '05 #2
Andreas (ah@steria.no) writes:
I have an SQL2000 server running on a 4-cpu 4 GB Win2003 system with
hyperthreading. The batch is running on only one virtual processor. I
would like to utilize all the processors in parallell in order to
speed up the batch. How ?


Most likely, you can't.

First of all, if your batch includes multiple statements, those cannot
be run in parallell. What can happen is that the optimizer decides to
use several processors to run a query. However, far from all queries
can be evaluated with parallel plans.

What you can do, is to use sp_configure to check the setting of "max
degrees of parallelism". If this parameter is 1, then parallelism is
turned, and you could change it to 4. (Not 8.)

Note also that parallel plans are far from always Godsent. Sometimes
parallel plans are plainly ineffective. And even if a query does
execute faster with a parallel plan, other users on the machine may
suffer, as the query monopolize the CPU.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by Jason Gyetko | last post: by
5 posts views Thread by nano2k | last post: by
5 posts views Thread by =?Utf-8?B?dWxpbGxpbGxpYQ==?= | last post: by
168 posts views Thread by broeisi | last post: by
6 posts views Thread by Peter Graf | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.