| Object | Counter | Preferred Value | Description |
| Memory | Available Mbytes | > 100MB | |
| Paging File | %Usage | < 70% | |
| Process (sqlservr) | %Privileged Time | < 30% of %Processor Time (sqlservr) | |
| Processor | %Privileged Time | < 30% of Total %Processor Time | |
| PhysicalDisk | Avg. Disk Sec/Read | < 8ms | |
| PhysicalDisk | Avg. Disk sec/Write | < 8ms (non cached) < 1ms (cached) | |
| SQLServer:Access Methods | Forwarded Records/sec | < 10 per 100 Batch Requests/Sec | |
| SQLServer:Access Methods | FreeSpace Scans/sec | <10 per 100 Batch Requests/Sec | |
| SQLServer:Access Methods | Full Scans / sec | (Index Searches/sec)/(Full Scans/sec) > 1000 | |
| SQLServer:Access Methods | Page splits / sec | compare with batch requests/sec | |
| SQLServer:Access Methods | Workfiles Created/Sec | < 20 per 100 Batch Requests/Sec | |
| SQLServer:Access Methods | Worktables Created/Sec | < 20 per 100 Batch Requests/Sec | |
| SQL Server:Buffer Manager | Buffer Cache hit ratio | > 90% , OLTP system 應大於 97% Checkpoint pages/sec, Lay writes/sec, Page life expectancy | |
| SQL Server:Buffer Manager | Checkpoint pages/sec Page written do disk during the checkpoint process, free up SQL Server cache Number of pages flushed to disk per second by a checkpoint or ther operation that required all dirty pages to be flushed | 當下列情況發生時，代表 Memory pressure High Checkpoint pages/sec High lzay writes/sec low page life expectancy (<300 seconds) | |
| SQL Server:Buffer Manager | Free list stalls/sec | < 2 | |
| SQL Server:Buffer Manager | Lazy Writes/Sec | < 20 | |
| SQL Server:Buffer Manager | Page Life Expectancy | > 300
| |
| SQLServer:Buffer Manager | Page lookups/sec | (Page lookups/sec) / (Batch Requests/sec) < 100 | Number of requests to find a page in the buffer pool. When the ratio of page lookups to batch requests is much greater than 100, this is an indication that while query plans are looking up data in the buffer pool, these plans are inefficient. Identify queries with the highest amount of logical I/O's and tune them. |
| SQL Server:Locks | Lock Requests/sec | (Lock Request/sec)/(Batch Requests/sec) < 500 | |
| SQLServer:SQL Statistics | SQL Compilations/sec | < 10% of the number of Batch Requests/Sec | |
| SQLServer:SQL Statistics | SQL Re-Compilations/sec | < 10% of the number of SQL Compilations |
參考
https://blogs.msdn.com/jchiou/archive/2007/11/21/sql-2005-performance-counter.aspx
http://www.grumpyolddba.co.uk/monitoring/Performance%20Counter%20Guidance%20-%20SQL%20Server.htm
沒有留言:
張貼留言