Performance analysis problem

Question

Performance analysis problem

m--S3qU3L

Hall of Fame

Points: 3706
More actions
December 1, 2009 at 7:13 pm

#214198

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

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/

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 2

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/

m--S3qU3L Hall of Fame Points: 3706 More actions · Answer 3

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 4

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)

Lock Pages in Memory[/url]

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

m--S3qU3L Hall of Fame Points: 3706 More actions · Answer 5

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 6

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/

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

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