SQL Server 2005 consuming too much memory | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL Server 2005 consuming too much memory

We are seeing an issue where SQL Server is consuming a large amount of memory. The server has approx 16gb of RAM and over the course of a few days the memory will consitently be allocated. We haven’t seen the db hang – but haven’t let it get to that point either. This is the only thing running on this server. Just trying to figure out what might be causing this. Anyone have any ideas?
check out sysprocesses and see which queries are using more CPU. ***********************
Dinakar Nethi
***********************
http://weblogs.sqlteam.com/dinakar/
What is the service pack level on SQL?
http://sqlserver-qa.net/blogs/perftune/archive/2007/06/19/memory-issues.aspx &http://sqlserver-qa.net/blogs/perft…-may-result-in-a-performance-degradation.aspx Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.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.
Thanks for that info, I see the memusage info returned by sysprocesses. Currently there is no load on that database yet the memory is nearly taxed out. Why would SQL Server continue to hold on to all of that RAM despite nothing happening on the database?
That is by default and behaviour of SQL is like that, it will release only when Windows requires it. As long as you don’t have any performance issues then you need not worry. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.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.
quote:Originally posted by satya What is the service pack level on SQL?
http://sqlserver-qa.net/blogs/perftune/archive/2007/06/19/memory-issues.aspx &http://sqlserver-qa.net/blogs/perft…-may-result-in-a-performance-degradation.aspx Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.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.

This is an issue with a customer of ours – I know that they are not on SP2.
quote:Originally posted by satya That is by default and behaviour of SQL is like that, it will release only when Windows requires it. As long as you don’t have any performance issues then you need not worry. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.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.

The database hasn’t hung but we are seeing some connections timeout. I am trying to determine the cause and we continue to see a lot of RAM being used. Not sure if that is connected but it seemed peculiar to me.
Is this a a specific time , or when a specific process is running? AKTHAR
For every connection memory is will be consumed or even reserved, so in this case capturing the system usage with SYSMON counters will help a lot to assess. Refer to the blogs above for further investigations Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.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.
Use the following query to get the what is in your buffer pool… select count(*)as cached_pages_count, obj.name as objectname, ind.name as indexname, obj.index_id as indexid from sys.dm_os_buffer_descriptors as bd inner join ( select object_id as objectid, object_name(object_id) as name, index_id,allocation_unit_id from sys.allocation_units as au inner join sys.partitions as p on au.container_id = p.hobt_id and (au.type = 1 or au.type = 3) union all select object_id as objectid, object_name(object_id) as name, index_id,allocation_unit_id from sys.allocation_units as au inner join sys.partitions as p on au.container_id = p.partition_id and au.type = 2 ) as obj on bd.allocation_unit_id = obj.allocation_unit_id left outer join sys.indexes ind on obj.objectid = ind.object_id and obj.index_id = ind.index_id where bd.database_id = db_id() and bd.page_type in (‘data_page’, ‘index_page’) group by obj.name, ind.name, obj.index_id order by cached_pages_count desc
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

Hi,
quote:
The database hasn’t hung but we are seeing some connections timeout. I am trying to determine the cause and we continue to see a lot of RAM being used. Not sure if that is connected but it seemed peculiar to me.

There could be several reasons for time out, you have to analyze * If this timeout occurs when a particular application is running
* If timeout occurred in particular timeframe
* If their is network congestion in particular network segment
* If a particular query is taking more time/cpu, analyze estimated execution plan and actual execution plan
* If their is complex query that may eat up your memory and/or cpu
* going forward for analysis you may also use profiler trace HTH Hemantgiri S. Goswami
MS SQL Server MVP
————————-
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami http://hemantgirisgoswami.blogspot.com
http://forum.sqlknowledge.com/ Disclaimer: This post is provided as is, for the sake of knowledge sharing only.
What is the best way to determine if locking is the issue? I can see from the results of the sysprocesses and dm_os_workers queries that there might be some locking occurring. I see many LCK_M_IX, LCK_M_X, and LCK_M_U results returned. What is the best way to determine what processes could potentially be locking others, causing timeouts? Thanks
Use thishttp://sqlserver-qa.net/blogs/perft…g-list-for-the-objects-indexes-using-dmv.aspx blog and see whether you see any sessions are blocked or blocking other processes. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.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.
I found the following info regarding turning on tracing to capture deadlock info through SQL Profiler. dbcc traceon (1204, 3605, -1)
go
dbcc tracestatus(-1)
go Can there be any problems with the amount of information written to the error files? This is for a customer of mine and I don’t want to recommend they do something that could adversely affect their overall system. Also, how do you turn off that level of tracing? Does it automatically cease once SQL Profiler is turned off, or do I need to run a statement to turn off tracing? Many thanks.
T Kisling
In order to find the blocking using PROFILER or server side trace you need those traceflags. BOL states, that "Default trace provides troubleshooting assistance to database administrators by ensuring that they have the log data necessary to diagnose problems the first time they occur." Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.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.
quote:Originally posted by satya In order to find the blocking using PROFILER or server side trace you need those traceflags. BOL states, that "Default trace provides troubleshooting assistance to database administrators by ensuring that they have the log data necessary to diagnose problems the first time they occur." Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.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.
What does that mean? Does that imply that the amount of logging captured by default should be enough? Why does Technet then suggest using the trace levels above to research deadlocks? Thanks
In sql server 2005 Trace Flag 1222 introduced check BOL topic "Detecting and Ending Deadlocks" Trace Flag 1204 writes the information to sql error log…and you enable Trace Flag 1204 or 1222 I don’t think you need to run profiler too…
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

Don;t you think PROFILER will get you underlying slow running queries in this case. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.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.
Hi,
I would suggest to refer http://support.microsoft.com/kb/224453/ , also you may use PSSDIAG utility to resolve your issue.
]]>