August 4, 2011 at 3:35 am
Do you have any resource_semaphore wait types when the server has issues?? Or do you not get any issues per se (or that you're aware of at the time) and just reviewing the error logs?
Do you have the min memory setting set as the same as max? Seen issues whereby if this is the case and the OS is starved it can't reclaim anything back from SQL if its desperate.
Could be related to Virtual Memory / VAS (Virtual Address Space), especially as you say you are using XML. I have a blog[/url] post relating to an issue i've had with it. My issue was down to a single process not running sp_XML_Removedocument after a failed import of a large XML file. I've known of CLR to cause similar issues. Have a read of Slava Ok's blog as he has a lot of information relating to these types of issues.
Alternatively, if you still believe its a memory related issue and want to find out the size of the cache's etc then have a look at Glenn Berry's 2005 diagnostics queries[/url], few memory related in there. Paul Randal has one showing the memory being wasted in the buffer pool.
Hopefully you'll find the answer in amongst all that 😉
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
August 4, 2011 at 7:35 am
dbcc memorystatus IS documented online, by Microsoft. It will have LOTS of useful information for you if you are having memory problems. There are also some support KBs on memory 'unloading' and CLR/appdomain issues too.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 4, 2011 at 2:26 pm
TheSQLGuru (8/4/2011)
dbcc memorystatus IS documented online, by Microsoft. It will have LOTS of useful information for you if you are having memory problems. There are also some support KBs on memory 'unloading' and CLR/appdomain issues too.
DBCC Memory Status does have a ton of information, but pretty crappy if you want to store that information off to analysis after the fact and not the quickest think to look at when the server is actually having memory issues.
Any idea on the links for those KB's? I would love to read them.
Thanks,
Fraggle
August 4, 2011 at 2:30 pm
ChrisTaylor (8/4/2011)
Do you have any resource_semaphore wait types when the server has issues?? Or do you not get any issues per se (or that you're aware of at the time) and just reviewing the error logs?
What is a resource_semaphore? Additionally, the only way we knew to solve the issue at the time was a instance restart, so no wait type stats.
Do you have the min memory setting set as the same as max? Seen issues whereby if this is the case and the OS is starved it can't reclaim anything back from SQL if its desperate.
No. 64gb physical ram on the server. Min Server memory set to 1024mb. Max set to 59392 MB.
Could be related to Virtual Memory / VAS (Virtual Address Space), especially as you say you are using XML. I have a blog post relating to an issue i've had with it. My issue was down to a single process not running sp_XML_Removedocument after a failed import of a large XML file.
We are not using sp_XML_Preparedocument or Removedocument. We are creating XML fragments inside of triggers and passing them into the Service Broker queue system. Then on the back end, we are shredding those using the XML Query functionalty.
Just getting the chance to sit down and read the articles. Thanks for the links.
Fraggle.
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply