asked on SQL Server 2008 Slows Server Down
I have a Win 2003 Server set up with MS SQL Server 2008 R2 installed - together with a few other apps. When I start the server (it is running as a VM with VMWare), there are no performance problems at all. But as the day goes on, performance begins to suffer to the point that it might take 5 minutes just to open the Start Menu or close or minimize an open window. Everything just seems to grind to a halt.
I've taken a look at the processes tab in Task Manager, which also takes forever to open, and CPU Usage and Physical Memory appear fine. But the commit charge is very high .... it reports somewhere in the neighborhood of 8975 M / 9999 M. I don't really know what that means or if it's bad or good but I'm guessing it's bad.
I then took a look at the processes that were running and what their memory usage numbers were. One image - SQLSRVR.EXE - was reporting 5,800,000K (or thereabouts). That was, by far, the biggest consumer of memory. No other processes was reporting more that 500,000K and there were very few of them.
When I reboot the server, the memory usage for SQLSRVR.EXE is at about 495,000K. It just seems that once I begin to use the one database that's on the machine, that memory usage begins to climb and climb and climb and it never goes back down to a reasonable level unless I reboot.
When I use task manager and stop the SQLSRVR.EXE process, I am then unable to use SQL for anything but the machine starts to respond again - I can open and close windows, etc. without delay
I have 10 GB of memory allocated to the VM. That should be more than sufficient.
Any ideas about what the cause of the performance problems might be or how I might address them?
I've taken a look at the processes tab in Task Manager, which also takes forever to open, and CPU Usage and Physical Memory appear fine. But the commit charge is very high .... it reports somewhere in the neighborhood of 8975 M / 9999 M. I don't really know what that means or if it's bad or good but I'm guessing it's bad.
I then took a look at the processes that were running and what their memory usage numbers were. One image - SQLSRVR.EXE - was reporting 5,800,000K (or thereabouts). That was, by far, the biggest consumer of memory. No other processes was reporting more that 500,000K and there were very few of them.
When I reboot the server, the memory usage for SQLSRVR.EXE is at about 495,000K. It just seems that once I begin to use the one database that's on the machine, that memory usage begins to climb and climb and climb and it never goes back down to a reasonable level unless I reboot.
When I use task manager and stop the SQLSRVR.EXE process, I am then unable to use SQL for anything but the machine starts to respond again - I can open and close windows, etc. without delay
I have 10 GB of memory allocated to the VM. That should be more than sufficient.
Any ideas about what the cause of the performance problems might be or how I might address them?
Microsoft SQL Server 2008
Last Comment
Rich Weissler
ASKER
I may have found a way to "fix" this problem. I set a fixed amount of memory on the instance that I was running. It was set to some astronomically high limit so I reset it to 45056 M. I wonder if that will do the trick?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
OK. I thought that 45056 MB would stop it at 44 GB. I will set to the lower value per your suggestion and see what happens. 4608 MB won't be too small will it? The DB itself is relatively large and some queries are pretty complex but this is a test box and there are only 2 or 3 users hitting it concurrently.
By the way, can you explain two things?
1. Why would 45056 MB end up giving SQL Server a 2 TB limit? That doesn't make sense to me.
2. I've never heard of balloon memory. What is that?
By the way, can you explain two things?
1. Why would 45056 MB end up giving SQL Server a 2 TB limit? That doesn't make sense to me.
2. I've never heard of balloon memory. What is that?
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
You did not misunderstand. I was the "misunderstander". I get it now though. You are correct, the VM only has 10GB to work with so 44 GB is more than it even has.
Also, I am using VMWare. I will take a look at VSphere.
In the meantime, my SQL Server memory numbers are going up and up and up as I run a SQL SErver process against the DB. After the process finishes, I will reset the SQL Server memory limit to the value that you suggested instead of the big one that I used and see what happens.
Also, I am using VMWare. I will take a look at VSphere.
In the meantime, my SQL Server memory numbers are going up and up and up as I run a SQL SErver process against the DB. After the process finishes, I will reset the SQL Server memory limit to the value that you suggested instead of the big one that I used and see what happens.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks very much. I will let you know how the testing goes.
Question abandoned, and there are the answers which provide information which can help resolve the issue.