May 22, 2006 at 6:07 pm
We've been having problems with our SQL server lately. After running performance monitor for 8.5 hours today on several counters, I've come up with the following averages:
Memory - Available MBytes: 12.27
Memory - Pages/Sec: 31.7
Process - Page Faults/sec: 58.0
SQL Server: Buffer Manager - Buffer Cache Hit Ratio: 98.61
SQL Server: Buffer Manager - Free Pages: 3215.22
It seems wierd to me that the Buffer Cache hit ratio would be so high yet I only have 12 Available MB of RAM.
I have 1.6GB of RAM available for SQL server.
Does anyone see a problem with the values? Should I simply add more RAM to bring the available MBytes counter up? or will that even help?
Thanks for the help...
Regards, Jim C
May 22, 2006 at 6:41 pm
Tough to know what to do without knowing what symptoms the problems you are experiencing exhibited. The numbers above don't necessarily point to anything without a better context. If you were severely out of memory, I'd expect the cache hit ratio to be lower, but a description of the problem will help
May 22, 2006 at 11:14 pm
There is nothing particularly unusual with the numbers you are seeing, especially if SQL Server is the only application running on the server.
SQL will not release memory unless there is another process that needs it. If you only have Windows and SQL, then there is unlikely to be a need for SQL to release memory. Hence the low amount of available memory.
SQL does a pretty good job of managing the memory that it has allocated to it. The perfmon counter gives you the value of the cache hit ratio since SQL Server started. Don't expect to the the cache hit ratio for the immediate past. If the value for this counter starts to drop, you either need to tune your SQL queries or allocate more memory to SQL.
With just these numbers to look at, there is nothing that looks like a problem. However, these numbers do not represent all activity on your server.
I would ask this question first "Are people experiencing poor performance ?". If they are, then you might need to dig a little deeper. If not, then continue to monitor and be ready to react if the number change.
May 23, 2006 at 4:39 am
I recommend the purchase of this book
http://www.compman.co.uk/scripts/browse.asp?ref=450145
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 23, 2006 at 7:44 am
Could you collect these two counters for sqlserv process: Private Bytes and Working Set? Since from what you posted I believe that the memory allocated to sql server might be in fact swapped in/out from page file. High Pages/sec ratio could support this assumption. Working Set is a total physical memory belonging to a process whereas Private Bytes is a total virtual memory of a process. So if you see a noticeable difference between these two values, this might be the case. Then adding more RAM should help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply