Solved

SQL Server 2008 Slows Server Down

Posted on 2016-08-01
8
14 Views
Last Modified: 2016-08-22
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
Comment
Question by:cbridgman
  • 4
  • 4
8 Comments
 

Author Comment

by:cbridgman
ID: 41737361
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
 
LVL 29

Accepted Solution

by:
Rich Weissler earned 500 total points (awarded by participants)
ID: 41737442
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
 

Author Comment

by:cbridgman
ID: 41737458
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
 
LVL 29

Assisted Solution

by:Rich Weissler
Rich Weissler earned 500 total points (awarded by participants)
ID: 41737501
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:cbridgman
ID: 41737509
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
 
LVL 29

Assisted Solution

by:Rich Weissler
Rich Weissler earned 500 total points (awarded by participants)
ID: 41737535
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
 

Author Comment

by:cbridgman
ID: 41737538
Thanks very much. I will let you know how the testing goes.
0
 
LVL 29

Expert Comment

by:Rich Weissler
ID: 41765030
Question abandoned, and there are the answers which provide information which can help resolve the issue.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now