SQL server memory usage

Question

SQL server memory usage

the_fenix

Right there with Babe

Points: 789
More actions
May 4, 2010 at 1:11 pm

#221163

Hi,
We released a new version of a system and since then the SQL server is consuming much more memory. We have a previous version in production (different SQL Server machine) that comsumes a stable amount of memory but for the new version, the memory usage just grows and grows until it reaches the limit and remains there. At some points the application stop answering and I would say that may be related with this.
How can monitor efficiently the memoty usage? I'd like to understand why the memory usage grows so much and so fast in my new version while not happening on the previous version.
Previous version using 42.5% of the available memory.
New version using 87.5% of the available memory.
Thanks!

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

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

GregoryF SSCertifiable Points: 6296 More actions · Answer 1

Was/Is "Max Memory" configured?

You have to be specific when asking questions, no bod know why two servers use different memory amounts when you have told us nothing of what version, database size, total memory, OLTP/DW, etc... Please provide some more specifics.

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

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

I generally agree with GregoryF, you haven't provided us much..

How is the new app different than the old?

Also to detect memory problems I have this:

http://www.sqlservercentral.com/Forums/Topic909689-146-1.aspx

You should confirm a memory issue by examining the following counters:

SQLServer:Buffer Manager - Page Life Expectancy

300 seconds or less may be indicative of low memory

SQLServer:Buffer Manager - Buffer cache hit ratio

Less than 98 percent of a hit ratio may indicate insufficient memory

SQLServer:Buffer Manager- Stolen Pages

High # relative to the total target pages (DBCC MEMORYSTATUS)

SQLServer:Buffer Manager- Memory Grants Pending

Zero and close to zero is best because then the server isn't queuing for memory.

SQLServer:Buffer Manager- Checkpoint pages/sec

More frequent checkpoints indicates low memory. If you see a high rate of checkpoints per second compared with normal (do you have a normal baseline?) then this is another indication of low memory

SQLServer:Buffer Manager- Lazy writes/sec

Zero and close to zero is best. If you're seeing this > 20 per second then the memory buffer pool isn't large enough

So you need to validate a memory pressure situation using all of these counters and then you'll better be able to determine whether or not you're under a significant memory pressure.

CEWII

the_fenix Right there with Babe Points: 789 More actions · Answer 3

GregoryF,Elliott W. Thanks a lot!

I was looking for a general advice about how to troubleshoot memory problems on SQL SERVER 2005. At this point I'm not sure about the differences between versions causing the problem and which information could be relevant for you. I apologize for the lack of information.

I'll be checking the information provided by Elliott and if need more help I'll be posting as much information as I can.

Thanks again guys!

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

I guess when I was asking about the versions I was asking about changes to the back-end. Were there any processes that were re-architected? Were there new processes added? Was there any major re-writes? I'm looking for big changes, because without knowing a LOT more that is where we are going to have to start.

CEWII

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Give a qualified professional a few hours access to your system for a performance review. I think you would be surprised what would be found - and you could be mentored on how to monitor things more effectively yourself. Win-win-win-win!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

sdorris-1150234 SSC-Addicted Points: 457 More actions · Answer 6

Since you mentioned "new system"; from a development point of view, you might take a look at any changes in SQL stored procedures for the application. For instance: are any stored procedures now using OPENXML that were not using this technique before? The system stored procedure - sp_xml_preparedocument - uses a lot of memory.

Not knowing a lot of details, I would concur with TheSQLGuru.