Script to Monitor SQL Server Memory Usage

Script to Monitor SQL Server Memory Usage

Script to Monitor SQL Server Memory Usage

Introduction:

This post will takes you through the T-SQL Script to monitor SQL Server Memory Usage. In previous blog post we have explained the parameters involved in understanding sql server memory usage. There are total 7 scripts to monitor SQL Server Memory Usage.

  • Buffer Pool Usage
  • System Memory Information
  • SQL Server Process Memory Usage Information
  • Buffer Usage by Database
  • Object Wise Buffer Usage
  • Top 25 Costliest Stored Procedures – Logical Reads
  • Top Performance Counters

Script to Monitor SQL Server Memory Usage: Buffer Pool Usage

Results:

BPool_Committed_MB: Actual memory committed/used by the process (SQL Server).

BPool_Commit_Tgt_MB: Actual memory SQL Server tried to consume.

BPool_Visible_MB: Number of 8-KB buffers in the buffer pool that are directly accessible in the process virtual address space (SQL Server VAS).

Analysis:

BPool_Commit_Tgt_MB > BPool_Committed_MB: SQL Server Memory Manager tries to obtain additional memory

BPool_Commit_Tgt_MB < BPool_Committed_MB: SQL Server Memory Manager tries to shrink the amount of memory committed

If the value of BPool_Visible_MB is too low: We might receive out of memory errors or memory dump will be created.

 

Script to Monitor SQL Server Memory Usage: System Memory Information

Results:

total_physical_memory_mb: Actual Physical Memory installed in OS

available_physical_memory_mb: Available Physical Memory

total_page_file_mb: Pagefile size on OS

available_page_file_mb: Available page file size

Percentage_Used: Physical Memory Percentage used

system_memory_state_desc: Memory current Health status

Analysis:

available_physical_memory_mb: Should be some positive sign based on total physical memory

available_page_file_mb: Should be some positive sign based on your total page file

Percentage_Used: 100% for a long time indicates a memory pressure

system_memory_state_desc: should be Available physical memory is high / steady

 

Script to Monitor SQL Server Memory Usage: SQL Server Process Memory Usage

Results:

physical_memory_in_use: Indicates the process working set in KB, as reported by operating system, as well as tracked allocations by using large page APIs

locked_page_allocations: Specifies memory pages locked in memory

virtual_address_space_committed: Indicates the amount of reserved virtual address space that has been committed or mapped to physical pages.

available_commit_limit: Indicates the amount of memory that is available to be committed by the process (SQL server)

page_fault_count: Indicates the number of page faults that are incurred by the SQL Server process

Analysis:

physical_memory_in_use: We can’t figure out the exact amount of physical memory using by sqlservr.exe using task manager but this column showcase the actual amount of physical memory using by SQL Server.

locked_page_allocations: If this is > 0 means Locked Pages is enabled for SQL Server which is one of the best practice

available_commit_limit: This indciates the available amount of memory that can be committed by the process sqlservr.exe

page_fault_count: Pages fetching from the page file on the hard disk instead of from physical memory. Consistently high number of hard faults per second represents Memory pressure.

 

Script to Monitor SQL Server Memory Usage: Database Wise Buffer Usage

Results:

db_name: Name of the database in the given SQL server Instance

db_buffer_pages: Total number of corresponding database pages that are in buffer pool

db_buffer_Used_MB: Database wise Buffer size used in MB

db_buffer_Free_MB: Database wise Buffer Size Free (sum of free space on all pages) in MB.

db_buffer_percent: Database wise percentage of Buffer Pool usage

Analysis:

We can quickly find out the top databases which are consuming more Memory / Buffer Pool from the given SQL server Instance

 

Script to Monitor SQL Server Memory Usage: Object Wise Buffer Usage

Results:

Object: Name of the Object

Type: Type of the object Ex: USER_TABLE

Index: Name of the Index

Index_Type: Type of the Index “Clustered / Non Clustered / HEAP” etc

buffer_pages: Object wise number of pages is in buffer pool

buffer_mb: Object wise buffer usage in MB

Analysis:

From the previous script we can get the top databases using memory. This script helps you out in finding the top objects that are using the buffer pool. Top objects will tell you the objects which are using the major portion of the buffer pool.If you find anything suspicious then you can dig into it.

Script to Monitor SQL Server Memory Usage: Top 25 Costliest Stored Procedures by Logical Reads

Results:

SP Name: Stored Procedure Name

TotalLogicalReads: Total Number of Logical Reads since this stored procedure was last compiled

AvgLogicalReads: Average Number of Logical Reads since this stored procedure was last compiled

execution_count: Number of Times SP got executed since it was compiled

total_elapsed_time: Total elapsed time for this proc since last time compiled

avg_elapsed_time: Average elapsed time

cached_time: Time at which the stored procedure was added to the cache.

Analysis:

  • This helps you find the most expensive cached stored procedures from a memory perspective
  • You should look at this if you see signs of memory pressure
  • More number of logical reads means you need to check execution plan to find the bottleneck

 

Script to Monitor SQL Server Memory Usage: Top Performance Counters – Memory

Results:

Total Server Memory: Shows how much memory SQL Server is using. The primary use of SQL Server’s memory is for the buffer pool, but some memory is also used for storing query plans and keeping track of user process information.

Target Server Memory: This value shows how much memory SQL Server attempts to acquire. If you haven’t configured a max server memory value for SQL Server, the target amount of memory will be about 5MB less than the total available system memory.

Connection Memory (GB): The Connection Memory specifies the total amount of dynamic memory the server is using for maintaining connections

Lock Memory (GB): Shows the total amount of memory the server is using for locks

SQL Cache Memory: Total memory reserved for dynamic SQL statements.

Optimizer Memory: Memory reserved for query optimization.

Granted Workspace Memory: Total amount of memory currently granted to executing processes such as hash, sort, bulk copy, and index creation operations.

Cursor memory usage: Memory using for cursors

Free pages: Amount of free space in pages which are commited but not currently using by SQL Server

Reserved Pages: Shows the number of buffer pool reserved pages.

Stolen pages (MB): Memory used by SQL Server but not for Database pages.It is used for sorting or hashing operations, or “as a generic memory store for allocations to store internal data structures such as locks, transaction context, and connection information.

Cache Pages: Number of 8KB pages in cache.

Page life expectancy: Average how long each data page is staying in buffer cache before being flushed out to make room for other pages

Free list stalls / sec: Number of times a request for a “free” page had to wait for one to become available.

Checkpoint Pages/sec: Checkpoint Pages/sec shows the number of pages that are moved from buffer to disk per second during a checkpoint process

Lazy writes / sec: How many times per second lazy writer has to flush dirty pages out of the buffer cache instead of waiting on a checkpoint.

Memory Grants Outstanding: Number of processes that have successfully acquired workspace memory grant.

Memory Grants Pending: Number of processes waiting on a workspace memory grant.

process_physical_memory_low: Process is responding to low physical memory notification

process_virtual_memory_low: Indicates that low virtual memory condition has been detected

Min Server Memory: Minimum amount of memory SQL Server should acquire

Max Server Memory: Maximum memory that SQL Server can acquire from OS

Buffer cache hit ratio: Percentage of pages that were found in the buffer pool without having to incur a read from disk.

Analysis:

Total Server Memory is almost same as Target Server Memory: Good Health

Total Server Memory is much smaller than Target Server Memory: There is a Memory Pressure or Max Server Memory is set to too low.

Connection Memory: When high, check the number of user connections and make sure it’s under expected value as per your business

Optimizer Memory: Ideally, the value should remain relatively static. If this isn’t the case you might be using dynamic SQL execution excessively.

Higher the value for Stolen Pages: Find the costly queries / procs and tune them

Higher the value for Checkpoint Pages/sec: Problem with I/O, Do not depend on Automatic Checkpoints and use In-direct checkpoints.

Page life expectancy: Usually 300 to 400 sec for each 4 GB of memory. Lesser the value means memory pressure

Free list stalls / sec: High value indicates that the server could use additional memory.

Memory Grants Outstanding: Higher value indicates peak user activity

Memory Grants Pending: Higher value indicates SQL Server need more memory

process_physical_memory_low & process_virtual_memory_low: Both are equals to 0 means no internal memory pressure

Min Server Memory: If it is 0 means default value didnt get changed, it’ll always be better to have a minimum amount of memory allocated to SQL Server

Max Server Memory: If it is default to 2147483647, change the value with the correct amount of memory that you can allow SQL Server to utilize.

Buffer cache hit ratio: This ratio should be in between 95 and 100. Lesser value indicates memory pressure

Script to Monitor SQL Server Memory Usage: DBCC MEMORYSTATUS

Finally DBCC MemoryStatus:

  • It gives as much as memory usage information based on object wise / component wise.
  • First table gives us the complete details of server and process memory usage details and memory alert indicators.
  • We can also get memory usage by buffer cache, Service Broker, Temp tables, Procedure Cache, Full Text, XML, Memory Pool Manager, Audit Buffer, SQLCLR, Optimizer, SQLUtilities, Connection Pool etc.

Summary:

These Scripts will help you in understanding the current memory usage by SQL Server. To maintain a healthy database management system:

  • Monitor the system for few business days in peak hours and fix the baselines
  • Identify the correct required configurations for your database server and make the required changes
  • Identify top 10 queries / procedures based on Memory and CPU usage
  • Fine tune these top 10 queries / procedures

Note:

These scripts are tested on SQL Server 2008, 2008 R2, 2012 and 2014. As we always suggests please test these scripts on Dev/Test environment before using them on production systems.

References:

Would like to thank famous MVPs / MCM / bloggers (Glenn Berry, Brent Ozar, Jonathan Kehayias, John Sansom) for the tremendous explanation on sql server internals. Their articles are very informative and helpful in understanding SQL Server internals.

Also Check:

SQL Server Memory Usage

SQL Server CPU Utilization

Posted in Interview Q&A, Performance Tuning, SQL Scripts, SQL Server DBA | Tagged , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , | 4 Comments

Leave a Reply

4 Comments on "Script to Monitor SQL Server Memory Usage"

Notify of
avatar
Sort by:   newest | oldest | most voted
Narendra N
Guest

can you please update SQL Server clustering scenarios….!!!

JemimaPaul
Guest

HI

Thanks for your script to find out overall memory usage of a server in percentage.I also need t sql script to find out overall CPU usage of a server in percentage.Could you please help me in this as well

wpDiscuz