SQL Server Not Releasing Memory

Question

SQL Server Not Releasing Memory

HBhagat

Hall of Fame

Points: 3341
More actions
November 11, 2011 at 7:43 am

#251710

Hi Gial,
My Server details
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64
Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
I have few things what I am not able to configure.
I have 20 server. There are SQL servers from 2000 to 2008R2. Why I have a problem with the memory in only 1 server? Why this memory problem is not other servers?
After lot of looking into the problem, we found that a few steps in a job is causing the memory increase. But this job was working fine till last thursday, and then suddenly on Friday this started giving a problem. No changes were made in the steps, neither any changes were made in the procedure executed in the steps.
I have decreased the max memory set , but still the problem is there.
What should we do that it does not use all the memory, or how to avoid memory increase.
We have already done 3 reboot of the server to reduce the memory problem.
Please help.
Thanks,
Harsha

Viewing 15 posts - 1 through 15 (of 15 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

SQL Server will generally take as much memory as it needs (or the max allowed, whichever is less), and then will keep it. There are reasons for that.

Is there a reason it needs to give the memory back?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

HBhagat Hall of Fame Points: 3341 More actions · Answer 2

Hi GSquared,

I have to provide a necessary explanation why this server is having such issue. Why not other servers.

Also I am also trying to find why sql server is taking so much memory.

What is causing to take that memory.

Thanks,

Harsha

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Why do you think the memory is the problem??

Do you run anything clr or xml on that server? That takes ram outside the max settings and you can't really control that aside from recuding the max memory by a lot (possibly too much).

Tried something like this?

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2

Lowell SSC Guru Points: 323518 More actions · Answer 4

is the server witht eh memory problem one of many on the same virtual server? could it be that instances are eating up 100% of the available memory, and #20 is starved for resources because of the others ?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

HBhagat Hall of Fame Points: 3341 More actions · Answer 5

Hi,

I am trying to find out, why my 1 server is having problem with SQL server Memory allocation.

All the servers are fine.

No they are not starving for memory.

All are set to range from 24 to 31 GB .

I just want to know what is making my X server is using that much memory? There are no CLR or XML queries.

Thanks,

Harsha

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

How did you find out that you were having memory issues on that server?

HBhagat Hall of Fame Points: 3341 More actions · Answer 7

Hi,

The IT team came running to us..

Later on... we were keeping an eye on it.

We did a reboot on Thursday then on Monday..

There were no multiple logins i=on Monday. 1 job was running ... and it was of for Data warehouse work.

It has lot of cursors..

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Ninja's_RGR'us

SSC Guru

Points: 294069

November 11, 2011 at 9:15 am

#1406374

The IT came running to you BECAUSE?

HBhagat Hall of Fame Points: 3341 More actions · Answer 9

Its not this because..

I also want to know why this server is taking so much memory.

Why this server only is having this memory issue..

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

SQL_DBA_New (11/11/2011)
Its not this because..
I also want to know why this server is taking so much memory.
Why this server only is having this memory issue..

SQL takes memory because it needs it. Period, end of story.

If you're not troubleshooting a specific performance issue you're wasting your time and ours.

Lowell SSC Guru Points: 323518 More actions · Answer 11

Ninja's_RGR'us (11/11/2011)
SQL takes memory because it needs it and lives happily ever after. Period, end of story.

there. fixed it for you. 😛

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 12

I also want to know why this server is taking so much memory.
Why this server only is having this memory issue..

I am sorry but it won't be an easy exercise for you. You should make inventory of ALL Servers (CPU, RAM, DBs, OS, SQL Server configuration etc) and find what’s DIFFERENT here.

That might be your answer.

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

http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/ Chapter 4.

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Or this if you have 1 month to decypher it.

DBCC memorystatus()