5

I've been looking at various PerfMon metrics on one of our client's SQL Server instances trying to get a good gauge of where our database (or server) could use improvements.

One measure that has me puzzled is the ratio of two PerfMon metrics: Compilations/sec and Batch Requests/sec.

According to this post, "It's a general rule of thumb that Compilations/sec should be at 10% or less than total Batch Requests/sec".

Our application has a Windows Service that invokes scheduled calculations against the database every hour. Like clockwork, in my PerfMon CSV data, I can see the spikes in Compilations/sec and Batch Requests/sec. What I didn't expect to see was the number of Compilations/sec to exceed Batch Requests/sec.

I'm looking at 15 second samples from PerfMon centered around the time where our calculations kick off:

enter image description here

What does this typically indicate? Does this even make sense? Why would we be compiling more statements than executing? Am I missing something?

Community
  • 1
John Russell
  • 717
  • 1
  • 8
  • 17

1 Answers1

7

Sorry, not to disparage Thomas' advice, but please take "general rules" with a grain of salt, or just throw them out the window altogether.

Baseline.

What is normal for your system? Is the system currently responding ok?

If there is no performance issue, don't try to compare your system to some number someone plucked out of the air or potentially based off some very specific system and workload years ago, and drop everything to try to "fix" it.

Specifically, batch requests and compilations don't have a very nice and handy correlation in ALL scenarios. You need to understand your workload before you start panicking because your counters hit some threshold someone put in a post somewhere. If all of your batches consist of exactly one statement, then yes, having more compilations/sec than batch requests/sec might seem out of the ordinary (but still might not indicate a problem). In most cases, you are sending more than one statement in a batch. If this is the case - and particularly if you are using things like ORMs or a lot of highly variable dynamic SQL, where you will be suffering from a high number of compilations - I would really not be surprised to see one counter higher than the other.

Whether you need to do something about that, in that case, is a completely different problem.

Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624