A significant part of SQL Server process memory has been paged out

A significant part of SQL Server process memory has been paged out. This may result in performance degradation.

A significant part of sql server process memory has been paged out.

This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 48864, committed (KB): 124224, memory utilization: 39%.

Cannot use Large Page Extensions: lock memory privilege was not granted.

What is working set: Memory allocated by the process which is currently in RAM.

Committed: Total memory that is allocated by process (allocated bytes can be in RAM or Page file)

Working Set trimming:  Windows is moving the allocated bytes of the process from physical RAM to page file because of memory pressure. Memory pressure is most commonly caused by applications or windows components that are requesting more memory causing OS to start trimming working set of other processes to satisfy these new requests.

Before we step in to troubleshooting working set trimming warnings, here are few basics about how SQL Server memory management is designed to dynamically adjust the committed memory based on the amount of available memory on the system.

SQL Server uses CreateMemoryResourceNotification to create a memory resource notification object  and SQL Server Resource monitor threads calls QueryMemoryResourceNotification every time it runs to identify if there is any notification. If a low memory notification comes from Windows, SQL Server scales down its memory usage and when Windows sends the high memory notification, SQL Server Server can grow its memory usage target. Low memory notification is signaled by windows when the available physical memory drops approximately below 32 MB per 4 GB, to a maximum of 64 MB. The default level that signals a high-memory-resource notification event is three times the default low-memory value. As soon as the SQL Server resource monitor threads finds low-memory-resource notification it scales down SQL Server memory usage.

Why do I see “A significant part of sql server process memory has been paged out. This may result in performance degradation.” By SQL Server In spite of having above mechanism to detect the system level memory pressure and scale SQL Server memory?

There are couple of situations where SQL Server Process working set might be paged out by Windows despite these memory resource notification mechanism.

1.If windows is not sending the correct notifications to all listening processes at the right moment and thresholds

2.If SQL Server is not responding fast enough to the low memory resource notification from Windows

3.Conditions in Windows where working sets of all processes are trimmed

4.Windows might decide to trim a certain percentage of working set of various or specific processes

We can also increase the LowMemoryThreshold value so the OS will notify applications such as SQL on low memory conditions much earlier and SQL Server can respond to memory pressure much early before the system is starving for memory.

How to set the LowMemoryThreshold value (in MB)?

In Regedit -> go to

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\SessionManager\MemoryManagement

Right click on the right pane,

Select New -> select click DWORD Value -> enter LowMemoryThreshold

Double Click LowMemoryThreshold -> value (choose decimal) -> 1000

System Reboot is required to take effect.

Default values as per MSDN:

“The default level of available memory that signals a low-memory-resource notification event is approximately 32 MB per 4 GB, to a maximum of 64 MB. The default level that signals a high-memory-resource notification event is three times the default low-memory value.”

We can use the below query to extract information about the condition of OS memory and SQL memory using a query like the following. Looking at this query, you will be able to easily determine the various indicators that would have triggered the Windows to page various processes including SQL Server. Use the following query to obtain the memory notification-related information from the XML data of the ring buffer.

Common Side Effects of Working set Trimming

1.  When OS starts trimming the working set of SQL Server we normally see non-yielding Resource Monitor dumps.

2.  IS-alive check failures resulting in SQL Server resource failure.

3. Resource monitor thread can start  Garbage collector  if SQLCLR is enabled on this instance of SQL. When Garbage collector  is kicked off during memory pressure all other threads in the process are suspended. So if Garbage collector is taking a long time reosurce monitor thread appears stuck and hence the non-yielding errors and dumps are generated. (Refer http://support.microsoft.com/kb/2504603)

How to troubleshoot?

1.  Capture perfmon counters (Process: Private bytes and Working set ) to determine which applications / windows component are requesting memory and causing OS to start trimming the working set of processes including SQL Server.

2.  Use this exe which will print the memory information of all the processes and system wide memory information (Global memory status) when the operating system signals  low memory notification.

3.  Cap the SQL Server MAX Server Memory after considering the memory required by other applications, Operating system, Drivers , SQL Server Non- Bpool allocations etc. Make sure you have adequate available physical memory even when the system is under heavy load.

4.  We can consider using the Lock pages in memory privilege. Remember it protects only the BPool from paging and Non-Bpool allocations can still be paged out.

Advertisements

Parameter Sniffing

What is parameter sniffing?

This problem occurs when you work with parametrized SQL statements like stored procedures in combination with the plan caching mechanism of SQL Server.

When you execute a parametrized SQL query in SQL Server, the Query Optimizer compiles an execution plan based on the first provided parameter values. Afterwards the generated query plan is cached in the plan cache for further reuse. This means that SQL Server will reuse the plan afterwards without regard for the parameter values that you provide at the time.

2 kind of parameter value:

  • Compile time values
  • Run time values

The Compile time parameter values are the values that are used by the Query Optimizer to generate a physical execution plan. And the Run time values are the values that you provide for the execution of the query plan.

For the first execution these values are identical, but for subsequent executions, these values will probably be different. This may introduce serious performance problems, because your execution plan is always optimized for the Compile time values, and not for the various Run time values that you subsequently provide.Imagine now what happens if a query plan with a Bookmark Lookup gets cached, and afterwards gets blindly reused with a non-selective value. In that case SQL Server doesn’t implement any protection mechanism and will just execute the found query plan from the plan cache. As a result   I/O costs (your logical reads) will just explode, and the query will end up with very bad performance.

To show you a concrete example of this behavior, the following code creates a simple table with an uneven data distribution in the second column.

— Create a test table
CREATE TABLE Table1
(
Column1 INT IDENTITY,
Column2 INT
)
GO

— Insert 1500 records into Table1
INSERT INTO Table1 (Column2) VALUES (1)

SELECT TOP 1499 IDENTITY(INT, 1, 1) AS n INTO #Temp
FROM
master.dbo.syscolumns sc1

INSERT INTO Table1 (Column2)
SELECT 2 FROM #Temp
DROP TABLE #Temp
GO

****************CREATE A NON CLUSTERED  INDEX ON COLUMN2**********************

CREATE NONCLUSTERED INDEX [indexTable1_Column2] ON [dbo].[Table1]
(
[Column2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Lets run the execute the select statement.

select * from Table1 where column2=1
select * from Table1 where Column2=2

This is an example of uneven distribution of data where value 1 occurs only once and that the value 2 occurs 1499 times.

We see 2 different execution plan for the same logical query.

SQL query1

Now when we create a stored procedure, the query optimizer will create a execution plan based on the initial parameter values and afterwards same execution plan will be used by the subsequent executions.

— Create a new stored procedure for data retrieval
CREATE PROCEDURE RetrieveData
(
@Col2Value INT
)
AS
SELECT * FROM Table1
WHERE Column2 = @Col2Value
GO

Now when we execute the procedure with value 1 it just return one record, query optimizer will choose a bookmark lookup in the execution plan.The query just itself produces a 3 logical reads. when you execute with the parameter value of 2, the cache plan get reused and bookmark lookup is done 1499 times as a result 1505 logical reads.This is a huge difference from the previous execution.