SQL Server Performance Forum – Threads Archive

Huge Memory Problems

We have a multi-threaded application that is connecting to Sql 2005. The problem is SQL Memory (looking through Task manager) grows and grows and grows. To the point that 1 of 2 things happen. 1. Our Application Locks up.
2. SQL Server 2005 Locks up. When either of these happen we just shut down and restart the application that locked up and everything is fine for awhile. SOOO,, we are not sure exactly if the problem is our application or SQL. What I’m looking for from this forum is a place to start. Can you point me to some documents that talk about or explain in detail how SQL uses memory, releases memory? Thanks in advance for any help or feed back.
BOB PS. I’ve tried to do a search on SQL Memory Issues in this forum but I keep getting a time out error.

Is your server is dedicated sql server?
How much memory the server has and how much allocated to sql? MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Yes the server itself is a dedicated sql server, but it is handling data for multiple applications. Only one of these applications is mine. The Server has 8gb of Ram and 2gb is allocated for sql. Thanks for the reply. Bob

Task Manager will give you only high level information, its better to run PERFMON (SYSMON) during busy times and less busy times for more informaton and assessment on resource usage. http://sqlserver-qa.net/blogs/perft…t-memory-counters-what-you-need-to-check.aspx fyi for few points on memory. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Would tell me the downside/upside of forcing sql to release memeory by using the following commands: –Bump memory down to 200mbs
EXEC sp_configure ‘min server memory’, ‘0’
EXEC sp_configure ‘max server memory’, ‘200’
GO
RECONFIGURE WITH OVERRIDE
GO –Bump memory back up to 2gbs
EXEC sp_configure ‘min server memory’, ‘0’
EXEC sp_configure ‘max server memory’, ‘2000’
GO
RECONFIGURE WITH OVERRIDE
GO

If the current settings of memory is dynamic then leave ‘as it is’, without proper testing do not attempt any configuration changes.
Also consider:
-Grant the "Lock Pages in Memory" right to the SQL Server Service Account
-Set the Max Server Memory option in SQL Server to a value that leaves anywhere from 1-3GB left for the OS or even leaving dynamic memory settings is fine if the SQL server is dedicated server.
-Monitor SQL Server:Buffer Manager – Page Life Expectancy (higher is better)
-Monitor SQL Server:Buffer Manager – Buffer Cache Hit Ratio(higher is better)
-Monitor SQL Server:Memory Manager – Memory Grants Pending (lower is better)
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

There is an issue with Windows 2003 and SQL Server 2005 on 64-bit where Windows will flush SQL Memory down to the minium – see KB 918483. One of the workarounds is locking SQL in memory, however, this reportedly only works for Enterprise edition. Contrary to BOL, Standard edition ignores this setting – so….if you’re on Standard edition (like I am) and experience this problem, you have limited options. I have a dedicated server with a single instance of SQL 2005 and 16 Gb memory. SQL working set rarely exceeds 10 Gb before it is flushed down to minimum – when the system is busy, this happens every 15 minutes or so – huge PITA! Event log shows ongoing Event 17890….

Standard edition has that drawback in this case, we have reported back to MS documentation team to bemore clear over that KBA too. useful information fromhttp://blogs.technet.com/askperf/archive/2007/05/18/sql-and-the-working-set.aspx blog in this case. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Happy Again!! My problem was on an HP box where there is also an apparent iLO bug/compatibility problem that exacerbates or triggers the memory trimming. Between disabling the iLO adapter and installing Windows Server 2003 SP2 (remote terminal issues with memory trimming) – we now have a server that is using all of its memory and expending resources on query execution instead of hammering itself constantly paging memory unnecessarily!!

Appreciate your feedback that is specific to the hardware vendor in this case, as you said this may not be universal problem but having such HP hardware better to check for such reference. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

]]>