Friday, February 25, 2011

Using Perfmon for SQL Performance Tuning

A while ago I found this older but still fantastic blog post about SQL performance tuning:

Excellent video in there, well worth watching. Yes, the video is from 2006, but even now, it is just as relevant.

A little after I saw the blog post, I got the chance to put this in practice too; A customer suspected his SQL cluster wasn't performing too well. I added the counters from the video to the perfmon counter log, and kept the perfmon running.

Then our customer came back after a half a year; he found the SQL performance had degraded even more. We had a nice list of performance statistics now that went deeper than just CPU and memory usage. Now, I got the number of User Connections, and saw that it went up 4 times.

The perfmon counters Brent Ozar was talking about:

  • These are listed OBJECT first, then COUNTER
  • Memory – Available MBytes
  • Paging File – % Usage
  • Physical Disk – Avg. Disk sec/Read
  • Physical Disk – Avg. Disk sec/Write
  • Physical Disk – Disk Reads/sec
  • Physical Disk – Disk Writes/sec
  • Processor – % Processor Time
  • SQLServer: Buffer Manager – Buffer cache hit ratio
  • SQLServer: Buffer Manager – Page life expectancy
  • 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
  • System – Processor Queue Length

To find out what they all mean, see the video:

No comments:

Post a Comment