SQL Server 2000 and Multi Processors

[barney2]

Would really appreciate if anyone could tell me whether they have been able to run SQL stored procedures using a particular processor on a multi processor server. I'm running SQL Server 2000 Enterprise with 4 processors, the instance of SQL is set to use all 4 but I want to be able to dedicate 1 job to 1 processor. Is this possible?

 

[WhiteKnight57t]

From my understanding server will run on one processor unless it finds a need to use more.



==========================
Date is a way to show you care
Time is a great healer
DateTime is just damn confusing

Tim

 

[donutman]

Good question. If you don't get an answer here, post in the sister forum, forum962, Microsoft SQL Server: Setup and Administration.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrdenny]

I don't know of a way to limit a stored procedure to a single processor. However you can limit a query within that procedure to a specific processor by forcing the parallelism. In SQL Server this is done by adding the MAXDOP option to the end of the query.

select Col1, Col4
from Table
Where Something = 12
   and SomethingElse = 'Test'
OPTION (MAXDOP=1) /*This line limits to one processor.*/

By adding that last line in the query you can stop the SQL Server from using more than one processor when running the query.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[WhiteKnight57t]

Now I read that his question as

I have things running on processor 1 so can I set this to run on processor 4.

To which I answered No

==========================
Date is a way to show you care
Time is a great healer
DateTime is just damn confusing

Tim

 

[donutman]

MAXDOP, doesn't look like it will help devote 1 processor to a given SP. It appears to work in the other direction, it will limit a SP to 1 processor. Maybe that's the same thing, but I'm thinking that SQL Server can run more than 1 thread on a processor at one time. Is that not true?
The MAXDOP seems to be for making sure that multiple threads aren't created (across processors) during indexing (thereby hogging resources???)
I wonder if setting MAXDOP would help any with the problem of Ranking with S2K?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrdenny]

Setting MAXDOP=1 tells the SQL Server not to do any parallelism work on the query, which will keep a large query from taking all the CPU resources that a server has.

I also think the SQL can run more than one thread on a processor at one time.

I'm not sure what problems with Ranking your referring to.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[donutman]

Until Yukon comes out with a Rank() function we don't have a good way to solve the problem of sorting records and then inserting them into a table. The sort gets performed in batches (in multiple threads???) so an identity column that is added to the records as they are inserted won't necessarily agree with the sort sequence. What I was thinking is that maybe MAXDOP would prevent that from happening.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]