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?

  • 1
    "Batch Requests/sec" indicates the number of batches processed per second. There could easily be multiple compiles per batch if you are issuing multiple T-SQL statements per batch, and those statements require compilation. This would indicate that you may have a memory-bound system with a lot of ad-hoc queries being recalculated. Do you have optimize for ad hoc workloads configured? – Max Vernon May 2 '14 at 20:26
  • @Max optimize for ad hoc workloads would at most double the number of compilations, unless every single statement is unique - in which case it won't have any effect at all. Or, I suppose, if it is severely memory bound to the point that even stubs are getting evicted. – Aaron Bertrand May 2 '14 at 20:29
  • The last prod server I worked on had full plans and stubs being evicted with a lifetime of less than 5 seconds. Made me wish for a larger plan cache. – Max Vernon May 2 '14 at 20:36
  • 2
    @Max wow, that's crazy. Sounds like someone was really cheap on memory. That said, I've always wished there were knobs for controlling the amount of memory given to buffer pool vs. plan cache. For example, imagine if I have 16 GB of memory, and my data is only 2 GB, but I have 12 GB worth of query plans because of all of the different ways the data is accessed? I'm never going to see all 12 GB make it into the plan cache... – Aaron Bertrand May 2 '14 at 20:39
  • The machine had 3 instances, EVERY query was ad-hoc, and a large percentage of queries were RBAR. Total memory for the machine was 128GB (not too bad) - part of my recommendation was to up that to 256GB. Having said that, I still don't think the plan cache will be big enough. Not to mention the bucket count of 40001 not being big enough. Also, I'd love to be able to set SQL Servers page size to 4KB instead of 8KB for that customer, like in Sybase. Vast majority of pages had over 50% free bytes. – Max Vernon May 2 '14 at 20:46
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, thanks for your reply. I think what you're saying makes sense. Perhaps my understanding of Compilations/sec was a little off. You're saying that Compilations/sec is on a per statement basis? For some reason, I thought SQL Server compiles an execution plan at the batch level. – John Russell May 2 '14 at 20:35
  • 1
    @JohnRussell It did that many, many versions ago - none that are currently supported. – Aaron Bertrand May 2 '14 at 20:36
  • 1
    @JohnRussell A batch (which may contain multiple statements) is indeed compiled for the first time all at once. Each statement within the batch may be recompiled individually (perhaps even immediately!) for a variety of reasons, from changes in statistics to schema changes. The ability to perform statement-level recompilation was introduced in SQL Server 2005. – Paul White May 3 '14 at 11:13
  • Definitely agreed with Aaron here. These generalized numbers are poor substitutes for a decent baseline on its best day. And that's a generous statement. These numbers can hardly be considered a solution at all. Great advice, Aaron. +1. – Thomas Stringer May 4 '14 at 2:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.