January 14, 2008 at 1:14 pm
I manage a SQL Server cluster with 8 gig memory hooked up to half terabyte of hard drive. It has two nodes each with two processors.
We have put quite a few databases on it. However in at least last month and a half it is eating up all of 6 gig memory allocated to it . Processor usage is consistenly above 10% but below 15%.
I am wondering if there is a best practices document that can help me determine following:
At what point I can say no to new databases? what indicates timeout from creating more work and databases for the environment?
What would the next step be? if I want to keep putting databases on the same cluster. do I want to go upto 16 gig memory or 32?
January 14, 2008 at 7:13 pm
Do you have many scripts running on your cluster? Do these scripts create many objects, such as table variables and ..., ? If so, are they destoyed after usage?
10-15% CPU usage may indicate some applications running on your cluster. Did you open the task manager and see whether or not some unexpeccted application running on your cluster?
January 14, 2008 at 7:37 pm
The fact that SQL is using 6 Gig of memory is not a problem. SQL is designed to used memory to cache data and does not release that memory unless the is another process that requires it.
To determine whether you have sufficent memory to satisfy the current work load, you really need to monitor performance monitor counters such as the cache hit ratio and also the I/O load on your disks (and in particular how close this is to the capacity of the disk drives).
15% Cpu tends to suggest to me that your server probably is not all that busy.
As you add databases to your server, you will most likely see the cache hit ratio start to drop and the I/O rate increase and the CPU utilisation increase.
Your definitely need to act (stop additional work load/databases) if you see the cache hit ratio drop below 90%. If you are activity monitoring this, you should be able to increase the RAM on the server before you get to that point.
Similarly, as your I/O load increases towards the max capacity of your disk drives, you need to either stop new work /database or increase the capacity of your disk array (more disks, faster disks, better spread of the I/O load across your existing disks).
One thing that will occur when the cache hit ratio drops is that SQL will be making more I/O requests. So, you will most likely see a correlation between the two figures.
Have a look at http://www.sql-server-performance.com. There are stacks of articles on how to monitor your server and what to look out for.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply