December 26, 2011 at 4:34 pm
From the MSDN article for sys.dm_os_memory_objects http://msdn.microsoft.com/en-us/library/ms179875.aspx:
Returns memory objects that are currently allocated by SQL Server. sys.dm_os_memory_objects is primarily used to analyze memory use and to identify possible memory leaks.
Beyond the blanket statement above nothing more is said about how to use the DMV for identifying memory leaks. Can anyone provide a real-world example of how they have used sys.dm_os_memory_objects to identify a memory leak?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 27, 2011 at 12:04 am
Determine Whether a Memory Leak Exists
1. Launch the Task Manager by pressing "Ctrl+Alt+Del" simultaneously.
2. Check the memory usage of SQL Server. To check the memory usage, click the "Processes" tab on the Task Manager scroll down until "SQL Server" is visible in the list of processes. The number listed to the right of "SQL Server" is its memory consumption. Write down this number.
3. Check the system memory. To check system memory, click the "Performance" tab on the Task Manager. Under "Physical Memory" there are three numbers. The first is the total memory, the second is the available memory. Write down both of these numbers.
4. Calculate the percentage of system memory used by SQL Server. Divide the SQL Server memory usage by the total memory to determine the percentage of the system's memory that SQL Server is using. If this number is higher than you would like, continue to set limits on the SQL Server memory. The only time that it is essential to set SQL Server memory limits is if the SQL Server is using over 95 percent of system resources.
For More:
http://www.ehow.com/how_7584174_troubleshoot-sql-server-memory-leak.html
December 27, 2011 at 3:42 am
Dev (12/27/2011)
2. Check the memory usage of SQL Server. To check the memory usage, click the "Processes" tab on the Task Manager scroll down until "SQL Server" is visible in the list of processes. The number listed to the right of "SQL Server" is its memory consumption. Write down this number.
Task manager is an appaulingly poor tool to use to monitor SQL's memory as it will very often show completely incorrect values. Perfmon, process manager or SQL's internal DMVs, but not task manager. (Jonathan Kehayias's book, chapter 4)
Under "Physical Memory" there are three numbers. The first is the total memory, the second is the available memory. Write down both of these numbers.
Depends on the OS. Mine has 4 - Total, Cached, Available, Free (and the meaning of available and free is not obvious, see Mark Russinovich's memory presentations)
The only time that it is essential to set SQL Server memory limits is if the SQL Server is using over 95 percent of system resources.
It is always essential to set SQL's max server memory to a sensible value on servers with more than minimal amounts of memory, doubly so if the server is not dedicated to SQL. Triply so if locked pages is set.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 27, 2011 at 4:08 am
I suspect what you'd be looking for in sys.dm_os_memory_objects is entries that only increase their page counts, never decrease. It's difficult though, as many of the caches do have that behaviour by design, they increase in memory usage and then remain static once they've got the most they can.
The only place I've heard of memory leaks in SQL Server is actually in 3rd party, in-process drivers that leaked memory all over the non-paged area (and that's bloody hard to diagnose, because AFAIK, they don't get entries in the various memory-related DMVs)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 27, 2011 at 9:40 am
Thanks, that helps. I have heard memory leaks in SQL Server itself are extremely rare at this point, however paranoia prevails at times so I am investigating. The consensus of some of the business users that write SQL for this instance is that we need to reboot the server periodically due to the possibility of a memory leak, and they are convinced the server runs "much faster" after the reboot. Meanwhile I just found a query in an Agent job doing over a millions reads every 30 minutes during the day, and the 32-bit box only has 4GB of RAM so every fourth or fifth time the job runs the buffer pool essentially gets flushed and the PLE goes to 0. I am abandoning my search for a memory leak and am going to focus on the queries running in that job.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 27, 2011 at 10:45 am
opc.three (12/27/2011)
The consensus of some of the business users that write SQL for this instance is that we need to reboot the server periodically due to the possibility of a memory leak, and they are convinced the server runs "much faster" after the reboot.
May I suggest doing some performance benchmarking and pulling some hard numbers to confirm or refute that belief?
Meanwhile I just found a query in an Agent job doing over a millions reads every 30 minutes during the day, and the 32-bit box only has 4GB of RAM so every fourth or fifth time the job runs the buffer pool essentially gets flushed and the PLE goes to 0. I am abandoning my search for a memory leak and am going to focus on the queries running in that job.
Good idea. A little bit of optimisation can go a long way.
Any chance of additional memory for that server?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 27, 2011 at 11:29 am
I have had a perfmon counter log capturing several counters, one of which is Available Megabytes. I can show that the number remains steady through the PLE drops and continued, normal, system activity over a long period of time. Do you think that is a proper number to present as evidence against the existence of a memory leak?
Re: adding memory...it's a possibility but not likely. I am in the process of setting up a 64-bit 2008R2 environment for these databases and the instance this one maps into will have 12GB RAM. The move is planned for March. I have also kicked around the idea of adding the /3GB switch to the boot.ini but am seeing mixed reviews about whether 32-bit Standard Edition on Server 2003 will benefit. Any insight into whether that would be helpful?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 27, 2011 at 12:57 pm
opc.three (12/27/2011)
I have had a perfmon counter log capturing several counters, one of which is Available Megabytes. I can show that the number remains steady through the PLE drops and continued, normal, system activity over a long period of time. Do you think that is a proper number to present as evidence against the existence of a memory leak?
Depending on who you're showing it to, you could potentially roll 5d20 and use that as evidence for or against a memory leak 😉 :hehe:
That would show there's no form of 'allocation leak' (my term, not a standard term) where an app keeps on allocating more and more and more memory. It won't prove that there isn't a leak within the process space (eg something's taking more and more buffer or non-buffer memory and leaving the other processes with less and less cache space, but that's highly, highly unlikely)
Given what you've said, it is probably that nasty scheduled query that's just displacing the buffer pool.
What I meant earlier was recording query performance statistics (duration and CPU mainly) over a period of time both before and after a restart to confirm whether the system really is faster after a restart
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 27, 2011 at 3:32 pm
I am confident I could actually pass it off too 😛
Thanks for the guidance. I am not going to dig into the possibility of an internal memory leak. I think this is a case of bad code striking once again. I can't wait to dig into the execution plans produced by my 1MMx reader 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 9, 2012 at 1:26 pm
An update on this: we disabled the job that was running the process doing 1.3MM reads every 30 minutes during the day since it was learned that it was a legacy "sanity check" process created >10 years ago and was no longer needed. In researching the issue I found something very interesting related to proc cache. I found an Agent job that was scheduled to run every 15 seconds during business hours that checked for spids blocking for more than 15 seconds. The Agent development team apparently did not get the memo about the value in using stored procedures because they issue adhoc updates to the job activity and history tables in msdb causing the proc cache to bloat significantly. The proc cache bloats from ~10MB to >900MB in only a few hours when the job runs. This is on a 32-bit system with 4GB RAM. You can see how this could cause problems. I added a job to clear the SQL Plans cache if it got over 200MB, but since I disabled the job the highest it has gotten is ~70MB.
I am planning to rewrite the job that checks for blocking using a loop and a WAITFOR command so the job never actually completes, preventing the constants msdb activity.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply