• Memory – Available MBytes
  • Physical Disk – Avg. Disk sec/Read
  • Physical Disk – Avg. Disk sec/Write
  • Physical Disk – Disk Reads/sec
  • Physical Disk – Disk Writes/sec
  • Physical Disk — % Disk Time — _Total
  • Physical Disk — Current Disk Queue Length — _Total
  • Processor – % Processor Time
  • PageFile – % Usage
  • System — % Total Processor Time
  • System – Processor Queue Length
  • SQLServer: General Statistics – User Connections
  • SQLServer: Memory Manager – Memory Grants Pending
  • SQLServer: SQL Statistics – Batch Requests/sec
  • SQLServer: SQL Statistics – Compilations/sec
  • SQLServer: SQL Statistics – Recompilations/sec
  • SQLServer: Access Methods – Full Scan/sec
  • SQLServer: Buffer Manager: Buffer Cache Hit Ratio
  • SQLServer: Buffer Manager: Page life expectancy
  • SQLServer: Memory Manager – Target Server Memory (KB)
  • SQLServer: Memory Manager — Total Server Memory (KB)
  • SQLServer: Locks – Average Wait Time
  • SQLServer: General Statistics — User Connections

Disk Activity

  • Physical Disk: % Disk Time: This counter monitors the portion of time the disk is busy with read/write activity. If the Physical Disk: % Disk Time counter is close to or over 90%, it indicates that too many system requests are waiting for disk access (check this via the Physical Disk: Current Disk Queue Length counter). The number of pending I/O requests should be sustained at no more than 1.5 to 2 times the number of spindles of the physical disk.
  • Physical Disk: Average Disk Queue Length: number of I/O operations waiting (again, over 1.5 or 2 times the number of disk spindles is bad)
  • SQL Server Buffer Manager – Page reads/sec and page writes/sec. If this counter rises above your baseline, it may indicate the need for more hardware power

Processor Utilization

  • Processor: % Processor time: A consistent 80-90% is too high. Multiprocessor systems have a separate instance for each CPU.
  • Processor: % Privileged time: indicates the time spent on Windows kernel commands (SQL Server I/O requests). If both this and Physical Disk counters are high, there might be a need for a faster disk or lower load for this server.
  • Processor: % user time: the percentage of time the CPU spends on user processes (SQL Server)
  • Processor: Queue Length: the number of threads waiting for processor time. A high number may indicate the need for faster or more processors.

Memory

  • Memory: Available MBs: indicates how much memory is available for new processes
  • Memory: Pages/sec: this counter indicates how many times the virtual memory is getting accessed. A rule of thumb says that it should be lower than 20. Higher numbers might mean excessive paging. Using Memory: Page Faults/sec can further indicate whether SQL Server or some other process is causing it.

Monitor SQL Server

SQL Server works with objects and counters, with each object comprising one or more counters. For example, the SQL Server Locks object has counters called Number of Deadlocks/sec or Lock Timeouts/sec.

  • Access Methods – Full scans/sec: higher numbers (> 1 or 2) may mean you are not using indexes and resorting to table scans instead.
  • Buffer Manager – Buffer Cache hit ratio: This is the percentage of requests serviced by data cache. When cache is properly used, this should be over 90%. The counter can be improved by adding more RAM.
  • Memory Manager – Target Server Memory (KB): indicates how much memory SQL Server “wants”. If this is the same as the SQL Server: Memory Manager — Total Server Memory (KB) counter, then you know SQL Server has all the memory it needs.
  • Memory Manager — Total Server Memory (KB): much memory SQL Server is actually using. If this is the same as SQL Server: Memory Manager — Target Server Memory (KB), then SQL Server has all the memory it wants. If smaller, then SQL Server could benefit from more memory.
  • Locks – Average Wait Time: This counter shows the average time needed to acquire a lock. This value needs to be as low as possible. If unusually high, you may need to look for processes blocking other processes. You may also need to examine your users’ T-SQL statements, and check for any other I/O bottlenecks.

 

  • Network Interface-> Bytes Total (per card): Represents the sum of network throughput for the card. This is the key counter for network throughput.
  • Physical Disk-> %Disk Time Physical Disk-> Disk Reads/sec Physical Disk-> Disk Writes/sec: Three counters that track the activity in the disk subsystem. The disk subsystem can very easily become the bottleneck on any system. On the front-end Web server the disk utilization should be quite low because the content and images for a page should fit well within the file cache. The primary disk activity is the log file that is now well tuned for performance in Windows 2000.

  • Memory -> Pages/sec: Measures the actual memory requests that are made to the hard disk. A high rate can indicate a lack of memory resources or a poorly laid out solution.

However, the SQL server makes extensive use of the physical disk subsystem. Planning and calibrating this subsystem is the key component of a fast DBMS.

Visites: 286