March 23, 2021 at 8:55 am
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!
March 23, 2021 at 12:01 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 23, 2021 at 1:50 pm
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!
March 23, 2021 at 5:07 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 24, 2021 at 9:50 am
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply