SQL Server Memory Usage

SQL Server Memory Usage Related Interview Questions and Answers

Introduction:

This post takes you through “how to monitor SQL Server Memory usage”. Most of the clients I worked with had a common question “What is my SQL Server Memory Usage?” yah there are lot of ways to monitor SQL Server Memory Usage. Also if you are a senior database professional whether you might be an administrator, developer or architect you might be asked the question “How do you know SQL Server Memory usage?” One can design the best scalable and an optimized database system when he/she understands the RDBMS architecture. Here we’ll be discussing memory related parameters and how sql server uses memory. Before going through “Monitoring SQL Server Memory Usage” we should understand below things.

• Memory Types
• Top Performance Counters
• Memory Allocation to SQL Server
• Virtual Address Space
• Buffer Pool and Memory-To-Leave (MTL)
• Lock Pages in Memory
• Instant File Initialization
• Logical Reads & Physical Reads

Memory Types:

Physical Memory: The actual memory installed on mother board.

Virtual Memory: Total Physical Memory + Page File

Page File: When available memory can’t serve the coming requests it starts swapping pages to disk to the page file. The current page file size we can get from “sysdm.cpl” à Advanced à Performance Settings –> Advanced

Cached memory: It holds data or program code that has been fetched into memory during the current session but is no longer in use now.

Free memory: It represents RAM that does not contain any data or program code and is free for use immediately.

Working Set: Amount of memory currently in use for a process. Peak Working Set is the highest value recorded for the current instance of this process. Consistently Working Set < Min Server Memory and Max Server Memory means SQL Server is configured to use too much of memory.

Private Working Set: Amount of memory that is dedicated to that process and will not be given up for other programs to use.

Sharable Working Set: Shareable Working Set can be surrendered if physical RAM begins to run scarce

Commit Size: The total amount of virtual memory that a program has touched (committed) in the current session. Limit is Maximum Virtual Memory that means Physical RAM + Page File + Kernal Cache.

Hard faults / Page Faults: 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.

Memory Related Top Performance Counters:

Memory: Available Bytes:

High: Good Health

Low: Low Value Indicates Shortage of Memory)

Available Bytes counter indicates how many bytes of memory are currently available for use by processes.

Memory: Pages/sec:

High: Indicates excessive paging

Low: Good Health

Pages/sec counter indicates the number of pages that either were retrieved from disk due to hard page faults or written to disk to free space in the working set due to page faults.

SQL Server: Buffer Manager: Buffer Cache Hit Ratio:

High > 90%: Good Health

Low < 90%: More requests are getting data from physical disk instead of data cache

Minimum Server Memory: Minimum amount of memory which is initially allocated to SQL Server. Default value is 0.

Maximum Server Memory: Maximum Server Memory that sql server can use up to. Make sure you are having a proper statistics and future plan before making any changes. Default value is set to 2 Peta Bytes. To determine this value first we need to know the memory required for OS and memory required for any other applications / services. Maximum Server Memory = Total Physical Memory – (Memory Required for OS + Memory Required for Other Applications);

Memory Manager: Total Server Memory (KB):

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.

Memory Manager: Target Server Memory (KB):

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.

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.

Page Life Expectancy:

High: Good Health

Low: Memory Pressure or Max Server Memory is not allocated properly

Number of seconds a page is staying on buffer cache. Usually we do calculate based on the Memory allocated to SQL server Instance. For 4 GB ram the PLE is supposed to be 300 sec / 5 Min.

4 GB – (4/4) * 300 = 300 Sec = 5 Min

16 GB – (16 / 4) * 300 = 1200 Sec = 20 Min

32 GB – (32 / 4) * 300 = 2400 Sec = 40 Min

This is to set an estimated health benchmark for PLE, one can follow their own formula based on their environment and experience.

Interview Questions and Answers – SQL Server Memory Usage

Q. How SQL Server acquires and releases Memory to / from OS?

Ans:

When SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free memory. Maintaining this free memory prevents the operating system (OS) from paging. If less memory is free, SQL Server releases memory to the OS. If more memory is free, SQL Server may allocate more memory. SQL Server adds memory only when its workload requires more memory; a server at rest does not increase the size of its virtual address space.

Q. What is Virtual Address Space (VAS)?

Ans:

• A virtual address is a binary number in virtual memory that enables a process to use a location in primary storage (main memory) independently of other processes.
• This provides a layer of abstraction between an application and physical memory so that the operating system can choose the most efficient way to use physical memory across all the processes.
• For example, two different processes can both use the memory address 0xFFF because it’s a virtual address and each process has its own VAS with the same address range.
• The size of the virtual address space is determined largely by the CPU architecture.
• 32 bit can have Max 2^32 = 4 GB VAS and 64 bit will have 2^64 = Almost 16 Trillion GB VAS – 8TB is CAP

Q. I have restarted my windows server. Can you be able to explain how memory allocation happens for SQL Server?

Ans:

Memory allocation is always depends on CPU architecture.

32 Bit:

• Initially allocates memory for Memory To Leave (MTL) also known as VAS Reservation (384 MB). This MTL value can be modified using the start parameter “–g”
• Then Allocates memory for Buffer Pool = User VAS – MTL (Reserved VAS) = Available VAS
• Maximum BPool Size = 2048 MB – 384 MB = 1664 MB.

64 Bit:

• Allocates Memory for Buffer Pool based on Maximum Server Memory configuration
• Non-Buffer Pool Memory region (MTL / VAS Reservation) = Total Physical Memory – (Max Server Memory + Physical Memory Used by OS and Other Apps)
• Ex: Windows Server is having 64 GB physical memory; SQL Server Max Server Memory = 54 GB and OS and other apps are using 6 GB then the memory available for
• Non-BPool (MTL / VAS Reservation) = 64 – (54+6) = 4 GB
• Max Buffer Pool Size = Max Server Memory = 54 GB

Q. What are all the objects that use MTL / Non-BPool memory allocated for SQL Server?

Ans:

• Connections with Network Packet Size higher than 8KB (8192 bytes)
• Memory allocated by Linked Server OLEDB Providers and third party DLL’s loaded in SQL Server process
• Extended Stored Procedures or sp_OAcreate calls
• XML Documents
• Query Plans with the size > 8 KB
• SQL Server CLR
• Backups using larger MAXTRANSFERSIZE parameter
• Memory consumed by memory managers when memory requested for more than 8 KB contiguous allocation
• Memory for threads (stack size is 2 MB in 64-BIT SQL)

Q. What is “Lock Pages in Memory”?

Ans:

• As you may know how memory allocation on windows operating system SQL Server occupies memory as much as it can based on the configurations and available memory.
• When windows operating system encounter a memory pressure it starts asking SQL Server to release memory and that released memory will be paged to page file.
• When Lock Pages In Memory is enabled for SQL Server Service Account then SQL Server can lock the pages and need not release memory when windows forcing to release.

Q. Can you technically explain how memory allocated and lock pages works?

Ans:

Windows OS runs all processes on its own Virtual Memory known as Virtual Address Space and this VAS is divided into Kernal (System) and User (Application) mode.

Default: No Lock Pages in Memory is enabled

• SQL Server memory allocations made under User Mode VAS using VirtualAlloc() WPI function.
• Any memory that allocates via VirtualAlloc() can be paged to disk.
• SQLOS resource monitor checks QueryMemoryResourceNotification windows API and when windows sets Memory Low notification, SQLOS responds to that request by releasing the memory back to windows.

Lock Pages in Memory is Enabled for SQL Server:

• SQL Server memory allocations are made using calls to the function AllocateUserPhysicalPages() in AWE API.
• Memory that allocates using AllocateUserPhysicalPages() is considered to be locked. That means these pages should be on Physical Memory and need not be released when a Memory Low Notification on Windows.

Q. Does Lock pages in Memory (LPIM) is enabled to by default for SQL Server 2008 R2 / 2012 /2014?

Ans:

No! As per Microsoft documentation it’s not. We need to enable it.

Q. When to choose Lock Pages in Memory option for SQL Server?

Ans:

• When using Old windows servers – SQL Server 2005 on Windows Server 2003
• When working with 32 Bit servers and AWE is enabled
• When using Windows 2008 R2 / 2012 / 2014 and above still seeing Hard Trims happening SQL Server process memory.

Note: If you are using the latest windows systems and configured SQL Server memory settings as per the business requirement we need not worry about Lock Pages in Memory.

Q. How to enable Lock Pages in Memory for SQL Server?

Ans:

We can enable by adding SQL Server service account to “Lock Pages in Memory” in group policy editor.

• Open Group Policy Editor using the shortcut “GPEDIT.MSC”
• Computer Configuration
• Windows Settings
• Security Settings
• Local Policies
• User Rights Assignment
• Right Side “Lock Pages In Memory”
• Right Click Properties
• Add SQL Server DBEngine service account
• Restart SQL Server

Q. On which basis you would determine the Max Server Memory Setting for SQL Server?

Ans:

Max Server Memory is the maximum amount of memory reserved for SQL Server. There are few things needs to be considered:

• Applications sharing the host with SQL Server and required average memory for those Apps
• Total Physical memory is less than 16 GB leave 1 GB for OS for each 4 GB
• Total Physical memory is greater than 16 GB leave 1 GB for OS for each 8 GB
• Monitor Memory\Available Mbytes counter and check the status
• Ex: If no user application is sharing with SQL Server
• Total Physical Memory = 8 GB – 2 GB for OS and Apps + (6 GB – Max Server Memory)
• Total Physical Memory = 16 GB – 4 GB for OS and Apps + (12 GB – Max Server Memory)
• Total Physical Memory = 96 GB – 12 GB for OS and Apps+ (84 GB – Max Server Memory)

Remember in 64 bit machine we need to consider Non-Buffer Pool object memory region while leaving memory for OS. This is just a baseline that we followed while setting up new environments. We are strictly supposed to monitor the memory usage in peak hours and adjust the settings.

Q. Here is a scenario: When we are monitoring memory usage by one of the SQL Server instance, surprisingly sql server is using more memory than Max Server Memory configuration. Any idea why it’s happening?

Ans:

• Yes! It is expected as Memory is allocated for SQL server: BPool + Non-Bpool.
• BPool can be controlled by the Max Server Memory Setting but not the Non-BPool memory.
• Also Lock Pages in Memory can control BPool memory but still Non-BPool pages are paged to disk

Q. What is Instant File Initialization and how it works?

Ans:

On Windows systems when SQL Server needs to write something on disk, first it verify that the hard disk space is trustworthy means the space is readable. The verification process is:

• SQL Server writes zeros to the file
• This process is known as zeroing process
• SQL Server uses single thread for zeroing process
• The actual operation that triggers this verification should wait until this verification process completed.

If Instant File Initialization is enabled for SQL Server, it skips the zeroing process for data files and reduces the wait time.

Q. What are the database activities that get benefit from Instant File Initialization?

Ans:

• Creating a new Database
• Restoring a database from backup file
• Increasing database data file size manually
• Increasing database data file size due to Auto Growth option
• Tempdb creation at the time of SQL Server restart

Note: Remember growing log file still uses the zeroing process

Q. How to check if Instant File Initialization is enabled for a SQL Server

Ans:

Enabling trace flags (3004,3605) enable writing zeroing process information into SQL Server error log. If Instant File Initialization is enabled for sql server we can’t see zeroing process messages for data file where we can still can see zeroing process messages related to log files something like “Zeroing Completed On …….._log.ldf”.

Q. How to enable Instant File Initialization?

Ans:

In order for SQL Server to be able to perform instant file initialization the SQL Server service account must be granted the Perform Volume Maintenance Task security permission. This can be done by using the Local Security Policy Editor.

• Run lusrmgr.msc on the server to find the appropriate group name for each instance of SQL Server.
• Run secpol.msc on the server.
• Under Security Settings on the left, go to Local Policies and under that to User Rights Assignment.
• Under Policy on the right side, go to “Perform volume maintenance tasks” and double click on it
• Add SQL Server group created by SQL setup (standalone) or cluster domain group (for clusters)
• Restart SQL Server

Note: If your sql server service account is already in part of Windows Local Administrators Group then we need not add it to the Volume Maintenance Task. Also IFI doesn’t works if Transparent Data Encryption (TDE) is enabled.

Q. Have you ever implemented Instant file Initialization in any environment? If yes did you observe any performance gain?

Ans:

Yes! I have enabled this option in most of the environments I worked and I strongly suggest enabling it.

I have clearly seen the performance gain by enabling Instant File Initialization. An example:

Restoring a database from the backup (160 GB) – 3Hr 47Min

After Enabling Instant File Initialization the same operation took – 2Hr 8 Min

Q. Does Instant File Initialization is enabled for SQL Server by default?

Ans:

No! By default IFI is not enabled for SQL Server as there is a slight security risk.

As you may know, when data is deleted from disk by the operating system, it really is not physically deleted; the space holding the data is just marked as being available. At some point, the older data will be overwritten with new data.

• When Instant File Initialization is not enabled: Data is zeroed out before writing anything on that page.
• When Instant File Initialization is enabled: There is a slight security risk here. When a new database is created those new pages are not zeroed out and there is a chance that newly allocated pages might contain previously deleted data and one can read that data using a recovery tool.

Q. Can you tell me the difference between Logical Reads and Physical Reads?

Ans:

Logical Reads:

• Logical read indicates total number of data pages needed to be accessed from data cache to process query.
• It is very possible that logical read will access same data pages many times, so count of logical read value may be higher than actual number of pages in a table.
• Usually the best way to reduce logical read is to apply correct index or to rewrite the query.

Physical Reads:

• Physical read indicates total number of data pages that are read from disk.
• For a query when required pages are not found on cache memory it picks the required pages from Hard Disk and keep those pages on cache memory for further usage. This is known as physical read.

Q. When I have been checking for buffer usage one of the stored procedure is using large number of Logical Reads. Does this impact performance? What are the most possible reasons for a large number of logical reads?

Ans:

Yes! Large number of logical reads leads to memory pressure. There are few common reasons that cause more logical reads:

• Unused Indexes: Indexes should be built on the basis of data retrieval process. If indexes defined on columns and those columns are not being used in queries that leads to huge number of logical reads.
• Wide Indexes: Indexing on the large number of columns will leads to high logical reads.
• Poor Fill Factor/Page Density: When a less fill factor is specified large number of page needed to qualify a simple query which leads to High Logical Reads.
• Poor Query Design: If query leads to index scan, Hash Join when Merge Join is possible, not using indexes etc. causes the more number of logical reads.

References:

The above List of Questions was asked in worlds top organizations for the positions Senior Database Architect and SQL Server SME and Database Advisory Team Lead. These questions were shared by Lalit Sharma and Niveedita. We would like to thank both of you for your valuable time and contribution. Would like to thank famous MVPs / bloggers (Glenn Berry, Brent Ozar, Jonathan Kehayias, John Sansom) for the tremendous explanation on sql server internals. Their articles are very helpful in understanding internals and answering these questions.

Also Check:

Script to Monitor SQL Server Memory Usage

SQL Server CPU Utilization