If Memory Utilization is 100% then identify
Memory\Available Bytes :If this counter is greater than 10% of the actual RAM in your machine then you probably have more than enough RAM and don't need to worry.
Disk Related:
Physical Disk (instance)\Disk Transfers/sec counter for each physical disk and if it goes above 25 disk I/Os per second then you've got poor response time for your disk.
Avg Disk Sec/Read- Look for <8Msec or less as optimal.
Avg Disk Sec/Write - Look for <8Msec(Non Cached) <1Msec(Cached)
Average Disk Queue Length can vary based on the activities (Typically 30 is a red flag)
Note: PerfMon is far less useful against a SAN. Check the vendor for monitoring disk performance tools specific to SAN.
PLE: Page Life Expectancy
The time in seconds the page stays in memory pool without being referenced before it is flush. should be >= 300. A lower or declining value may indicate memory pressure.
SQL Compiles/sec & Recompiles/sec
<2/sec negligible, 2-20/sec could be investigated, 20-100 poor, >100 potentially serious, I would really like a means of assessing the cost of compiles, as simple statement compile cost is low, a complex query could take 1min to compile.
Page Splits/Sec
Occurs when a 8KB page fills and must be split into two new 8K pages.
Buffer Cache Hit Ratio
Indicates how often SQL Server can get data from the buffer rather than disk (since the last restart of instance). >90% for OLAP, >95% for OLTP system . If buffer cache hit ratio is less than the bench marks then you need to increase the amount of memory available to SQL Server so that buffer cache hit ratio will in crease.
Lazy Writes/Sec
The number of times per second that lazy writer moves dirty pages from buffer to disk to free buffer space <20
Page Reads/Sec and Page Writes/Sec
Number of physical database page reads and writes issued respectively <90
Memory\Available Bytes :If this counter is greater than 10% of the actual RAM in your machine then you probably have more than enough RAM and don't need to worry.
Disk Related:
Physical Disk (instance)\Disk Transfers/sec counter for each physical disk and if it goes above 25 disk I/Os per second then you've got poor response time for your disk.
Avg Disk Sec/Read- Look for <8Msec or less as optimal.
Avg Disk Sec/Write - Look for <8Msec(Non Cached) <1Msec(Cached)
Average Disk Queue Length can vary based on the activities (Typically 30 is a red flag)
Note: PerfMon is far less useful against a SAN. Check the vendor for monitoring disk performance tools specific to SAN.
PLE: Page Life Expectancy
The time in seconds the page stays in memory pool without being referenced before it is flush. should be >= 300. A lower or declining value may indicate memory pressure.
SQL Compiles/sec & Recompiles/sec
<2/sec negligible, 2-20/sec could be investigated, 20-100 poor, >100 potentially serious, I would really like a means of assessing the cost of compiles, as simple statement compile cost is low, a complex query could take 1min to compile.
Page Splits/Sec
Occurs when a 8KB page fills and must be split into two new 8K pages.
Buffer Cache Hit Ratio
Indicates how often SQL Server can get data from the buffer rather than disk (since the last restart of instance). >90% for OLAP, >95% for OLTP system . If buffer cache hit ratio is less than the bench marks then you need to increase the amount of memory available to SQL Server so that buffer cache hit ratio will in crease.
Lazy Writes/Sec
The number of times per second that lazy writer moves dirty pages from buffer to disk to free buffer space <20
Page Reads/Sec and Page Writes/Sec
Number of physical database page reads and writes issued respectively <90
Note: If any of the above mentioned counter values will fall down less than the bench marks then check with developer or application team about the recent code changes or heavy transactions in the project and then suggest to increase the available RAM to SQL Server.
No comments:
Post a Comment