May 27, 2008 at 7:28 pm
Hi,
I was contacted today for an issue with a SQL 2005 server. The problem reported is that the server 'freezes' for a temporary period on a regular basis .... i.e to say CPU, Memory both shoot up to 100%. The server does not do anything during this time.
Here a few stats -
SQL Server 2005
AWE enabled (min, max server memory left to defaults)
8 CPU (x86 3GHz)
8GB memory
When I checked the perfmon logs, I can see the server pages a lot (1500 pages/sec on an average and even shoots up to as high as 4000 pages/sec). The stolen pages/sec counter also shoots up to as much as 38300 pages/sec ... these correspond to the times when the server sees a lot of network activity .... around 7 MBps hitting up to as much as 20 MBps. The PLE during this time drops to less than 100.
However, here is the part that I can't explain ... the server memory stays up at 6.9GB consistently during this time (130 MB physical).
1) Is it that it has hit the max. limit on memory when the above happens and has to start paging to support the excess data being brought in?
2) Why would the server freeze? .... is it because sqlservr.exe is eating up too much without leaving a lot for the system and other apps?
3) Or is it the case that sql is being swapped out completely? (because,stolen pages * 4K = 38K*4K )
4) Is there a way to say which are the most expensive queries being issued? like which ones generate the swapping? I get the feeling, there are a lot of full table scans going on ....
My narration of the problem probably makes it clear that I am a newbie ... which is why any help you could provide on the subject would be really appreciated!
Thanks!
May 27, 2008 at 7:30 pm
also, if you buy into the idea that it is being swapped out completely (difficult though since it stays at 6GB) ... would setting a min server memory limit help?
vice versa, if the max server memory being not set is causing an issue, leading to the "freeze" should that be set?
let me know if any section is not clear
May 27, 2008 at 8:06 pm
How did you know there is no other application running against your SQL Server?
You may try sp_who2 active or SELECT * FROM master..sysprocesses to check activities on your SQL Server.
You may try to use, DBCC MEMORYSTATUS to check memory allocation.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply