SQL Server Performance

CPU Allocation In SSMA

Discussion in 'ALL SQL SERVER QUESTIONS' started by Ksr39, Oct 3, 2012.

  1. Ksr39 Member

    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.
  2. Luis Martin Moderator

  3. Shehap MVP, MCTS, MCITP SQL Server

    As luis Said, CPU Parallelism setting can be helpful to govern CPU consumption and adding to that , you can use a new 2008 techniques called " Resource Governor" to control CPU utilization used by certain logins or certain tools or sources like SQL Server Management studio , .net SQL client data provider…etx , for more information about it, you can read at http://msdn.microsoft.com/en-us/library/bb933866.aspx

    On the other aspect , it worth much thinking about the setting of CPU resources number , what number to use and why …?

    According to generic Microsoft recommendations , it is much preferable to reduce CPU number if a huge no of transactions are working in parallel where CPU governing is highly considerable meanwhile it is much preferable to increase CPU number if small no of transactions with high need for parallelism due to huge data entity selected by transactions like DWH transactions…

    Kindly let me know if any further help is needed

Share This Page