Greetings,
Nice article. After years monitoring Oracle, DB2 and SQL Server databases, I made for each of those RDBMS a three level performance monitoring check list. The first level is a day to day monitor list. I run on each server a perfmon logging into a csv file the results and doing some trending with Excel. It is a 3 minutes process per server.
When I notice something abnormal I run the second level. 95% of the time, I can identify positively there is a problem and begin to address it. If I don't see anything with the level 2, I go on level 3. I can say frankly, I went to level 3 only 7-8 times in the last 5 years.
Level 1:
Processor >> % Total Processor time |
Physical Disk >> % Disk utilization |
Physical Disk>>Avg. Disk Queue Length |
System >> Processor Queue Length |
System Object : Avg. Disk Queue Length |
à Free Memory (KBs) (Task Manager) |
à Total memory available (Task manager) |
SQL Memory Manager >>Total server memory |
Memory Object : Available Mbytes |
Level 2:
Check SQL Connectivity issue |
Memory >>Pages/Sec |
SQL Buffer Manager>>Pages Writes/sec |
SQL Buffer Manager>>Pages Reads/sec |
SQL Buffer Manager>>Buffer Cache Hit Ratio |
SQL Access Methods>>Index Searches/sec |
SQL Server Databases Methods: Transactions/Sec |
SQL Access Methods >>Page Splits/sec ctr |
SQL Blocking situation |
SQL Error or warning message in the log |
SQL Abnormal activity in current activity or Profiler (logging errors and warnings) |
Level 3:
SQL Server Locks Object : Average Wait Time |
SQL Locks>>Lock Waits |
SQL Locks>>Number of deadlocks |
Server Network Interface Object>>Bytes total/sec |
SQL SQL Statistics >> SQL compilations/sec |
SQL SQL Statistics >> SQL re-compilations/sec |
Thank you
Alain Gagne OCP, DB2 expert, MCDBA, MCSE, MCSD