April 22, 2009 at 7:48 am
Hi
running an hour long perfmon trace with 5 sec intervals Im looking at (amongst other things) memory : pages/sec and buffer cache hit ratio. The pages per sec is an average of 9 peaking at 3495. Might suggest a memory problem .. but the buffer hit ratio is constantly at 90% which I interpret as SQL using buffer memory efficiently etc...
Am I misreading this ? what should be my next step to confirm either way ?
many thanks
Simon
April 22, 2009 at 8:21 am
According to Grant Fritchey's book SQL Server 2008 Query Performance Tuning Distilled, I know you are on 2005, but I think it still applies, if your average Pages/Sec < 50 then you are likely all right. I'm not an expert, but since I just read that chapter in the book, I figured I through out Grant's 2 cents.
Also according to the book you may want to look at Available Bytes to make sure it isn't too low (yes this is the generic term used).
Edit: added link to book.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 22, 2009 at 9:55 am
Was it a small number of peaks? It could be explained by, for example, a backup running. Note that this counter isn't SQL-specific so other apps could cause the spikes.
Are you specifically looking for memory problems, or just at server performance in general?
April 22, 2009 at 10:15 am
matt stockham (4/22/2009)
Was it a small number of peaks? It could be explained by, for example, a backup running. Note that this counter isn't SQL-specific so other apps could cause the spikes.Are you specifically looking for memory problems, or just at server performance in general?
Hi Matt
Initally looking at sever performance as a starting point and was debating whether I needed to drill down into memory problems based upon those results
Yes the page/secs high points are spikes.... .. but as far as Im aware sql is the only app running on this box .. having said that there was a profile trace running on the box at the same time
~simon
April 22, 2009 at 11:12 am
I saw your other post afterwards, and you should follow Grant's advice in creating baselines. Between perfmon stats, wait stats and DMVs there is a wealth of information that can be gathered.
For general purpose monitoring I copy/reset wait stats daily and monitor a dozen or so perfmon stats at 10 minute intervals. These include:
memory\available mb
memory\pages/sec
paging file\%usage
physical disk\avg disk sec/read
physical disk\avg disk sec/write
processor\% processor time
SQLServer:buffer manager\buffer cache hit ratio
SQLServer:buffer manager\page life expectancy
SQLServer:SQL Statistics\Auto-Param Attempts/sec
SQLServer:SQL Statistics\Batch Requests/sec
SQLServer:SQL Statistics\SQL Compilations/sec
SQLServer:SQL Statistics\SQL Re-Compilations/sec
I also monitor network stats and mirroring stats, plus a few others that are useful for reporting to management (user connections etc). Your requirements may differ based on your workload etc.
Baselining is vital as without it you won't know what is normal, or even acceptably abnormal. You should also be aware that many stats can be misleading taken in isolation, for example disk stats may be higher than expected because memory is in short supply.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply