June 3, 2010 at 6:24 am
Hi,
Can anyone help me here? Trying to figure out a solution...
We have a SQL 2005 64bit enterprise running on an ESX Virtual server (windows 2003). We have 2 big tasks revolving around 29 million records running on this server. The performance was getting worse and worse and eventually grinding to a halt (the process taking 24 hours + when should be more like 5).
Doing some checks in perfmon I discovered that the System Cache Resident Bytes counter was expanding through the roof, leaving the machine's available memory at just 24mb, and the SQL Server that should be using 5gb was using just 200mb!!!
Now the thing is I cannot work out what is expanding the System Cache so rapidly. Could it be Dirty Pages from the SQL Process itself?
I am presently using the third party tool "Cacheset" from Microsoft's site to clear the cache every 5 minutes until this huge task is complete. At which point I could turn LargeSystemCache off as I see some settings have not been made correctly on this box (system cache given priority over Programs etc). BUT I want to work out why the System Cache is expanding so much in the first place. Any ideas?
I have not set Lock Pages in memory on this box as I did read of possible issues with ESX server. I realise this should solve the issue too.
Any advice and tips would be great. Thanks!
:hehe:
June 3, 2010 at 7:25 am
You really should run PerfMon and a server side trace to determine at what point the cache is eating the memory and to see if it's even related to SQL Server.
What other applications (if any) are running on the box?
Does the problem only occur during these two particular queries? (You may not notice if the other queries are much less intensive, but PerfMon will tell you.)
June 3, 2010 at 7:51 am
Thanks for the advice. I need a bit more help though - If I run a server side trace how will I be able to correlate the system cache on perfmon with x on profiler? My mind is running on empty after my day so far!
June 3, 2010 at 8:03 am
You have to make sure both are running at the same time. When both are done, open up Profiler and import the server side trace. Then go to File -> Import Performance Data and import the PerfMon file.
June 3, 2010 at 8:31 am
Thanks. Well typically the massive bursts in System Cache have now subsided with the server side trace and perfmon in place even thought he big job is still chugging along (at good speed).
Just a shame there isn't a solid "SQL Does\Doesn't use the system cache for x\y" document\books online article somewhere.
June 3, 2010 at 8:51 am
Just keep watching it. Also, write down what times of day you're seeing this stuff happen. Maybe it's happening at the same time every day and that'll help you narrow down what else is going on (server backups, big print jobs, network slowdowns, etc.)
June 3, 2010 at 9:28 am
Culprit discovered...One of the overnight backup processes for that server overran and finished at the same time the system cache stopped filling up. So there was the offender!
I am going to turn off the large system cache option - Seems my best option...unless I lock pages in mem but I'm not sure on that still (being a production server).
June 3, 2010 at 12:40 pm
RE: Lock Pages in Memory. We have it set on our prod server and it hasn't caused any problems. I've never heard of the situation you've referenced, though.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy