Cached plans getting pushed out of memory?

Question

Cached plans getting pushed out of memory?

Jesse Reich

Hall of Fame

Points: 3020
More actions
July 6, 2012 at 5:21 pm

#263718

I'm working with a legacy SQL Server machine:
SQL Server 2005 SP2
Windows Server 2003 Enterprise
AWE enabled
Pages locked in memory
31.GB RAM
Visible target memory: 27 GB
Memory used: 18 GB
We have a daily report that tracks the results from sys.dm_exec_query_stats but I noticed that there was a strange discrepancy between the results on that report and what I was seeing from monitoring the server directly.
When I started monitoring the number of rows in sys.dm_exec_query_stats, I noticed it would go up to about 5000, then drop to 4000 ,then go back, and so on.
When I run this query:
SELECT SUM(size_in_bytes) / 1024. / 1024. FROM sys.dm_exec_cached_plans
The value is about 575-600 MB. It never goes above 600. But when the system is under heavy load, this value will go up and drop, up and drop, in five minute intervals. I assume that all the cached plans that I expected to see the sys.dm_exec_query_status are getting pushed out because of this constant cleaning of the procedure cache.
But here is where I am confused. According to my SQL Server 2008 Internals book, my cache settings should be:
75 percent of visible memory from 0 to 4 GB + 10 percent of target memory from 4 GB to 64 GB.
That should be (4 GB * .75) + (23 GB * .10) = 5.3 GB.
But we are maxing out at 600MB. And according to Perfmon, the plan cache hit ratio is 68-72%.
I wonder if the cause of the plans being recycled so frequently is because the legacy application makes heavy use of inline SQL (although single use plans make up only 33% of the total).
I would think that the plan cache would be much larger, Is there a limitation to plan cache and memory from AWE? Or some other way to limit the plan cache?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

32 bit SQL or 64 bit? I assume 32 bit since you have AWE enabled.

The plan cache cannot use memory above the 2GB boundary on 32 bit servers. Only the data cache can use AWE memory above that limit.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

You can help your cause a little by adding the /3GB switch to your boot.ini, and optionally the /USERVA option to restrict the VAS split to less than 3:1.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I wouldn't recommend using the /3GB switch. That limits AWE memory to 16GB (because of the reduction in the number of page table entries). This would severely reduce the amount of data cache SQL can access (as half of that 32 GB of memory on the server would be inaccessible to Windows, assuming a 32-bit OS as well as 32-bit SQL) and risk server instability.

Using /3GB with more than 8GB of memory is not recommended and using it with more than 16 is unsupported.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jesse Reich Hall of Fame Points: 3020 More actions · Answer 4

Thanks for the detailed explanation. In this case it looks like our only options are to upgrade the server or refactor the application calls...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

If you are running 32 bit, I strongly suggest upgrading to 64 bit. The licenses are compatible (you can replace your 32 bit SQL instance with a 64 with no additional licenses), hardware has been 64-bit for years now and 64-bit eliminates so many memory problems.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

My bad, I neglected to consider the total memory on your system. I use the /3GB switch to solve the problem you have with proc cache size, but on a system with less than 8GB. An upgrade to 64-bit architecture will alleviate a lot of your troubles.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato