Sql server doesnt release memory.

Question

Post reply

Sql server doesnt release memory.

joshy mathew

Old Hand

Points: 361
More actions
September 29, 2011 at 4:38 am

#244607

hi
How can i release the memory used by sql server.
When more and more users are logged in, performance is getting down and finally the application hangs
Reason is cpu usage of sql server becomes 99%
How can i sort out this issue
Jos

Viewing 5 posts - 1 through 4 (of 4 total)

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

Perry Whittle SSC Guru Points: 233792 More actions · Answer 1

which process is taking 99% of the CPU?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

No, SQL doesn't release memory. That's how it's designed (and it's documented that way)

You need to tell SQL how much memory on the server it is allowed to have (max server memory setting) and you need to ensure that enough it left for the OS.

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

But wait, if your CPU is maxing out, what makes you think it's memory pressure? I can see I/O issues from memory pressure as more and more stuff is cached on disk, but not usually CPU.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

InvisibleCat Default port Points: 1496 More actions · Answer 4

Grant Fritchey (9/29/2011)
But wait, if your CPU is maxing out, what makes you think it's memory pressure? I can see I/O issues from memory pressure as more and more stuff is cached on disk, but not usually CPU.

I would agree with Grant, I've just been resolving a memory issue on one of our servers, it had a large impact on the I/O, but the impact on the CPU was not signifant.

I would check the memory using the standard perf mon counters or DMV's, check the page cache hit ratio, page life expectancy etc.

Also whilst you there you could have a look at the batches/sec and compilations, if SQL is receiving a large number of ad-hoc queries (or alike) this could be forcing the CPU up I think.

Nic