I’m continuing a series on the Standard Reports you can find in SQL Server Management Studio (SSMS).
The Memory Consumption report shows information about how the Instance of SQL Server has partitioned out the memory on your system.
Memory is probably one of the most misunderstood parts of SQL Server. It has a lot to do with the way applications work in 32-bit, x64, and IA 64 memory spaces. I’ll give you the quick version of those here.
32-bit Architectures and Operating Systems: SQL Server can use the first 2GB of RAM for anything it needs to do, whether that means space for query plans and the like or buffers to hold data read-aheads. Unless you use a special switch (/3GB) in the operating system, this is all SQL Server uses by default. At 4GB, you can use the /PAE switch in the operating system and turn on AWE support in SQL Server to get more memory for editions that support it. Those are mostly read-ahead buffers, though.
For x64, where you’ve installed a 64-bit operating system, the memory space is “flat” for applications like SQL Server. That means that they can take from 0 to whatever you have on the box if you want, and all of it is used for everything SQL Server needs to do. That’s why x64 (or EMT64) is so useful for SQL Server.
For the IA64, it’s also a flat memory space.
With all that out of the way, it’s important to know how much of each component SQL Server is parsing out to memory. That will help you decide whether you should add RAM or change a query, or whether to add another CPU, for instance.
In the following graphs, I have as much information as I can hyperlinked out to other documentation in case you're not familiar with a particular term or concept.
This report has four main sections. The first is the general information about the memory grants and page life expectancy. Here are those definitions:
Memory Grants Outstanding: The total number of processes that have successfully acquired a workspace memory grant. Check the link to learn more about what a memory grant is.
Memory Grants Pending: Total number of processes waiting for a workspace memory grant.
Page life expectancy: The number of seconds a page will stay in the buffer pool unless it gets called again. Should be at least 300 seconds.
The next section of the report is a graph that shows the top memory consumptions. You can do a Windows Live search on those items to find out more about what has the most space in your memory.
The next graph shows how your “buffer” area is allocated. This is basically where data is stored, and you can read more about that here.
The final graph shows the memory changes over time, again dependant as some of the previous reports are, on the default trace.
Finally a chart at the bottom of the page shows the memory for each component.The headings are pretty self-descriptive, and the two parts that you want to care about are “reserved” and “committed”. The reserved memory is what SQL Server is asking for, and committed is what it is actually using. There’s a lot more here.
To find this report, open SSMS, right-click a Server, and then select “Reports” and then “Standard Reports”.This report takes a few moments to render.