monitoring memory usage - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

monitoring memory usage

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-11-15 : 03:41:19

what's the best way to monitor what procedures are using high memory as my memory usage is high on my sql server

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-15 : 04:03:33

http://msdn.microsoft.com/en-us/library/aa905152(v=sql.80).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-11-17 : 02:50:42

but how can I see what stored procedures are eating up my memory?

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-17 : 13:21:27

Check this..


SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads], 
qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count, 
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second], 
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count 
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_reads DESC;

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-11-18 : 01:43:21

thanks - that was helpful

I have a procedure that uses a lot of memory and runs a few hours at night - -but it appears it is still using memory by the day -- is it possible that something I am doing ties up memory and doesn't release it when it's finished running?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources