June 25, 2014 at 2:57 am
Hi
Is it correct in saying that if your compilations/sec are greater than 10% of your batch requests/sec then sql server is supposedly under memory pressure - in that it is having to compile queries as opposed from using plans in the cache ?
Currently according sql monitor (redgate) my average batch requests/sec is at 11.2 and my compilations\sec is at 2.5 which is hgher than 10% (the sample was over an hour period)
I am not however getting any complaints from end users about poor performance.
Is this 10% figure strictly true or is it a bit like the PLE measurement of 300 that we still banded around the internet ?
June 25, 2014 at 3:09 am
If compilations are high, you may want to investigate why cached plans aren't being reused. It's not an automatic 'you are under memory pressure' though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 25, 2014 at 3:23 am
Would you say they are high given the numbers I mentioned ?
Any pointers on how to investigate why cached plans may not be being reused ?
June 25, 2014 at 3:57 am
To investigate why you're getting recompiles, I'd put extended events on the server. You can capture the recompile event and it tells you specifically why it was recompiling. You can also look for cache miss events to understand why a given query isn't being found in cache.
And don't trust any hard number. 10% recompilations just means 10% recompilations. It's neither a good nor a bad thing. It's what your system is doing. It may be perfectly normal. As you say, you're getting no complaints and you don't have any other indications of performance problems.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 25, 2014 at 4:01 am
PearlJammer1 (6/25/2014)
Would you say they are high given the numbers I mentioned ?
You're the only one who can say if they're high, because 'high' is a sustained value over what's normal for your system.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply