SQL 2005 SP3 crashes :(

Question

SQL 2005 SP3 crashes :(

anish_ns

SSCommitted

Points: 1705
More actions
September 24, 2010 at 10:20 am

#140827

Hello All
I have a sufficiently large server with 72GB RAM and 2 x 8 core "Nehalem" With windows 2003 x64 R2 (SP2) and SQL 2005 Enterprise SP3.
For some reason SQL crashes after a while, it uses up all the memory available and seems to get kinda stuck and eventually crashes. There doesn't seem to be anything apparent in the server logs either. The only entry I see before the crash is:
Message
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 144896, committed (KB): 69833036, memory utilization: 0%.
I also get Virtual Memory (Page File errors) on the windows host.
So, my question is what is the recommended page file size for a server like this? Should the system be paging at all....considering that it has plenty of RAM available. If I let Windows automatically manage my pagefile it happily takes up all available space on the C:\
For now I have limited SQL server to use only 65GB of available memory. Any recommendations on what I should do with the paging? and should I be using AWE to allocate memory for SQL process.
Thanks for your help.
Anish

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Grant the SQL service account 'Lock pages in memory' and make sure that you have max memory configured properly. With 72GB of memory in the server, gut feel is around 64GB for SQL's max memory. Less if there are other apps on the server or you see the OS under memory pressure

You do not want SQL using the page file at all. SQL uses memory to avoid having to fetch data off slow disks. Using disk that's pretending to be memory as cache is obviously counter-productive.

You said the OS is x64. Can I assume that SQL is as well? If so, no need for AWE, it's only needed by x86 processes.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

anish_ns SSCommitted Points: 1705 More actions · Answer 2

Thanks Gail!

I subsequently found the knowledge base from microsoft:

http://support.microsoft.com/kb/918483

I have also set the max server memory for SQL server to be 64GB. SQL is also x64 and we are also running Analytical Services on the same server.

After applying the changes, the server seems to be behaving better but, we will find out for sure in the next couple of days. Do I have to do anything special as far as Virtual Memory settings are concerned in the Windows 2003 OS? or leave it as default "System Managed Size"?

Once again thanks for your help.

Regards

Anish

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

anish_ns (9/24/2010)
I have also set the max server memory for SQL server to be 64GB. SQL is also x64 and we are also running Analytical Services on the same server.

You're going to have to drop that max memory lower to leave memory for Analysis services. I don't offhand know how much memory SSAS likes. Do some monitoring and see. I would advise setting SQL's max memory so that SQL's memory usage + SSAS's memory usage does not exceed 64 GB (if at all possible)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

skrilla99 SSCertifiable Points: 5795 More actions · Answer 4

Hi Anish,

Is it safe to assume there is only one instance on this server?

Does the problem go away with max memory set to 65 GB?

(Edit --> Oops! A bit late with those questions... :-D)

I think the page file only needs to be >= physical RAM if you want to be able to get a memory dump if (when) the system crashes. Otherwise, I think it is safe to use a smaller amount.

Sincerely,

Dan B

skrilla99 SSCertifiable Points: 5795 More actions · Answer 5

Gail,

For larger memory machines, do you stick with a percentage of RAM as max or do you like to leave a static amount? 10% or 10 GB, etc...

Thanks,

Dan B

anish_ns SSCommitted Points: 1705 More actions · Answer 6

@Gail: I will perform the monitoring as you suggest...

@dan-2: Yes, there is only one instance. After Gail's recommendations, I have set the max memory to be 64GB and have applied the "lock pages in memory" in the Group Policy. The server seems to be behaving now but, I will keep you posted if anything untoward happens in the next 24 hours.

I don't think there is a problem with paging on Win 2K8 but, since, our physical server does not officially support Windows 2008, we have to stick with Win2K3

Cheers!

Anish