Which process and memory
-
How do I find which process/task is taking what amount of memory? We have about 4GB and SQL Server keeps on taking more and more memory and not releasing at all (1.5 GB so far). I would like to find which application or query is causing this problem.
Thanks.
-
SQL Server is designed to grab as much memory as it can and not release it. If you want to limit how much memory SQL Server can use, set you Max Server Memory (MB) setting in sp_configure to a lesser value; however, I recommend that you give SQL Server as much as you can spare. If this is a stand-alone DB server, why are you concerned about the memory not being released? If this is not a stand-alone DB server, you'll need to re-configure the memory setting to tell SQL Server what it's limit is.
-
To understand sql memory architecture...
SQL Server 2005 Books OnlineMemory ArchitectureMohammedU
Microsoft SQL Server MVP -
RAM,
Since this is a SQL 2005 forum, I assume you are using SQL 2k5. As such, you can use some of the Dynamic Management Views (DMV) to tell you this information.
The first is sys.dm_os_memory_objects. Just do a
select * from sys.dm_os_memory_objects
and this will tell you all the memory objects that are currently allocated by SQL Server. This is helpful in determining memory leaks if any. As John Rowan said, SQL will grab memory and not release it unless necessary.
You also asked about which object/query is taking up the most. You can also look at some of the DMVs such as sys.dm_exec_cached_plans which will tell you about cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.
Hope this helps to get you started.
SJ
-
swjohnson (3/19/2007)
RAM,
Since this is a SQL 2005 forum, I assume you are using SQL 2k5. As such, you can use some of the Dynamic Management Views (DMV) to tell you this information.
The first is <STRONG><FONT color=#ff1111>sys.dm_os_memory_objects</FONT></STRONG>. Just do a
<FONT color=#bb3333><FONT color=#3333bb>select * from sys.dm_os_memory_objects</FONT> </FONT>
and this will tell you all the memory objects that are currently allocated by SQL Server. This is helpful in determining memory leaks if any. As John Rowan said, SQL will grab memory and not release it unless necessary.
You also asked about which object/query is taking up the most. You can also look at some of the DMVs such as <STRONG><FONT color=#ff1111>sys.dm_exec_cached_plans</FONT></STRONG> which will tell you about cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.
Hope this helps to get you started.
SJ
Can someone pls provide an example of how to use sys.dm_os_memory_objects to probe memory leaks?
I suppose the view will need to be sampled at set intervals and the results plotted on a graph to be able to view any trends?
Any additional insight would be appreciated.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url] -
Please post new questions in a new thread. Thanks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
OK, will do
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Accepting all invitesViewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply