T-SQL – Finding Memory Utilization Information

T-SQL – Finding Memory Utilization Information

Memory is one the most important factor affecting SQL Server performance.

As an administrator, you should be monitoring the memory regularly. When Microsoft SQL Server runs out of memory, it will use virtual memory: i.e. disk paging, which will grind SQL Server to a halt.

You can keep track of the memory with dbcc memorystatus command.

Following is the simple syntax to use it:

DBCC MEMORYSTATUS


Note: You will get multiple outputs with different headings, these are explained below.

  • Memory Manager: shows overall memory consumption by SQL Server
  • Memory node Id: usage for each memory node: summary of memory usage
  • MEMORYCLERK_SQLGENERAL: aggregate for each memory node: aggregate memory
  • Buffer distribution: the distribution of 8-kilobyte (KB) buffers in the buffer pool
  • Buffer Counts: Buffer pool details from sys.dm_os_buffer_descriptors
  • Procedure cache: Size and type
  • Global memory objects: size of the global memory objects
  • Query memory objects: snapshot of the query memory usage: the workspace memory
  • Optimization Queue: summary of the users who are trying to optimize queries at the same time
  • MEMORYBROKER_FOR_CACHE: control cached memory, stolen memory, and reserved memory
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s