November 4, 2008 at 11:27 am
We had slow application performance which we quickly traced to our SQL Server 2008 cluster's Active Node. It appeared that we were having a memory issue The system has 16GB RAM, 14 allocated to SQL Server, and Task Manager indicated only 50MB of RAM was "available". (CPU stats were low, less than 10%.) SQL Server was swapping pages in and out of the pagefile like crazy. In PerfMon, Page life expectancy was hanging around an embarrassing 17.
I started Profiler and ran a tuning trace, and then started Tuning advisor and began to analyze the trace files. Suddenly, the Available RAM figure in Task Manager shot up to 8GB, and the CPU stats started being more lively. (Fluctuating between 25 and 50%.) It was as if a drain was clogged, and suddenly the clog broke and the backed up water drained in a flood.
To this point, we did not have any unusual activity going on--No failing / long running SQL Agent jobs, no long-running / large resultset returning queries in Activity Monitor, no "out of the ordinary" business operations which would generate a large amount of activity.
We have an IIS front-end application which does caching using SQL Query Notification (instead of polling notification) in a limited fashion, and had a problem with it when we were running SQL Server 2005 in 32-bit mode. (A Microsoft support ticket recommended we upgrade to 2008 and 64-bit OS, to clear out some "throttling" issues. It was part of our HDW/SW upgrade plan at the time anyway, so we did it.)
The question here is...Has anyone else out there experienced anything similar? Are there any places where we can start our investigation? We've checked the Event Logs and SQL Server Logs, and there are no messages of any kind to help us pinpoint why we were using so much memory in the first place, and why it suddenly was released.
If any of this is unclear, please let me know.
Thanks in Advance and Regards,
Jim
November 4, 2008 at 12:26 pm
Hi,
If you are very lucky and there hasn't been much activity you can query sys.dm_exec_query_stats and get something from it. However, if 8 GB was paged out then there is high risk that the particular plan that bogged down SQL Server was somewhere in those 8GB.
It also requires that the query in question was actually using a cached plan (or its plan got cached).
There is a sample query in BoL which you can adjust a little bit to fit our needs.
For the future; have a look at the Performance Data collector and Management Data Warehouse capabilities.
You also need to set you "max server memory" setting to something that will prevent the OS from coming into a situation when it needs to page out SQL Server memory. You have already seen how ugly it can get!
HTH!
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply