I have a SQL Server 2012 instance with 96 GB of RAM that has the Buffer Cache Hit Ratio consistently at 100% and the PLE consistently over 500,000, so memory is obviously not a problem.
However, my monitoring tool shows the Procedure Cache Hit Ratio is usually in the low 90% range, dropping as low as 70% between 10pm and 7am when the user load is minimal. There are no significant nightly maintenance jobs that run other than a full backup which only takes about 6 minutes. The Plan Cache Size doesn't vary significantly over the course of 24 hours, staying around 7 MB.
If I run DBCC FREESYSTEMCACHE('SQL Plans'), it drops the Plan Cache Size to less than 1 MB, but seems to have no effect on the Procedure Cache Hit Ratio. We have not tried "optimize for ad hoc workloads", mainly due to risk aversion and a concern that if freeing the cache didn't make a difference, changing that setting may not either.
While I have no user complaints about performance, I'd like to understand this better.
What might be causing the nightly dip in the ratio?
What can I do to improve the Procedure Cache Hit Ratio?
Aucun commentaire:
Enregistrer un commentaire