The first place I would look is to make sure the AOS is not hitting the 'rails' (aka maxing out the server). If there is more than 15% of the physical memory free, I would look at the SQL server for the block. Odds are it might look like Figure 1 below (if using 8GB SQL box space).
As I understand it, SQL will dynamically cache memory and not release it until it hits a certain memory limit. Once it hits these limits, it will start releasing memory. Out of the box, the memory is set to be unlimited. I'm sure there are reasons for this but I don't know them. Please share below if you can add to this topic. All I know is that this issue was causing an issue in an implementation and this solution fixed it.
To help resolve the issue, You can right click the DB of interest, click properties, select memory, and finally select an amount of memory to use. When adjusting this maximum server memory, the actual consumed RAM will be a little higher. For example, adjusting to 6GB (6000 MB) will actually consume 6.4GB. If the value were 5500 (5.5GB), the actual memory consumed for the sqlservr.exe would be 5.9 GB. SQL must have enough memory to cache the appropriate number of objects as well as be small enough for the server to still have some RAM to do its thing as well. Its a balancing act so don't be afraid to play with these settings.
NOTE: In order to change these settings, your user must have the appropriate permissions. In my experience, the user must have either the Sys Admin or Security Admin roles added to allow the changes above.
Figure 1 - SQL without a max cap
Figure 2 - Changing the Maximum server memory setting in SQL
Figure 3 - The sever sqlserv.exe size from Figure 1 after the change from Figure 2
not an admin but here what can be found on MS blogoshere:ReplyDelete
Vlad, Thanks for the link. The above worked for us in the DEV and TEST boxes so I didn't look too much into it past that but it looks like this could also be from a known bug with linked servers on SQL Server 2008. Upgrading to the latest service pack should resolve this. http://blogs.msdn.com/b/psssql/archive/2009/09/22/if-you-use-linked-server-queries-you-need-to-read-this.aspxReplyDelete
Also of note, since an instance of SQL by default will try to consume as much memory as possible, if there is more than one instance of SQL running on a box, the settings above MUST be set or else each instance will compete each other creating a giant mess.