Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2008-12-29 : 15:31:26
|
| My mind is mush after reading numerous papers on monitoring memory. Most of what I found was in regards to what DMVs and perfmon/sysmon counters to check, but very little information on what values are considered bad. So I ask you, what do you check when you suspect a memory bottleneck and does this apply to 64-bit OS? I've always concentrated on paging, but I know that is too simplistic.Thanks, Dave |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2008-12-29 : 16:02:43
|
| Hi Tara,What values do you consider to be acceptable and what do you consider to be a bottleneck? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-12-29 : 16:31:41
|
| Buffer cache hit ratio should be near 99% at all times. Page life expectancy should be consistent and no sudden drops. I believe the value should be at least 1000 or more. Total and target server memory should be equal. If target is higher than total, then SQL Server wants to consume more memory but can't. I don't remember the acceptable values of the others as I don't have my Windows books with me, but you can find the info on the sql-server-performance.com site.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-31 : 21:47:09
|
quote:
Originally posted by DBADave
My mind is mush after reading numerous papers on monitoring memory. Most of what I found was in regards to what DMVs and perfmon/sysmon counters to check, but very little information on what values are considered bad. So I ask you, what do you check when you suspect a memory bottleneck and does this apply to 64-bit OS? I've always concentrated on paging, but I know that is too simplistic.Thanks, Dave
I would check with DBCC memorystatus and see data cache and procedure cache and analyze how big is it. In addition to counters added by Tara, I would look for Lazy writes/sec. Acceptable values:Lazy writes/sec <20Page life expectance >300. |
|
|
|
cshah1
Constraint Violating Yak Guru
347 Posts |
Posted - 2009-01-02 : 14:42:54
|
| Duplicate threadAlso, some interesting information from GlennAlanBerry - SQL MVP http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/f72b12c3-05b2-4b79-90ac-be9bb74628d8 |
|
|
|
|
|
|
|
|
How to look for memory bottlenecks on 64-bit OS?