performance issue – Memory Metrics to check for MSSQL

–Memory metrics to check

Pages/sec,
Page Faults/sec,
Pages Input/sec,
Pages Output/sec,
Page Reads/sec,
Page Writes/sec,
Available Memory,
Non-pageable memory pool bytes,
Pageable memory pool bytes,
and Committed Bytes

pages/sec (in perfmon)
——————————-
ref : http://www.sqlshack.com/sql-server-memory-performance-metrics-part-1-memory- pagessec-memory-page-faultssec/

– it is rate at which data pages are read from disk or written to disk.
formula,
– pages/sec = pages input/sec + pages output/sec
– pages/sec value should be < 50
– A high value of Pages/sec indicates intensive memory activity – many pages are
written from disk to RAM and read from RAM to disk.

page faults/sec (in OS – Perfmon)
——————-
ref : http://www.sqlshack.com/sql-server-memory-performance-metrics-part-1-memory- pagessec-memory-page-faultssec/

– this occurs when the requested data pages are not accessible in current avilable
memory pages(RAM).

– 2 types of Pages fault/sec –

Hard page faults – happens when requested page is not present in physical memory(RAM)
Soft page faults – happens when requested page is available but not accessible since
the other process is currently accessing it.

– when page fault/sec occurs the process execution is put on wait state. OS will
search for requested page & copies to physical memory & then process is allowed to
access.

– There is no specific Page faults/sec value that indicates performance problems.
take a value of page faults/sec in normal situtaion & this can be referenced as
Baseline value.

– during the issue time, collect the value of Page faults/sec over a period of time &
compare with page reads/sec(this also indicates hard page faults). if the value of
page reads/sec also is regulary high during this time, then there is insufficient
Memory on server.
Available bytes (in OS – Perfmon)
—————–

ref – http://www.sqlshack.com/sql-server-memory-performance-metrics-part-2-available-
bytes-total-server-target-server-memory/
– this is the MB/GB amount of memory available to Processes for its execution.
– normal threshold value should always be More than 200 MB. if its constantly less
than 200 MB, then there is a insufficient memory on server.

– in case of insufficient memory, the values of pages/sec, page faults/sec will also
be high as there is not enough memory to store the reqd pages for read/write data.

– When there’s enough memory on the server, the available memory is high, page faults
are rare, and Pages/sec and Page faults/sec are low.
total server memory (in MSSQL Server)
————————

– the amount of memory assigned to the SQL server.

target server memory (in MSSQL Server)
————————-

– the amount of Memory SQL server requires to perform better.

Note– if the target server memory is much Greater than the Total server memory then
there is memory pressure.
Buffer Manager metrics.
===========================

page reads/sec (in mssql server)
—————–
ref – http://www.sqlshack.com/sql-server-memory-performance-
metrics-part-3-sql-server-buffer-manager-metrics-memory-
counters/#sthash.dVz9bpXm.dpuf

– no# of times the physical datapages read from disk per sec.
– value should be < 90. if > 90 would mean insufficient memory or
indexing issues.
– this is server level metrics, the value obtained is for all
databases.
– Please note that this is not the number of pages read from disk.

The value can be obtained from the sys.dm_os_performance_counters
system view

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ‘%Buffer Manager%’
AND [counter_name] = ‘Page reads/sec’
[\code]
page writes/sec(in mssql server)
—————
– shows the number of times (certain) pages were written to disk on
the server level
– the recommended value for Page writes/sec is below 90

Note: pages/sec = pages input/sec + pages output/sec

– High paging and disk I/O activity indicate insufficient memory. If
the excessive disk activity is not caused by paging (indicated by
normal Page Faults/sec values, compared to the defined metric
baseline), Page reads/sec and Page writes/sec will be high. In this
situation, it’s recommended to check the Lazy writes/sec and Page
Life Expectancy values as well, as the non-zero Lazy writes/sec and
low Page Life Expectancy require attentio
Pages Input/sec (in mssql server)
——————-
– the number of pages brought in from disk every second.
Note: Soft fault = pages input/sec – page faults/sec.
– value: recommended to be lower than 10.

Pages output/sec(in mssql server)
——————

– the number of pages written to disk every second
– these metrics (Pages Input/sec and Pages Output/sec.)
shows how many requested pages are not avilable in memory & had to
be read/write to/from disks.
– Note: pages/sec = pages input/sec + pages output/sec
– if value of Pages/sec > 50 constantly, then there is page faults
happening. additional investigation is needed. monitor disk
behaviour such as disk reads/sec & AVg disk read bytes/sec

– it’s good to check the Page Reads/sec value first and compare it
to Pages Input/sec. If the latter is greater, it indicates a high
page fault rate that can be solved by increasing memory designated
to SQL Server.
– value: recommended to be lower than 10.

– Both Pages Input/sec and Pages Output/sec values are recommended
to be lower than 10.

buffer cache hit ratio(BCHR)
————————-
ref – http://www.sqlshack.com/sql-server-memory-performance- metrics-part-4-buffer-cache-hit-ratio-page-life-expectancy/

– buffer cache hit ratio = [ no# of data pages present in pool/no#
of data pages required for process]

i.e lets say process A1 requires 100 data pages to accomplish task.
now the buffer cache has 95 records in buffer pool.

– so accroding to formula, BCHR = (95/100)
– recommended value: more than 90. a lower value indicates memory
issue.

– even after performance imporves, the BCHR value shows little
change.so only this metric alone is not enough to isolate the issue.
so consider Page life expectancy (PLE) for further trouleshooting.
The same as with other Buffer Manager metrics, it’s value can be
obtained from the sys.dm_os_performance_counters view

SELECTobject_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ‘%Buffer Manager%’
AND [counter_name] = ‘Buffer cache hit ratio’
[\code]
page life expectancy
——————–
ref – http://www.sqlshack.com/sql-server-memory-performance- metrics-part-4-buffer-cache-hit-ratio-page-life-expectancy/

– duration is secs a page resides in buffer pool.
– recommended value: above 300 secs(5 mins) & trend should be
stable. monitor this over a period of time. if there is frequent
fluctuation then it indicates memory issues.
– The cause for Page Life Expectancy values below 300 can be poor
index design, missing indexes, mismatched data types, insufficient
memory, etc.

http://www.sqlshack.com/sql-server-memory-performance-metrics-part- 5-understanding-lazy-writes-free-list-stallssec-memory-grants- pending/

http://www.sqlshack.com/sql-server-memory-performance-metrics-part- 6-memory-metrics/

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s