asked
SQL 2k5: How to determine memory usage for each job
Windows server 2003 SP1. SQL server 2005 SP3. We have a number of jobs executing under SQL Agent, around 30. Every 10 to 20 days, the SQL agent starts gobbling huge amount of memory, probably following an error. When it happens, the SQL Server begins showing slow response and denies new logins. Some jobs can not start. We search the culprit job but often wind up rebooting the server to clear everything. My question is: how can I determine the memory usage for each job running under the SQL Agent? Hopefully, I can then narrow our search and find the problem. Thanks Steve
You can't find the memory used specifically for jobs, but you can monitor the memory use by query and database. If you know which queries your jobs are running against which databases, you can easily correlate the memory use to the jobs. Glenn Berry has some useful memory queries (as do innumerable other SQL People and many fine monitoring tools): [
http://sqlserverperformance.wordpress.com/2010/10/08/sql-server-memory-related-queries/][1] [1]:
http://sqlserverperformance.wordpress.com/2010/10/08/sql-server-memory-related-queries/
Since some old questions have been popping up recently, here's one on using Profiler and Perfmon for monitoring jobs:
http://ask.sqlservercentral.com/questions/34862/performance-during-job-execution.html
If you have a test environment run each job in turn and see which one takes up all of the memory. If you don't try and correlate the memory usage to the job schedule to identify likely candidates. Once the memory has been used up is it ever released or do you restart the instance to drop it down again?. You could limit the amount of memory SQL Server can take if it causing the server to grind to a halt if it has gobbled up 99% of available memory. You could run a perfmon trace for a analysis period which would give you an indication of when the memory usage suddenly shoot up and tie this back to the job that commenced at the time. DM queries such as SELECT * FROM sys.dm_os_memory_clerks give and indication as to how SQL is using it's memory allocation. However it will only indicate how much is in various buffers and and caches at the time it is run. The best answer I can find on this site can be found here
http://www.sqlservercentral.com/Forums/Topic1215340-391-1.aspx.
14 People are following this question.
