Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to limit the number of processor SQL servur uses. Expand / Collapse
Author
Message
Posted Monday, March 30, 2009 3:18 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 128, Visits: 1,142
Hi,

Is it possible to tell SQL server to use only 4 processors out of 8 ?
I would like to dedicate 1 SQL server instance to processor 0 to 3 and a Second SQL server instance to processor 4 to 7, is it possible?
Can I use affinity mask Option ?

regards



Kindest Regards,

egpotus DBA

Post #685924
Posted Monday, March 30, 2009 4:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 11:27 PM
Points: 170, Visits: 553
You can use Affinity mask. From my experience, SQL Server performed better when i left SQL Server to automatically set the affinity...
Post #685942
Posted Monday, March 30, 2009 4:21 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 128, Visits: 1,142
I have one server with 8 CPU (CPU0, CPU1... CPU7) , I am using 2005 Standard Sql version so I am limited to 4 CPU.
I have 2 instances running on my server, how can I tell one instance, using affinity mask, to only use CPU4 to CPU7?



Kindest Regards,

egpotus DBA

Post #685945
Posted Monday, March 30, 2009 6:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:26 AM
Points: 14,205, Visits: 28,536
Just open up the Server Properties in SSMS. On the Processors page you can see how many processors you have available and set them to a particular affinity. I'd suggest reading up in the BOL prior to setting it, just so you get it right.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #686040
Posted Monday, March 30, 2009 8:32 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 128, Visits: 1,142
Those parameters cannot be update using SSMS, do you know why ?


Kindest Regards,

egpotus DBA

Post #686166
Posted Monday, March 30, 2009 9:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:26 AM
Points: 14,205, Visits: 28,536
First disable the automatic affinity in the check box below.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #686235
Posted Monday, March 30, 2009 2:20 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, December 12, 2014 12:03 PM
Points: 4,363, Visits: 9,547
BTW - when they say SQL Server can only use 4 CPU's - they are talking about slots. A single quad core processor is one CPU's as far as licensing and utilization. So, SQL Server Standard can use up to 16 CPU's if you use 4 quad-core processors.

Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #686510
Posted Tuesday, March 31, 2009 1:18 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 128, Visits: 1,142
thank you for this help


Kindest Regards,

egpotus DBA

Post #686763
Posted Monday, May 21, 2012 1:39 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:06 PM
Points: 4,253, Visits: 4,295
Since moving two Databases and several SSIS Packages I'm having issues with the AS400 iSeries and I'm getting Communication errors.


Several task fail. It seems that the task complete much faster and as if the AS400 can't keep up with it.

Should I limit the number of processors?

If I limit the number of parallel loads, SSIS Tasks, I have been able to avoid the error.

I have 1 64 bit Windows Server 2008 R2 running SQL Server 2008 R2.

I looked it up on BOL but I have never had to mess with changing the number of processors.

Any ideas would be greatly appreciated.




For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1303684
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse