Page Life Expectancy – It is an age of a data page in seconds in the buffer cache or
buffer memory after querying the tables with the loading data page into the
buffer memory. Page Life Expectancy value indicates the memory pressure in
allocated memory to the SQL Server instance.
PLE.
Recommended value is greater than 300 Seconds.
Buffer Cache Hit Ratio – Buffer Cache Hit Ratio is
the percentage of sql server pages requested and retrieved from the buffer
cache without reading from disk.
SQL Server reads data
pages into a pool of memory called the Buffer Cache. The Buffer Cache Hit Ratio
is a good indicator of Buffer Cache performance.
The
calculation is: total number
of cache hits \ total number of cache lookups from the previous few
thousand-page requests.
BCHR. Recommended
value is greater than 95 %.
Page
Read per Seconds – Page
Reads/sec is the rate at which the disk is read to resolve page faults.
Recommended value
is greater than 90 per seconds.
Page Write per Seconds
– Page Writes/sec is the rate at which page data is written to
the disk to open up space in physical memory.
Recommended value
is greater than 60 per seconds.
Lazy
Writes per Seconds – Indicates
the number of buffers written per second by the buffer manager's lazy writer.
Recommended value
is less than 20 per seconds
Memory Grant Pending –
Recommended value is less than
or equal to 1.
Free List Stall per Seconds –
Free list
stalls/sec is the SQL Server Buffer Manager performance objects which Indicates
the number of requests per second that had to wait for a free page.
Recommended value
is below 2.
Some
other counters are...
Total Server Memory – Total Server Memory (KB) is the current buffer pool size that
SQL Server has allocated to itself. This performance counter indicates the
operational used memory at that point in time.
Target Server Memory – Target Server
Memory (KB) is the amount of memory that SQL Server can potentially allocate to
the buffer pool under its current workload.
Pages per Seconds – Pages/sec is the
rate at which pages are read from or written to disk to resolve hard page
faults.
Causes: One or more applications are using memory heavily, and the system is having to page data to and from disk to meet the demand.
Paging File % usages – Paging File % Usage” displays the percentage of the paging file that is
currently in use. The paging file extends the RAM’s capacity, as it
stores RAM data that has not been used or accessed lately.
No comments:
Post a Comment
If you have any doubt or question, please contact us.