How to Identify Microsoft SQL Server Memory Bottlenecks

There are many reasons for memory related performance problems on a MS SQL Server instance, the source can be either a limit in virtual or physical memory, memory pressure from other applications or inside the SQL Server. Fortunately enough, we have many built-in tools which can be used to track down the root cause.

Performance Monitor

Performance Monitor is part of the Microsoft Management Console, you can find it by navigating to Start Menu -> Administrative Tools group.  First, I would like to emphasize that the values below can vary from system to system, depending on the amount of memory, system volume, load, etc.  I suggest saving metrics of the system under normally working load so you have a reference of the typical values.  As a starting point, review the Memory: Available [M, K] Bytes performance counter. Low amount of available memory might indicate external memory pressure. A rule of thumb is to look into this counter when the value drops below 5% of all available memory. If there are memory-related errors, you will have to look for the key memory consumers on the system. They can be identified by using the Process: Working Set performance counter. The total physical memory in use can be approximately calculated by summing the following counters:

• Process object, Working Set counter for each process
• Memory object
  • Cache Bytes counter for system working set
  • Pool Nonpaged Bytes counter for size of unpaged pool
  • Available Bytes counter
  • Modified Page List Bytes counter

The Process: Private Bytes counter should be around the size of the working set (Process: Working Set), otherwise the memory is paged out.Unfortunately these performance counters do not take into account AWE mechanisms.  If AWE is enabled, you will need to look at the memory distribution inside SQL Server using DBCC MEMORYSTATUS command or Dynamic Management Views (see below).
You need to find out whether the page file has enough space for the virtual memory. Take a look at the following counters: Memory: Commit Limit, Paging File: %Usage, Paging File: %Usage Peak. You can estimate the amount of memory that is paged out per process by calculating the difference between Process: Working Set and Process Private Bytes counters. High Paging File: %Usage Peak can indicate low virtual memory event. A solution can be to increase the size of your page file. High Paging File: %Usage is a sign of physical memory over commitment so you should also look for potential external physical memory pressure.
The following performance counters on SQL Server: Buffer Manager object can also indicate memory pressure:

• High number of Checkpoint pages/sec
• High number of Lazy writes/sec
• High number of Page reads/sec
• Low Buffer cache hit ratio
• Low Page Life Expectancy

For further reading on this topic, check out these tips:

• SQL Server Database Specific Performance Counters
• Setting up Performance Monitor to always collect SQL Server performance statistics

---

DBCC MEMORYSTATUS command

You can use the DBCC MEMORYSTATUS command to check for any abnormal memory buffer distribution inside SQL Server. The buffer pool uses most of the memory committed by SQL Server. Run the DBCC MEMORYSTATUS command and scroll down to the Buffer Pool section (or Buffer Counts in SQL Server 2005), look for the Target value. It shows the number of 8-KB pages which can be committed without causing paging. A drop in the number of target pages might indicate response to an external physical memory pressure.
If the Committed amount is above Target, continue investigating the largest memory consumers inside SQL Server. When the server is not loaded, Target normally exceeds Committed and the value of the Process: Private Bytesperformance counter.
If the Target value is low, but the server Process: Private Bytes is high, you might be facing internal SQL memory problems with components that use memory from outside of the buffer pool. Such components include linked servers, COM objects, extended stored procedures, SQL CLR, etc.  If the Target value is low but its value is close to the total memory used by SQL Server, than you should check whether your SQL Server received a sufficient amount of memory from the system. Also you can check the server memory configuration parameters.
You can compare the Target count against the max server memory values if it is set. Latter option limits the maximum memory consumption of the buffer pool. Therefore the Target value cannot exceed this value.  Also the low Targetcount can indicate problems: in case it is less than the min server memory setting, you should suspect external virtual memory pressure.
My last recommendation on the DBCC MEMORYSTATUS output is to check the Stolen Pages count. A high percentage (>75%) of Stolen Pages compared to Target can be a sign of internal memory pressure.
Further reading on Microsoft Support pages:

• INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage
• How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005

---

Dynamic Management Views

You can use the sys.dm_os_memory_clerks dynamic management view (DMV) to get detailed information about memory allocation by the server components in SQL Server 2005 and 2008.  Some of the DMVs provide similar data as the DBCC MEMORYSTATUS command, but their output is much more programmer friendly. For example the following query returns the amount of memory SQL Server has allocated through the AWE mechanism.

SELECT SUM(awe_allocated_kb)
FROM sys.dm_os_memory_clerks

You can also check the amount of memory that is consumed from outside of the buffer pool through the multipage allocator.

SELECT SUM(multi_pages_kb) 
FROM sys.dm_os_memory_clerks

If you are seeing significant amounts of memory (more than 100-200 MB) allocated through the multipage allocator, check the server configuration and try to identify the components that consume the most memory by using the following query:

SELECT type, SUM(multi_pages_kb)
FROM sys.dm_os_memory_clerks 
WHERE multi_pages_kb <> 0 
GROUP BY type
ORDER BY SUM(multi_pages_kb) DESC

In SQL Server 2008, you can query the sys.dm_os_process_memory DMV to retrieve similar data. Look for the columnsphysical_memory_in_use, large_page_allocations_kb, locked_pages_allocations_kb andmemory_utilization_percentage. The process_physical_memory_low = 1 value indicates that the process responds to physical memory low notification from the OS.
Check the main consumers of the buffer pool pages:

SELECT type, 
       SUM(single_pages_kb) as [Single Pages],
       SUM(multi_pages_kb) as [Multi Pages]
FROM sys.dm_os_memory_clerks
GROUP BY type 

In SQL Server 2005 and 2008, internal clock hand controls the relative size of caches. It launches when the cache is about to reach its maximum. The external clock hand moves as the SQL Server gets into memory pressure. Information about clock hands can be obtained through the sys.dm_os_memory_cache_clock_hands DMV. Each cache has a separate entry for the internal and the external clock hand. If the rounds_count and removed_all_rounds_count values are increasing then your server is under memory pressure.

SELECT  *
FROM sys.dm_os_memory_cache_clock_hands
WHERE rounds_count > 0

You can get additional information about the caches by joining with the sys.dm_os_cache_counters (Please note that the amount of pages is NULL for USERSTORE entries):

SELECT
    distinct mcc.cache_address, 
    mcc.name, 
    mcc.type,
    mcc.single_pages_kb,
    mcc.multi_pages_kb, 
    mcc.single_pages_in_use_kb,
    mcc.multi_pages_in_use_kb, 
    mcc.entries_count, 
    mcc.entries_in_use_count,
    mcch.removed_all_rounds_count, 
    mcch.removed_last_round_count
FROM sys.dm_os_memory_cache_counters mcc 
    JOIN sys.dm_os_memory_cache_clock_hands mcch 
 ON (mcc.cache_address = mcch.cache_address)

Virtual Address Space consumption can be tracked by using the sys.dm_os_virtual_address_dump DMV. If the largest available region is less than 4 MB then your system is most likely under VAS pressure. SQL Server 2005 and 2008 actively monitor and respond to VAS pressure.
You can also use the following DMVs for memory troubleshooting both in SQL Server 2005 and 2008:

• sys.dm_exec_cached_plans
• sys.dm_exec_query_memory_grants
• sys.dm_exec_query_resource_semaphores
• sys.dm_exec_requests
• sys.dm_exec_sessions
• sys.dm_os_memory_cache_entries

There are several new DMVs in SQL Server 2008 which make us easier to gather memory diagnosis information. I would like summarize these new DMVs for memory troubleshooting:

• sys.dm_os_memory_brokers provides information about memory allocations using the internal SQL Server memory manager. The information provided can be useful in determining very large memory consumers.
• sys.dm_os_memory_nodes and sys.dm_os_memory_node_access_stats provide summary information of the memory allocations per memory node and node access statistics grouped by the type of the page. This information can be used instead of running DBCC MEMORYSTATUS to quickly obtain summary memory usage. (sys.dm_os_memory_node_access_stats is populated under dynamic trace flag 842 due to its performance impact.)
• sys.dm_os_nodes provides information about CPU node configuration for SQL Server. This DMV also reflects software NUMA (soft-NUMA) configuration.
• sys.dm_os_sys_memory returns the system memory information. The ‘Available physical memory is low’ value in the system_memory_state_desc column is a sign of external memory pressure that requires further analysis.

---

Resource Governor

The Resource Governor in SQL Server 2008 Enterprise edition allows you to fine tune SQL Server memory allocation strategies, but incorrect settings can be a cause for out-of-memory errors. The following DMVs can provide information about the Resource Governor feature of SQL Server 2008: sys.dm_resource_governor_configuration, sys.dm_resource_governor_resource_pools and sys.dm_resource_governor_workload_groups

---

SQL Server ring buffers

Another source of diagnostic memory information is the sys.dm_os_ring_buffers DMV. Each ring buffer records the last number of notifications. You can query the ring buffer event counts using the following code:

SELECT ring_buffer_type, COUNT(*) AS [Events]
FROM sys.dm_os_ring_buffers
GROUP BY ring_buffer_type
ORDER BY ring_buffer_type

Here is a list of ring buffers of interest:

• RING_BUFFER_SCHEDULER_MONITOR: Stores information about the overall state of the server. The SystemHealthrecords are created with one minute intervals.
• RING_BUFFER_RESOURCE_MONITOR: This ring buffer captures every memory state change by using resource monitor notifications.
• RING_BUFFER_OOM: This ring buffer contains records indicating out-of-memory conditions.
• RING_BUFFER_MEMORY_BROKER: This ring buffer contains memory notifications for the Resource Governor resource pool.
• RING_BUFFER_BUFFER_POOL: This ring buffer contains records of buffer pool failures.

 Ref: http://www.mssqltips.com

Configuring Server Startup Parameters in SQL Server Denali

In previous versions of SQL Server, whenever a DBA had to change the SQL Server startup options he had to carefully add or modify the entries which were separated using semicolons (;). In the case where you missed adding the entries correctly, the SQL Server service will not start. In the below snippet you can see how this was done for SQL Server 2005 and SQL Server 2008 under the Advanced Tab in SQL Server Configuration Manager.

In the new version of SQL Server “Denali” Microsoft has made configuring the SQL Server startup options an easy and simple process. Let's go through an example which demonstrates how one can configure SQL Server Startup Options.
1. In SQL Server Configuration Manager, click SQL Server Services on the left side pane.
2. In the right side pane, right click the SQL Server (<instance_name>) Service and choose Properties from the drop down menu.

3. In the SQL Server Properties window navigate to the Startup Parameters tab as shown below. 

• To modify an existing parameter, select it from the "Existing parameters" list, make the change and click Updateto save. 
• To add a new parameter, like a trace flag, type the new parameter and click on Add to save the change.
• To remove an existing parameter select if from the "Existing parameters" list and click Remove.

4. Note that you still need to restart the SQL Server service for these changes to take effect just like older versions of SQL Server.

Unable to see a SQL Server table

From SQL Server 2005 onward, Microsoft is restricting what objects you could see in the database based on how your permissions were set up. For instance, if you don't have any permissions for a particular table, and you don't have permissions at a higher level (like being a member of the db_owner fixed database role), you won't see the table. That's a change in behavior from SQL Server 2000, where if you had access to the database, you could see every object in it. Therefore, it's likely you don't have any permissions against the table in question. If that's the case, then SQL Server is functioning as designed.


We can see this with a simple example. I've performed several steps that you'll need to recreate in order to run the example. First, you'll need to create a SQL Server based login called NormalUser. Within your test database you'll need to create a role called Everyone. Add the NormalUser login to the database and make it a member of the Everyone role. After that, go into the database you're using and execute the following script.

CREATE TABLE EveryoneSeesMe (ID INT);
GO 
GRANT SELECT ON EveryoneSeesMe TO Everyone;
GO 
CREATE TABLE IAmHidden (ID INT);
GO

Here we're creating two tables, one of which the Everyone role has SELECT permissions on. The other table, IAmHidden, we're not assigning any permissions to. Now, if I execute the following, I'll see both tables, because my account is a member of the sysadmin server role (and therefore a member of the db_owner role for the database):

SELECT * FROM sys.objects WHERE type = 'U';
GO 

Because I have high privileges, I see both tables, like so:

If I want to see what a normal user sees, I'll need to execute the following:

EXECUTE AS LOGIN = 'NormalUser';
GO
SELECT * FROM sys.objects WHERE type = 'U';
GO 
REVERT;
GO 

Instead of seeing both tables, I'll only see EveryoneSeesMe. That's because the NormalUser only has permissions against that table. It gets those permissions because it a member of the Everyone role you created. Since no one has permissions to see IAmHidden, the NormalUser isn't shown that table when it queries sys.objects. Even if there were permissions against IAmHidden, but none of those permissions matched up to NormalUser, the query would still only show this:

Therefore, if you're running on SQL Server 2005 or above and you can't see an object that you know is there, check with your DBA. Chances are you don't have any permissions for that object. If that's the case, then SQL Server is performing its role properly.
Ref: http://www.mssqltips.com

Using CROSS JOIN queries to find records out of sequence

Let’s say I have table in which there are three fields:

• PersonID
• Version
• DEDate  (date entry date)

Here, PersonID is an integer field and could be considered our primary ID. Version is again an integer field and could be considered a secondary ID. So, in all PersonID and Version will form the Composite Key. There can be many versions of the same PersonID and each Version would have a corresponding Data Entry Date (DEDate). Our problem is to compare the Data Entry Dates of all the versions of a particular person on an iterative basis using a single query to ensure the Data Entry Dates are in order based on the Version for a particular person.
Data Entry Date’s for all versions of a particular PersonID should be compared in in such a way that the succeeding version’s Data Entry Date should be greater than its preceding version’s Data Entry Date. If the records are stored correctly, then that PersonID’s version is said to be in proper order, otherwise it is said to be in an improper order.
I have tried lots of ways to tackle this problem, using cursors, loops, subqueries, etc... , but the best solution I have found is by using CROSS JOINS.
The following script will help you understanding the problem and its solution.  I am creating a new table, adding some data and then using the query to determine which records are out of order.

CREATE TABLE [dbo].[Person]( [PersonID] [int] NOT NULL, [Version] [int] NOT NULL, [DEDate] [datetime] NULL, CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED ( [PersonID] ASC, [Version] ASC )) GO INSERT INTO Person VALUES(1,0,'03/10/2000') INSERT INTO Person VALUES(1,1,'03/16/2000') INSERT INTO Person VALUES(1,2,'03/19/2000') INSERT INTO Person VALUES(1,3,'03/18/2000') INSERT INTO Person VALUES(1,4,'03/17/2000') INSERT INTO Person VALUES(2,0,'02/10/2000') INSERT INTO Person VALUES(2,1,'02/11/2000') INSERT INTO Person VALUES(2,2,'02/18/2000') INSERT INTO Person VALUES(3,0,'03/25/2000') INSERT INTO Person VALUES(3,1,'03/23/2000') INSERT INTO Person VALUES(3,2,'03/26/2000') INSERT INTO Person VALUES(3,3,'03/30/2000') INSERT INTO Person VALUES(4,0,'08/19/2000') INSERT INTO Person VALUES(4,1,'08/20/2000') INSERT INTO Person VALUES(4,2,'08/23/2000') INSERT INTO Person VALUES(4,3,'08/24/2000') GO SELECT *, (SELECT CASE WHEN (SUM(CASE WHEN B.DEDate<C.DEDate THEN 0 ELSE 1 END))>=1 THEN 1 ELSE 0 END AS IsNotProper FROM Person2 B CROSS JOIN Person2 C WHERE B.PersonID=A.PersonID AND C.PersonID=A.PersonID AND B.Version<>C.Version AND B.Version<C.Version) AS [OutOfOrder] FROM Person2 A

In the output it will display all the records with a flag called OutOfOrder. If the flag is 1 that means there is a problem with the record order. Otherwise, the PersonID’s versions are in proper order.
In this example we can see that PersonID 1 has two records that are out of order.  The 2003-03-18 record came before the 2008-03-17 record, so the query picked these up as being out of order.  Also, for PersonID 3 the 2000-03-25 record came before the 2000-03-23 record, so again these are out of order.

So, instead of using loops and cursors to determine if there is an issue it handles everything in a single query.

---

In this next example we are looking at just one record (PersonID =1) and we can also see the execution plan that is generated.

SELECT *, (SELECT CASE WHEN (SUM(CASE WHEN B.DEDate<C.DEDate THEN 0 ELSE 1 END))>=1 THEN 1 ELSE 0 END AS IsNotProper FROM Person2 B CROSS JOIN Person2 C WHERE B.PersonID=A.PersonID AND C.PersonID=A.PersonID AND B.Version<>C.Version AND B.Version<C.Version) AS [OutOfOrder] FROM Person2 A WHERE A.PersonID = 1

Query Output

Statistics I/O

(5 row(s) affected) Table 'Worktable'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Person'. Scan count 7, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Execution Plan

Attach Database Failed

Error Message
Attach database failed for Server 'SERVERNAME\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)

Additional information:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Express.ConnectionInfo)
Unable to open the physical file "filename.mdf". Operating system error 5: "5(error not found)".(Microsoft SQL Server, Error: 5120)

Solution.
Please make sure that the SQL Server Service account has modify permissions over the physical file on the hard drive that you are trying to attach to a SQL Server instance, and modify permissions also over the folder that contains the file.