Cached plans getting pushed out of memory?

Question

Cached plans getting pushed out of memory?

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

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

Gail Shaw SSC Guru Points: 1004514 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: 182293 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: 1004514 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: 1004514 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: 182293 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