December 1, 2009 at 7:13 pm
Hi guys,
I have been doing a performance analysis on a SQL Box to find/prove any memory crunch.
I have used the PerfMon tool and collected few days data.
The confusing part of the collected data is that the collected counter values indicates low page file usage/page faults/page reads, which means there is not much memory pressure as such. But the counter SQL Server Memory Manager:Total server Memory gives a higher value (i.e., 6GB which equals to the MAX memory value of SQL server). In other words, it reaches the value as that of the counter SQL Server Memory Manager:Target server Memory.
My question is if counter 'Total server Memory' reaches its allowed maximum value, then paging values also should increase to accommodate more memory from the disk for BPool. But my counters related to paging are showing less value.
Please advise is there any other counters to consider to relate this situation.
My server runs analysis services also.
I considered the following counters mainly and they are at its optimum value indicating less paging.
SQL Server: Buffer Manager: Buffer Cache Hit Ratio – 99.8 % (Higher value optimum)
SQL Server: Buffer Manager: Free list stalls / sec - 0.210 (Lower value optimum)
SQL Server: Buffer Manager: Lazy writes / sec – 0.635(Lower value optimum)
SQL Server Buffer Node: Page Life Expectancy – 6367 (Higher value optimum)
Memory : (Hard Page Faults represent actual reads or writes to the disk)
% Hard Page Faults= Pages Input/sec / Page Faults/sec= 12.69/769.47= 1.6% (Lower value optimum)
More Details:
SQL Server: Memory Manager
Target Server Memroy (KB): 6144000
Total Server Memroy (KB): 6120777
Process: msmdsrv sqlservr
Page faults/sec 38.212 8.075
Page File Bytes 4335846089 6661959463
SQL Server: Buffer Manager:
Database Pages: 696523
Page Lookups/sec: 14724.616
Page reads/sec: 286.563
Target Pages: 768000
Total Pages: 765097
John
December 2, 2009 at 3:39 am
I considered the following counters mainly and they are at its optimum value indicating less paging.
SQL Server: Buffer Manager: Buffer Cache Hit Ratio – 99.8 % (Higher value optimum)
SQL Server: Buffer Manager: Free list stalls / sec - 0.210 (Lower value optimum)
SQL Server: Buffer Manager: Lazy writes / sec – 0.635(Lower value optimum)
SQL Server Buffer Node: Page Life Expectancy – 6367 (Higher value optimum)
Hi,
All the counters showing good
Can you share the configuration details
1.32 bit or 64 bit
2.Max memory in OS
3.Awe enabled or not
SQL Server is Dedicated?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 2, 2009 at 3:46 am
John,
Are u get any error in sql server errorlog like memory paging out
Already i told counter value is good Check the sql/OS error log
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 2, 2009 at 4:21 am
Hi,
Its a 64 bit server.
MAX memory in OS = 16 GB. MAX mem value for SQL Server is 6 GB. Mem is divided among SQL server, SSIS, SSAS.
No AWE used.
Do receive SQL Server Page out messages occassionally.
Comparing different counter values and SQL error logs is confusing in this case. Cant bring the pieces together.
Thanks
MJ
John
December 2, 2009 at 4:37 am
m.John (12/2/2009)
Hi,Its a 64 bit server.
MAX memory in OS = 16 GB. MAX mem value for SQL Server is 6 GB. Mem is divided among SQL server, SSIS, SSAS.
No AWE used.
Do receive SQL Server Page out messages occassionally.
Comparing different counter values and SQL error logs is confusing in this case. Cant bring the pieces together.
Thanks
MJ
Locks pages memory enabled or not ?
Its only for Enterprise edition.Now u can use this for Standard edition (with CU-4)
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 2, 2009 at 5:10 am
I'm not looking for locking pages for sql server. Because other process requesting memory would be SQL related only (.ie., analysis services, SSIS etc..). I dont want to hamper them.
I want to prove upon is whether there is an issue of insufficient physical memory (RAM), so that I can action upon it (Can add physical mem.). But I want to prove it.
John
December 2, 2009 at 7:03 am
m.John (12/2/2009)
I'm not looking for locking pages for sql server. Because other process requesting memory would be SQL related only (.ie., analysis services, SSIS etc..). I dont want to hamper them.I want to prove upon is whether there is an issue of insufficient physical memory (RAM), so that I can action upon it (Can add physical mem.). But I want to prove it.
Hi,
I think there is no memory pressure because ur counters showing good results
How big ur DB also check any locking &index fragmentation
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 3, 2009 at 8:18 am
1) why are you checking for memory problems? Those are maybe 5% of the actual performance problems out there.
2) are you actually HAVING any performance problems? There are lots of other things to check if you are.
3) as you and someone else stated, the metrics you posted do no indicate any hint of a problem...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply