{"id":246,"date":"2025-01-20T15:19:10","date_gmt":"2025-01-20T13:19:10","guid":{"rendered":"http:\/\/mariospavlidis.site\/?p=246"},"modified":"2025-01-20T15:19:10","modified_gmt":"2025-01-20T13:19:10","slug":"useful-perfmon-counters-for-sql-server-monitoring","status":"publish","type":"post","link":"http:\/\/130.61.57.200\/index.php\/2025\/01\/20\/useful-perfmon-counters-for-sql-server-monitoring\/","title":{"rendered":"Useful Perfmon Counters for SQL Server Monitoring"},"content":{"rendered":"\n

<\/h4>\n\n\n\n
\n\n\n\n\t\n\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t
Object Name<\/th>Counter<\/th>Description<\/th>Ideal<\/th>\n<\/tr>\n<\/thead>\n
Processor<\/td>%Processor Time\/_Total<\/td>All processors usage<\/td><70%<\/td>\n<\/tr>\n
System<\/td>Processor Queue Length<\/td>Threads ready to run<\/td>Near 0 but at most <10 per processor<\/td>\n<\/tr>\n
Network Interface<\/td>Bytes Total\/sec<\/td>Total network load<\/td><60% bandwidth<\/td>\n<\/tr>\n
Memory Manager<\/td>Total Server Memory<\/td>Current Memory consumption<\/td>OS memory = 2GB+ 2MB*(500 + 16 *(CPUs-4))<\/td>\n<\/tr>\n
<\/td><\/td><\/td>Rest is max memory for instance<\/td>\n<\/tr>\n
Memory Manager<\/td>Target Server Memory<\/td>Ideal Memory for optimal operation<\/td>“=Total Server Memory<\/td>\n<\/tr>\n
Memory Manager<\/td>Free Memory<\/td>Not used memory<\/td>>0 but not too large<\/td>\n<\/tr>\n
Memory Manager<\/td>Stolen Server Memory<\/td>Server Memory stolen for other purposes than DB pages<\/td>0<\/td>\n<\/tr>\n
Memory Manager<\/td>Memory Grants Pending<\/td>Processes waiting for memory workspace<\/td>0 or 1<\/td>\n<\/tr>\n
Buffer Manager<\/td>Free List stalls\/ sec<\/td>Requests had to wait for free page<\/td>0<\/td>\n<\/tr>\n
Buffer Manager<\/td>Buffer Cache hit Ratio<\/td>Pages in memory without reading from disk<\/td>>99%<\/td>\n<\/tr>\n
Buffer Manager<\/td>Lazy Writes\/sec<\/td>Indicate dirty pages from lazy writer (NOT CHECKPOINT)<\/td>0<\/td>\n<\/tr>\n
Buffer Manager<\/td>Page Life Expectancy<\/td>Seconds that a page stays in Buffer Cache before flush<\/td>300s * Total memory (GB)\/4GB<\/td>\n<\/tr>\n
Buffer Manager<\/td>Page reads\/sec<\/td>Physical Reads<\/td>Not too high compared to Logical Reads<\/td>\n<\/tr>\n
Buffer Manager<\/td>Page lookups \/sec<\/td>Logical Reads<\/td><\/td>\n<\/tr>\n
Memory<\/td>Available Mbytes<\/td>Memory Available to OS.<\/td>>2-4GB<\/td>\n<\/tr>\n
Memory<\/td>Pages \/ sec<\/td>Pages read from or written to disk to resolve hard page faults<\/td>0<\/td>\n<\/tr>\n
Plan Cache<\/td>Cache Hit Ratio<\/td>Ratio between cache hits and lookups for plans<\/td>>95%<\/td>\n<\/tr>\n
Paging File<\/td>%Usage<\/td>Page file in use<\/td>0<\/td>\n<\/tr>\n
Process<\/td>Working Set<\/td>The working set in Bytes<\/td>If much smaller than assigned memory then it\u2019s overallocated<\/td>\n<\/tr>\n
Physical Disk<\/td>Avg. Disk Queue Length<\/td>Read Write requests queued for selected disk<\/td>Near 0<\/td>\n<\/tr>\n
Physical Disk<\/td>Avg. Disk Sec\/Read<\/td>Average time for one read<\/td><20ms<\/td>\n<\/tr>\n
Physical Disk<\/td>Avg. Disk Sec\/Write<\/td>Average time for one write<\/td><20ms<\/td>\n<\/tr>\n
Physical Disk<\/td>%Disk Time<\/td>Busy disk time<\/td><50%<\/td>\n<\/tr>\n
Physical Disk<\/td>Disk Reads\/Sec<\/td>Rate of read operations on the disk.<\/td><85% of disk capacity<\/td>\n<\/tr>\n
Physical Disk<\/td>Disk Writes\/Sec<\/td>Rate of write operations on the disk.<\/td><85% of disk capacity<\/td>\n<\/tr>\n
Access Methods<\/td>Full Scans\/sec<\/td>Table or full Index Scans. Can indicate further need for optimization with new indexes.<\/td><\/td>\n<\/tr>\n
Access Methods<\/td>Index Searches\/sec<\/td>Index Range scans, single index record fetch, reposition within index<\/td><\/td>\n<\/tr>\n
Access Methods<\/td>Range Scans\/sec<\/td>Range scans through indexes<\/td><\/td>\n<\/tr>\n
Access Methods<\/td>Page Splits \/ Sec<\/td>Number of page splits as a result of overflowing index pages Indicate fragmentation<\/td><\/td>\n<\/tr>\n
Access Methods<\/td>Forwarded Records\/sec<\/td>Records fetched through forwarded record pointers. Indicate fragmentation<\/td><\/td>\n<\/tr>\n
Access Methods<\/td>Table Lock Escalations\/sec<\/td>Locks escalations to table lock<\/td><\/td>\n<\/tr>\n
SQL Statistics<\/td>Batch Requests\/sec<\/td>Number of batch requests received by server. Indicates load<\/td><\/td>\n<\/tr>\n
SQL Statistics<\/td>SQL Compilations\/sec<\/td>Compiles. Hard parses for first time<\/td><\/td>\n<\/tr>\n
SQL Statistics<\/td>SQL Re-Compilations\/sec<\/td>Re-compiles. Hard parses for existing sql. High number might indicate not use of bind variables<\/td><\/td>\n<\/tr>\n
General Statistics<\/td>Processes blocked<\/td>Blocked processes<\/td><\/td>\n<\/tr>\n
General Statistics<\/td>User Connections<\/td>Used for calculating a baseline<\/td><\/td>\n<\/tr>\n
Databases<\/td>Transactions\/sec<\/td>Used for calculating a baseline<\/td><\/td>\n<\/tr>\n
Databases<\/td>Log Growths<\/td>Number of log growths<\/td><\/td>\n<\/tr>\n
Databases<\/td>Percent Log Used<\/td>Log usage<\/td><\/td>\n<\/tr>\n
Locks<\/td>Average Wait Time (ms)<\/td>Lock average wait<\/td><\/td>\n<\/tr>\n
Locks<\/td>Number of Deadlocks\/sec<\/td>Deadlocks per second<\/td><\/td>\n<\/tr>\n
Locks<\/td>Lock Requests\/sec<\/td>New locks and lock conversions<\/td><\/td>\n<\/tr>\n
Wait Statistics<\/td>Lock Waits<\/td>Processes waiting on a lock.<\/td><\/td>\n<\/tr>\n
Wait Statistics<\/td>Log Buffer Waits<\/td>Processes waiting for log buffer to be available.<\/td><\/td>\n<\/tr>\n
Wait Statistics<\/td>Log Write Waits<\/td>Processes waiting for log buffer to be written.<\/td><\/td>\n<\/tr>\n
Wait Statistics<\/td>Memory grant queue waits<\/td>Processes waiting for memory grant to become available.<\/td><\/td>\n<\/tr>\n
Wait Statistics<\/td>Network IO waits<\/td>Processes waiting for Network IO.<\/td><\/td>\n<\/tr>\n
Wait Statistics<\/td>Non-Page latch waits<\/td>Processes waiting for Non-page latches.<\/td><\/td>\n<\/tr>\n
Wait Statistics<\/td>Page IO latch wait<\/td>Processes waiting for Page IO latches.<\/td><\/td>\n<\/tr>\n
Wait Statistics<\/td>Page Latch waits<\/td>Processes waiting for Page latches, not including IO latches<\/td><\/td>\n<\/tr>\n
Wait Statistics<\/td>Thread-safe memory objects waits<\/td>Processes waiting on thread-safe memory allocators.<\/td><\/td>\n<\/tr>\n
Wait Statistics<\/td>Transaction ownership waits<\/td>Processes synchronizing access to transaction.<\/td><\/td>\n<\/tr>\n
Wait Statistics<\/td>Wait for the worker<\/td>Processes waiting for worker to become available.<\/td><\/td>\n<\/tr>\n
Wait Statistics<\/td>Workspace synchronization waits<\/td>Processes synchronizing access to workspace.<\/td><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":104,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-246","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mssql"],"blocksy_meta":[],"_links":{"self":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/246","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/comments?post=246"}],"version-history":[{"count":4,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/246\/revisions"}],"predecessor-version":[{"id":255,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/246\/revisions\/255"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media\/104"}],"wp:attachment":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media?parent=246"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/categories?post=246"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/tags?post=246"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}