February 29, 2008 at 1:11 pm
Hello,
I'm so sorry for not spending more time researching this issue before posting. I'm sure there must be topics on it, but I have limited research time at the moment. And I would like to get the best info or pointers as soon as possible instead of wasting time down blind alleys.
Here is the situation:
We have a SQL 2005 server on 64-bit SQL and Windows. 8 GB RAM. Active-Passive cluster with space on a SAN. I have allocated 5120 MB minimum memory and 6144 MB maximum memory for SQL in the memory options. Because it is 64-bit (as I understand it) we have left the AWE checkbox unchecked.
Today we had a problem where suddenly we got paged because the web site was down. When I checked SQL, there were a few database blocks. Blocks have fortunately been rare since we switched to this more powerful server. However, usually killing the blocking process resolves this issue. Today it did not. Even though we cleared the blocks, the server was really slow, sp_who2 took about 30-40 seconds to complete.
Someone looking at the problem with me suggested to check Task Manager. I did, and he noticed that the PF Usage was at 6 GB. That looked high to him, although he said it was hard to say because (my fault) I did not have a baseline value for this statistic.
Finally, we decided to stop and restart (Take Offline/Bring Online) the SQL Server resource on the cluster. And that fixed the performance problem right away.
What seems odd to me (although maybe it isn't to those who know more) is that the other SQL stats appeared fine - pages per sec were avg 6 per sec, buffer cache hit ratio was 99.8, etc. It just seemed like something (perhaps memory usage) spiked crazily and froze the system. [CORRECTION - These values were OK after the restart, but after the PF Usage rose to about 6 GB again. I don't know for sure what the pages per sec, buffer cache hit ratio etc. were just before the outage.]
My questions are:
1. How can I tell what SQL Server is using its memory for? Is there any process in particular that is gobbling up a lot more memory than the other processes? What is available in Perfmon or Profiler to help find this info?
2. How can I determine whether 6 GB of PF Usage is high or not? What should I take into account?
3. I happened to be copying a backup file (11 GB) off of the server when this problem surfaced. I was copying from a different drive than the OS drive or the SQL drive, but is it possible that this copy action caused the outage?
4. Nothing obviously wrong turned up in the Windows event logs, but is there anything in particular I should look for?
Thanks in advance for your help! Let me know if anyone needs more info to help diagnose the problem.
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
March 2, 2008 at 1:02 pm
webrunner (2/29/2008)
3. I happened to be copying a backup file (11 GB) off of the server when this problem surfaced. I was copying from a different drive than the OS drive or the SQL drive, but is it possible that this copy action caused the outage?
99% likely, that's your problem. Windows uses memory to copy files, plus the copy is putting heavy load on the disks and the network.
I had a similar issue couple years back. Server admin - " I'm just copying a 500GB file, it won't have any effect". We stop the copy, SQL performance instantly goes back to normal.
If you're running enterprise edition, check that SQL has permission to lock pages in memory (Local policy, the SQL service account needs rights to the "lock pages in memory" setting)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 3, 2008 at 5:58 am
this may help http://sqlblogcasts.com/blogs/grumpyolddba/archive/2008/01/06/configuring-windows-2003-x64-for-sql-server.aspx
x64 can be a bit tricky!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 3, 2008 at 7:46 am
Thanks for the replies!
I will read the link provided. Also, to Gail: We are running SQL 2005 Standard Edition. We have Lock Pages in Memory set for the SQL Server service account - is that OK to do in Standard Edition, or is is just for Enterprise Edition?
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
March 4, 2008 at 2:39 am
I think Standard Edition ignores the 'lock pages' possibilities. In any case, Windows is more than happy to rob SQL of memory if something higher up the Windows food chain wants it. Unhappily, the memory needed for a file copy is deemed more important than SQL Server, and Windows will take up to 50% of the server memory for a file copy. (Yes, that does mean on a 32 GB box copying a 20 GB file will steal 16 GB memory from anything else that is running!)
See Uwe Sieber's utility on how to limit system file cache memory: http://www.uwe-sieber.de/ntcacheset_e.html
Other links that may have some useful information are: http://msdn2.microsoft.com/en-us/library/aa965240.aspx
http://www.microsoft.com/technet/prodtechnol/windows2000serv/reskit/core/fnec_evl_acks.mspx?mfr=true
Also, when looking at how much memory is in use in Task Manager, the true figure is the 'Commit Charge (K) Total' PLUS the 'Physical Memory (K) System Cache' values. People often only look at the commit charge but tis does not give the full picture.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 4, 2008 at 7:56 am
DBCC MEMORYSTATUS will show you all kinds of information about internal memory useage.
there is a nasty bug in windows for large-file copies to slower drives. Also, if your box is HP there is a nasty iLO bug too that should be patched.
and yes, standard edition does ignore the lock pages in memory setting.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 4, 2008 at 10:04 am
Thanks again to everyone for their help. This information will take me a little while to review, and I have already begun reading it.
One thing I would like to be able to do is to provide a breakdown of what processes in SQL Server are using how much memory. For example, to tell whether certain specific queries are consuming large amounts of memory each time they are run, or whether lots of smaller queries are running that add up.
Is that possible to do in Performance Monitor or in SQL Profiler?
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
March 4, 2008 at 10:57 am
check out some of the sys.dm_exec... DMVs in BOL. also sys.dm_os_waiting_tasks to see if things are getting behind.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 1, 2008 at 6:45 am
TheSQLGuru- can you please provide any information on the HP ilo bug you referenced? We have been fighting overnight freezes/lock ups for the past two weeks. ilo was something that was activiated somewhat recently before this all started.
April 3, 2008 at 3:28 pm
http://www.grumpyolddba.co.uk/infrastructure/HP%20Lights%20Out.htm
there is no lock pages for std edition. Make sure you set min and max memory on the sql server. I've posted a series of posts about o/s and x64 config on my blog ( link from above or previous post )
there's also some rdl's you can use to examine running processes. The whole thig of x64 config is too big to post in a forum.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply