Years ago, my first go-to tool for performance tuning was Performance Monitor: it was available everywhere, worked in all versions, and gave me a lot of useful information without paying for a monitoring tool. Since then, I’ve moved on – wait stats are a much better starting point – but there are still 3 good Perfmon counters that I rely on.
No, not Page Life Expectancy, Buffer Cache Hit Ratio, and Disk Queue Length. Those are still hot garbage.
1. Forwarded Fetches per Second
When your tables don’t have clustered indexes, you’ve got a heap: a table organized in random order. Heaps are notoriously bad ideas if you do updates on variable-length fields because as SQL Server runs out of space on a page, it’ll move a row to another page where there’s available space for your new update. The more of these you have, the higher penalty you pay when scanning a table.
The Perfmon counter SQLServer:Access Methods: Forwarded Records/sec tracks how many of times SQL Server had to jump around while reading. (Think of these as extra logical reads to deliver query results.) The higher this number is, the more extra logical reads you’re doing. I don’t gauge the number by itself – I look at it as a percentage of the reads we’re doing overall. If I can cut the logical reads we’re doing by, say, 10% just by rebuilding the heap, great! That’s an easy win.
With a name like “Forwarded Records/sec” you would reasonably assume that querying this number would get you the, uh, forwarded records that occurred in any given second. Nope. It’s a cumulative number from when SQL Server last restarted. That means in order to get actionable data, you need to measure this over time, and do differentials between measurements.
2. Memory Grants Pending
When your query starts executing, it usually needs a memory grant to do things like sorts, joins, and parallelism. I find this topic really intriguing, and you can probably tell – we’ve got a whole category of memory grant posts.
SQL Server:Memory Manager: Memory Grants Pending tells you the number of queries that are waiting on a memory grant before they can even START executing. When this number > 0, your apps are probably getting query timeouts, and the sysadmins are confused because CPU time looks low, but SQL Server is slow.
3. Compilations per Second
When you execute your beautifully hand-crafted query, SQL Server has probably never seen anything like it before – well, at least not since it restarted – and has to build a query execution plan.
With a regular transactional app, the same queries probably come in over and over, just with different parameters. (Data warehouses and reporting systems with a lot of truly unique queries are different – we’ll set those aside for now.)
The SQLServer: SQL Statistics: Compilations/sec counter measures the number of times
per second (hahaha, you wish) that SQL Server has had to compile a plan for a query it hasn’t seen before. The best way to evaluate this is to frame it in terms of the SQLServer:SQL Statistics: Batch Requests/sec counter, which, as you can probably guess, is also not measured per second. Totally up to you to do a differential, or to use the open source sp_BlitzFirst to do the differentials for you.
In a transactional system, I expect to see 90% (or higher) query plan reuse – so Compilations/sec should be 10% (or less) of the Batch Request/sec measure.
These 3 Perfmon counters shouldn’t be the first place you look – wait stats are still a better starting point – but sooner or later, you’re going to hit one of these issues that require Perfmon counters to solve. Ol’ trusty Perfmon is still there for you.