SQLTeam.com | Weblogs | Forums


Looking for guidance on compile/re-compile/batches stats from a SQL 2014 instance with one database. One minute sample.

batches/sec = 899
compiles/sec = 3
re-compiles/sec = 0.5

Aligning to that sys.dm_exec_procedure_stats shows sprocs cached_time along with execution_count restarting every few seconds.

(1) I would expect more re-compiles/sec than compiles/sec. Does a greater compiles/sec suggest some config setting that needs adjustment?
(2) Although we are within the 10% rule of thumb (compilations/sec is less than 10% of bathes/sec) it does not seem right to me that a given sproc is being compiled every few seconds.

Any help/guidance much appreciated.

Memory pressure?

Thank you for your reply. We have 256 GB memory on box
Page Life Expectancy = 259,194
Target Server Memory = 16,777,224
Total Server Memory = 16,766,624

BufferCacheHitRatio = 100.0

That doesn't look bad to me. 900 batches/second is basically nothing for SQL Server anyway. And of that, only ~3.5 need compiled per second. That's very low.

Performance-wise, you'd get a much bigger gain from reviewing indexes:

  1. in particular, make sure you have the best clustered index on every table;
  2. that indexes are compressed whenever reasonable.

What is max memory set to? 16,777,224 is just 16GB, unless you missing some zeroes or that's not in KB.

I do agree with Scott though. I'm not sure why you are worrying about such low numbers unless the same stored procs keep getting their plans removed from cache.