Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 2008 Slows Server Down

Posted on 2016-08-01
8
Medium Priority
?
24 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 30

Accepted Solution

by:
Rich Weissler earned 2000 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 30

Assisted Solution

by:Rich Weissler
Rich Weissler earned 2000 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
 

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 30

Assisted Solution

by:Rich Weissler
Rich Weissler earned 2000 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 30

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

722 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