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.
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:
- in particular, make sure you have the best clustered index on every table;
- 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.