Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 28
  • Last Modified:

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?
0
cbridgman
Asked:
cbridgman
  • 4
  • 4
3 Solutions
 
cbridgmanAuthor Commented:
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?
0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
If database clients are using the database(s), SQL will attempt to load as much as possible into memory.  If you have 10GB allocated to that VM... look to see how much memory you really want SQL to be limited too... setting a 44GB limit on a box with 10GB is basically the same as having a 2 TB limit.  I'd suggest setting the limit to something closer to 4608MB or so depending on what the system can actually handle.

The next big thing to check is to make certain your VM isn't getting into balloon memory as memory pressure is applied.
0
 
cbridgmanAuthor Commented:
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?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
Maybe I misunderstood... you have a VM with 10 GB of RAM allocated.  SQL shouldn't get up to 44 GB of RAM used, because (if I understand) you don't have 44 GB to give it.  (That's why I say it's the same as allocating 2 TB of memory... it's like storing a tray of brownies 100 meters above my head.  100 meters, 1 km, 100 km ... they are all the same to me, I'm not jumping that high... the brownies are simply out of reach.)

You didn't specify which hypervisor you have... Balloon memory is VMWare's way of over-provisioning memory on the host machine.  The impact is that the physical host will start paging memory to disk even though the machine itself will believe it still has memory available. ... so performance will suffer... looking a lot like you're paging memory to disk locally.  If your virtualization environment is VMWare, you can see if easily enough in the VSphere client for the machine.   Apparently other hypervisors have similar mechanisms, I'm just less familiar with those.  (Which hypervisor OS are you using?)
0
 
cbridgmanAuthor Commented:
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.
0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
Perfect.  Once you get SQL's memory set to a 'sustainable level', the next thing you'll want to keep an eye on, inside SQL, will be Page Life Expectancy.  It's an easy counter to watch in Performance Monitor if the number drops 'too low', it's an indication that SQL is memory starved.
0
 
cbridgmanAuthor Commented:
Thanks very much. I will let you know how the testing goes.
0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
Question abandoned, and there are the answers which provide information which can help resolve the issue.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now