Categories
Sql Server Technology - Tips

Performance Monitor

Performance Monitor

In this tutorial we will discuss about Performance Monitor. You can use Windows Performance Monitor to examine how programs you run affect your computer’s performance, both in real time and by collecting log data for later analysis.

Windows Performance Monitor uses performance counters, event trace data, and configuration information, which can be combined into Data Collector Sets.

perfmon

I am using Performance Monitor to monitor SQL Server, because it not only provides us with information on how SQL Server is performing, but it also lets us know how Windows Server is doing.

Some of key counters I like to watch on a regular basis:

  1. Memory — Pages/Sec: To see how much paging my server is doing. This should be close to zero on a dedicated SQL Server. You will see spikes during backups and restores, but this is normal.
  2. Network Interface — Bytes Total/sec: To see how much network activity is going on.
  3. PhysicalDisk — % Disk Time — _Total: To see how busy all the disk drives are.
  4. PhysicalDisk — Current Disk Queue Length — _Total: Also to see how busy the drives are.
  5. System — % Total Processor Time: To see how busy all the CPUs are as a whole.
  6. System — Processor Queue Length: Also see how busy the CPUs are.
  7. SQLServer: General Statistics — User Connections: To see how many connections (and users) are using the server. Keep in mind that one connection does not equal one user. A single user can have more than one connection, and a single connection can have more than one user.
  8. SQLServer: Access Methods — Page Splits/sec: Lets me know if page splits are an issue or not. If so, then that means I need either to increase the fill factor of my indexes, or to rebuild the indexes more often.
  9. SQLServer: Buffer Manager — Buffer Cache Hit Ratio: To find out if I have enough memory in the server. Keep in mind that this ratio is based on the average of the buffer hit cache ratio since the SQL Server service was last restarted, and is not a reflection of the current buffer cache hit ratio.
  10. SQLServer: Memory Manager — Target Server Memory (KB): To see how much memory SQL Server wants. If this is the same as the SQLServer: Memory Manager — Total Server Memory (KB) counter, then I know that SQL Server has all the memory that it wants.
  11. SQLServer: Memory Manager — Total Server Memory (KB): To see how much memory SQL Server actual is using. If this is the same as SQLServer: Memory Manager — Target Server Memory (KB), then I know that SQL Server has all the memory that it wants. But if this is smaller, then SQL Server needs more available memory in order to run at its optimum performance.