Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 CPU Allocation in SSMS

Author  Topic 

ksr39
Posting Yak Master

193 Posts

Posted - 2012-10-03 : 04:17:12
Hi Experts,
Need small information on CUP allocation in SQL Server 2005 64bit Enterprise edition on windows 2003 64bit Enterprise edition. What is the default allocation of CPU in SQL Server (SSMS--Properties--Processor) and I have a server which is having 16 CPU’s and 90+ of Ram memory, all the CPU’s are configured to SQL Server and the below check boxes are also checked in processor tab. Now my doubt is by allocating all the CPU’S to the SQL Server will it degrade the performance or will it increase as when I see task manager I found my CPU utilization (spikes) are more in peak hours. And virtual memory utilization is also more.

One more doubt is if I want to reduce the allocation of CPU in the SSMS—Properties—Processor, on one of my test server which I want to make it equal with one of my prod server, what is the process and without restarting the SQL Server I need to do, as we are going to test some performance on the test server.

Thank you in advance.

Thank You All In Advance
KRS39

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-10-03 : 16:43:06
The default is for SQL Server to be configured to use ALL CPUs. Why would you want to change this?

Is the server dedicated to SQL Server? Why are CPU spikes bad? CPU spikes are normal. What issue are you trying to resolve?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

ksr39
Posting Yak Master

193 Posts

Posted - 2012-10-04 : 00:33:42
Hi Kizer,
Actually we are trying to test some load on the Test machine before deploying on the prod server but the problem is prod server is with 8 CPU’s and 32Gigs of ram memory but on Test server it is 16 CPU and 90+ ram. So now we need to make changes in the Test machine so that it should be as it is like Prod server so that we can do the load test and then check the results.
Yes both the server’s are dedicated SQL Server. We found the CPU and Virtual memory is utilized more in the Prod server. I couldn’t find any specific reason for the same.

Thank you in Advance

Thank You All In Advance
KRS39
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-10-04 : 00:37:09
It is not possible to make your test machine like production. All CPUs will be used even if you uncheck some. They'll be used differently but still used.

Load test servers should match production hardware.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-10-04 : 01:42:38
Unless you have an application , which is paralllelism sensitive - I wouldn't advise to change the CPU settings.
I understand the problem, about the server disparity , and I've seen manay occassions where a casual approach to testing on different servers has caused problems. You must also make sure that the disk and other layers on the stack are similar - for it to be a valid test .
For example, if you're Prod is running of one disk pool and the test is off multiple disk pools - then you may experience performance differences

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

ksr39
Posting Yak Master

193 Posts

Posted - 2012-10-04 : 06:19:29
Hi Kizer,
Thank you for the suggestion but how to over come this as we need to check the load test and it has to be deployed on the test machine before going live.please suggest me any so that i can over come this.
Hi Jack,
Thank you for ur advice i will check on the disk level and ill go with that.but is there any other way where we can restrict the CPU utilization or allocation.

Thank You All In Advance
KRS39
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-10-05 : 01:19:28
There are such things as MAXDOP , but that's more about forcing the amount cpu count use. And should only be applied after analysis of the type of workload . Some notes here: http://www.sqlserver-dba.com/2011/02/waitstats-cxpacket-wait-type-and-how-to-reduce-it-.html
There are also other factors you haven't mentioned such as CPU speed - and CPU model

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -