Share via

Changes in dbcc memorystatus in Yukon

  • Article

You probably have used dbcc memory status command in SQL2000. In Yukon we have changed output of dbcc memorystatus to reflect new memory manager design, covered in my previous post. 

The first part of the output represents overall amount of memory consumed by SQLOS, SQL Server, memory manager 

Memory Manager                  KB
------------------------------ --------------------
VM Reserved                          overall amount of VAS reserved by SQL Server   
VM Committed                        overall amount of VAS committed by SQL Server
AWE Allocated                        overall amount of memory allocated using AWE mechanism
Reserved Memory                    overall amount of reserved memory, special memory used in emergency situations
Reserved Memory in Use          overall amount of reserved memory currently in use

The next part is summary per cpu node

Memory node Id = 0              KB
------------------------------ --------------------
VM Reserved                           amount of VAS allocated by this node
VM Committed                         amount of VAS committed by this node
AWE Allocated                         amount of memory allocated using AWE mechanism by this node
MultiPage Allocator                   amount of memory allocated through  mutli page allocator by this node
SinglePage Allocator                  amount of memory allocated through single page allocator, stolen from buffer Pool, by this node

Following information is summary output for a node grouped by a type of clerk. Please remember that there are several clerk types:

(5 rows affected)
MEMORYCLERK_SQLGENERAL (node 0)                                   KB
---------------------------------------------------------------- --------------------
 VM Reserved                                                                         amount of VAS reserved by this clerk on a node 0
 VM Committed                                                                       amount of VAS committed by this clerk on a node 0
 AWE Allocated                                                                       amount of memory allocated using AWE mechanism by this clerk on node 0
 SM Reserved                                                                          amount of VAS reserved by this clerk using shared memory, file mapping APIs, by the clerk on node 0
 SM Commited                                                                         amount of VAS committed by this clerk using shared memory, file mapping APIs, by this clerk on node 0 
 SinglePage Allocator                                                                amount of memory allocated through single page allocator, stolen from BP, by this clerk on node 0
 MultiPage Allocator                                                                  amount of memory allocated through multi page allocator by this clerk on node 0

Then dbcc memorystatus outputs a summary for a given clerk across all the nodes.

MEMORYCLERK_SQLGENERAL (node 0)                                   KB
---------------------------------------------------------------- --------------------
 VM Reserved                                                                         amount of VAS reserved by this clerk on all nodes
 VM Committed                                                                       amount of VAS committed by this clerk on all nodes
 AWE Allocated                                                                       amount of memory allocated using AWE mechanism by this clerk on all nodes
 SM Reserved                                                                          amount of VAS reserved by this clerk using shared memory, file mapping APIs, by the clerk on all nodes
 SM Commited                                                                         amount of VAS committed by this clerk using shared memory, file mapping APIs, by this clerk on all nodes 
 SinglePage Allocator                                                                amount of memory allocated through single page allocator, stolen from BP, by this clerk on all nodes
 MultiPage Allocator                                                                 amount of memory allocated through multi page allocator by this clerk on all nodes

The rest of information is similar to what described in https://support.microsoft.com/?id=271624

In most of the cases output from this command is not enough to figure out particular memory issue. However the  information from this command could be sufficient to understand memory distribution inside of SQL Server.  I usually use this command as a first step when debugging memory problems in SQL Server. For example if a server fails with VAS reservation error, having amount of VAS allocated is very important to be able to classify leak as internal or external for SQL Server.

Have a great day!

Comments

  • Anonymous
    July 27, 2005
    Does then Multi page allocator memory means MemToLeave allocation?
  • Anonymous
    January 24, 2006
    what is the equivalent in 2005 for dbcc memusage? this seems to be deprecated in this release.
  • Anonymous
    February 01, 2006
    Currently there is no substitution for the command. Could you tell me what problem are you trying to resolve?
  • Anonymous
    February 01, 2006
    Q: Does then Multi page allocator memory means MemToLeave allocation?
    A: Yes, multi page allocation comes outside VAS region controled by Buffer Pool
  • Anonymous
    August 08, 2006
    I like using the DBCC MEMUSAGE(names) command to see how the datapages are allocated to the individual objects such as what table has the most data pages in memory. Is there another way to get this information in SQL 2005?
  • Anonymous
    August 21, 2006
    In SQL Server 2005 you can leverage sys.dm_os_buffer_descriptors, sys.allocation_units and sys.partitions
    to build output similar to dbcc memusage. For more information see example on http://msdn2.microsoft.com/en-us/library/ms173442.aspx
    Let me know if you have more questions