How can I let system use more CPU and RAM of SQL Server ?

  • I have a system, when I perform one function (nobody uses this server), it works very slow, but I check the SQL server database(SQL server is 2005 ) CPU Utilization rate is about 15%, and RAM usage is low 6.5GB, the total RAM is 16GB.

    why the CPU and RAM utilization rate is so low? and how can I let system use more CPU and RAM of SQL Server? thanks!

  • SQL Server, by default after an install, will attempt to use every bit of CPU and RAM that the system will let it have. Assuming you've changed none of the default settings, this is the amount of CPU & RAM that it currently needs to satisfy the query. Although, on the RAM side, you also have to take into account, what else is running on the system, because other things, from the OS to other programs, could be using up RAM, so SQL Server can't allocate more.

    Check that you haven't set limits on the CPU through the affinity settings. Check that you don't have a limit on RAM that is too low (you should have a limit, but it should allow for enough memory for the OS, say 2gb of the top, so SQL Server will be limited to 14gb). The fact is, you can only limit the amount of RAM or CPU that SQL Server uses. You can't make it use more.

    ----------------------------------------------------
    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 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant Fritchey wrote:

    SQL Server, by default after an install, will attempt to use every bit of CPU and RAM that the system will let it have. Assuming you've changed none of the default settings, this is the amount of CPU & RAM that it currently needs to satisfy the query. Although, on the RAM side, you also have to take into account, what else is running on the system, because other things, from the OS to other programs, could be using up RAM, so SQL Server can't allocate more.

    Check that you haven't set limits on the CPU through the affinity settings. Check that you don't have a limit on RAM that is too low (you should have a limit, but it should allow for enough memory for the OS, say 2gb of the top, so SQL Server will be limited to 14gb). The fact is, you can only limit the amount of RAM or CPU that SQL Server uses. You can't make it use more.

     

    Actually , I didn't change any default setting of CPU and RAM, Max Degree of Parallelism is 0, the value of Max Server memory  is default value, so don't know why  system runs slow and the the utilizations of CPU and RAM low.  thanks!

     

  • What OS is being used - is it x86 or x64 OS?  Since this is SQL Server 2005 - I suspect you might have an older x86 OS.  If so - then we need to look at AWE settings to allow for more memory to be accessible.

    BTW - you really should upgrade to a supported OS and database.  If this system is truly only used by you - and is used for development/testing you could use the Developer Edition.  It really depends on the usage of the system though...

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    What OS is being used - is it x86 or x64 OS?  Since this is SQL Server 2005 - I suspect you might have an older x86 OS.  If so - then we need to look at AWE settings to allow for more memory to be accessible.

    BTW - you really should upgrade to a supported OS and database.  If this system is truly only used by you - and is used for development/testing you could use the Developer Edition.  It really depends on the usage of the system though...

     

    OS is X64, SQL server 2005 is very very old, but there is no documents to tell us how to set the configuration for an applicaton if we upgrade SQL server

    thanks!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply