Sql Server 2008 Memory Issue.

Question

Sql Server 2008 Memory Issue.

svbarve 2867

SSC-Addicted

Points: 443
More actions
August 11, 2011 at 8:50 am

#255381

Hi,
I am using SQL Server 2008 64 bit with Windows 2008 server (64 bit) as OS, we have Datawarehouse ETL jobs (SSIS) running every night. Everything was fine for about a year past; from last couple of weeks we are finding that the server does not respond after finishing the ETL's. i.e. After ETL executes the server does not allow any remote sessions until the SQL services are restarted, The server has 32 GB of RAM available, When I monitored the ETL I found the memory utilization goes high and occupies up to 29.5 GB of memory and after which it stops all the remote connections. This causes the next ETL run to fail with message “Insufficient system resources.”
I have also granted Lock page memory rights to service account, The Databases are well indexed and the SSIS jobs executes quite fast. I have run DBCC commands to check Torn pages but no such issues found. All the indexes are rebuild periodically so no chances for index fragmentation.
Any idea what's going on?
Any help will be appreciated.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

You might consider setting the MAX sql memory to something like 24-26GB and see if that resolves it. Keep in mind that if SQL does not have a max set it will take what it wants which can and will strangle things, usually SQL doesn't stop responding though..

Anything in the errorlog?

CEWII

svbarve 2867 SSC-Addicted Points: 443 More actions · Answer 2

Thanks Elliott,

You are right.. even when the server stops responding I can still run SQL scripts of any complexity and they do run. No idea why ETL jobs don't work, The reason i have not enabled AWE is because of 64 bit environment. But looks like now I have to...

Thanks anyway. I will keep the results posted..

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

You do NOT need AWE on a 64 bit SQL. The setting is completely ignored.

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

svbarve 2867 SSC-Addicted Points: 443 More actions · Answer 4

Thanks GilaMonster,

I have not enabled the AWE, Just configured 'Max server memory' to 24 GB. I am curious to see the outcome tomorrow.

Thanks.

BlueTiger Ten Centuries Points: 1396 More actions · Answer 5

SSIS using seperate memory with SQL Server database engine. If you configure the max memory in SQL Server service. It will be used by the SQL database engine. The rest of memory can be used by SSIS Service ( SQL Agent too).

BlueTiger Ten Centuries Points: 1396 More actions · Answer 6

My point is if you configure too much memory for SQL Database engine. You ssis service on the same server has to compete withe other services and OS , It can cause the Server no responding. You should limite the max memory to Database engine. I may wrong.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

BlueTiger (8/11/2011)
My point is if you configure too much memory for SQL Database engine. You ssis service on the same server has to compete withe other services and OS , It can cause the Server no responding. You should limite the max memory to Database engine. I may wrong.

This is correct, and my overall point above. SQL was not unavailable just that you didn't have enough memory to run the SSIS packages. Also SSIS and the SQL engine use separate memory. Setting MAX mem to 24GB should leave 8GB for the OS and SSIS, given that you were capping out at 29.5GB for SQL I'm gonna say that you should have perhaps 5.5GB available for SSIS now. Gila is absolutely correct AWE is not even considered on a 64-bit machine AND if it were SQL would have been using like 1.7GB of RAM. When SQL and the OS start to fight each other, you end up with thrashing and performance takes a dive.. Also, based on my understanding of Lock Pages in Memory, SQL won't give up memory it has already allocated, so I'm not sure the OS can MAKE it reduce usage which makes it hard for SSIS to run..

CEWII

svbarve 2867 SSC-Addicted Points: 443 More actions · Answer 8

Thanks All of you...

Looks like issue is resolved following are the settings I did on server and its working fine. I could remote to server without any issues. Only downside is I can notice about 10 GB of memory not getting used at all, but I feel monitoring the ETL process over couple of weeks could help me deriving exact figures for MAX SERVER MEMORY counter. But since the ETL performance has not been affected I can stay with these settings for at least a month till I complete the math.

The server settings done are as follows.

1.SQL Service account has a rights for Lock pages.

2.SQL MAX SERVER MEMORY is set to 20 GB.

3.No MIN SERVER MEMORY settings done yet.

When the ETL process executes it occupies total 22.8 GB including SQL and SSIS(DTExec) both. At the end when Reporting services executes the subscriptions additional 183 MB is occupied. I will still monitor the process for a week to conclude the status of the issue. but still i feel 90% issue is resolved.

Thanks...