Trace Flags in SQL Server related to Memory Issues or Memory Dumps

SQLServerF1

Trace Flags in SQL Server are used to turn ON or OFF particular behavior. TraceFlags are used for variety of purposes like Logging extended information in to SQL Server errorlog, Troubleshooting or improving SQL Server Performance, Enabling certain features or functionality, etc.

Below are the SQL Server Trace Flags related to Memory related Issues or Memory Stack Dumps in SQL Server.

806 – Cause ‘DBCC-style’ page auditing to be performed whenever a database page is read into the buffer pool. This is useful to catch cases where pages are being corrupted in memory and then written out to disk with a new page checksum. When they’re read back in the checksum will look correct, but the page is corrupt (because of the previous memory corruption). This page auditing goes someway to catching this – especially on non-Enterprise Edition systems that don’t have the ‘checksum sniffer’.

815 – Detect unwanted changes to in-memory SQL Server data pages, latch enforcement is enhanced with this trace flag enabled

831 – Protect unchanged pages in the buffer pool to catch memory corruptions.

836 – Use the max server memory option for the buffer pool. Only applicable for 32-bit SQL Server

842 – Use sys.dm_os_memory_node_access_stats to verify local vs. foreign memory under NUMA configurations after turning on this flag.

845 – SQL Server 2005 or SQL Server 2008 R2 – On 64 bit SQL Server non ENT. This turns on Lock pages in memory. Startup time of SQL Server takes longer because SQL Server allocates all memory up to the Max Server Memory setting. Scope Startup KB 970070.

1211 – Disables lock escalation based on memory pressure, or based on number of locks. The SQL Server Database Engine will not escalate row or page locks to table locks. Using this trace flag can generate excessive numbers of locks. This can slow the performance of the Database Engine, or cause 1204 errors (unable to allocate lock resource) because of insufficient memory.
Note: If both trace flag 1211 and 1224 are set, 1211 takes precedence over 1224. However, because trace flag 1211 prevents escalation in every case, even under memory pressure, we recommend that you use 1224. This helps avoid “out-of-locks” errors when many locks are being used. Scope: global or session

1224 – Disables lock escalation based on the number of locks. However, memory pressure can still activate lock escalation (when > 40%). The Database Engine escalates row or page locks to table (or partition) locks if the amount of memory used by lock objects exceeds one of the following conditions:
40% of the memory that is used by Db Engine, exclusive of memory allocation using AWE. This is applicable when the locks parameter of sp_configure is set to 0.
Forty percent of the lock memory that is configured by using the locks parameter of sp_configure.
Note: If both trace flag 1211 and 1224 are set, 1211 takes precedence over 1224. However, because trace flag 1211 prevents escalation in every case, even under memory pressure, we recommend that you use 1224. This helps avoid “out-of-locks” errors when many locks are being used. Scope: global or session

1260 – Disabled mini-dump for non-yield conditions

1262 – Dump everytime a non-yielding scheduler condition is detected.

1604 – Once enabled at start up makes SQL Server output information regarding memory allocation requests.

1611 – If possible, pin shared memory — check errorlog for success/failure.

2335 – Amount of memory available to SQL Server affects the execution plan generated though SQL Server generates the most optimal plan based on this value, but occasionally it may generate an inefficient plan for a specific query when you configure a large value for max server memory. Using 2335 as a startup parameter will cause SQL Server to generate a plan that is more conservative in terms of memory consumption when executing the query. It does not limit how much memory SQL Server can use. The memory configured for SQL Server will still be used by data cache, query execution & other consumers. KB 2413549.

2542 – Skip mini-dump

2544 – Put maximum information in a dump file (all memory in the process). Can lead to a very large dump file size for 64-bit and 32-bit systems with AWE enabled.

2546 – Dump all threads in a process. Can lead to a very large dump file size for 64-bit and 32-bit systems with AWE enabled.

2551 – Generate a filtered dump of the SQL Server process.

4010 – Allows only shared memory connections to the SQL Server

4127 – In SQL Server 2005, Compilation time of some queries is very long in an x64-based version. Basically its more than execution time because more memory allocations are necessary in the compilation process. Kb 953569

4613 – Generate a minidump file whenever an entry is logged into the ring buffer.

6527 – Disables generation of a memory dump on the first occurrence of an out-of-memory exception in CLR integration

8004 – Create memory dump for the first occurrence of out-of-memory condition

8011 – Disables the collection of additional diagnostic information for Resource Monitor. You can use the information in this ring buffer to diagnose out-of-memory conditions. Scope GLOBAL.

8020 – SQL Server uses the size of the working set when SQL Server interprets the global memory state signals from the operating system. Trace flag 8020 removes the size of the working set from consideration when SQL Server interprets the global memory state signals. If you use this trace flag incorrectly, heavy paging occurs, and the performance is poor. Therefore, contact Microsoft Support before you use.

8024 – Performs additional checks before generating non-yielding condition dump

8026 – Remove the dumptrigger setting after the first dump has been triggered.

8030 – In SQL Server 2005, Occurs only on 64-bit servers with 16+ GB of physical memory. On SQL Server 2005 query performance may degrade with time and when you query the sys.dm_os_wait_stats dynamic management view, you may notice that there are many rows in which the values of the wait_type column are SOS_RESERVEDMEMBLOCKLIST or DBCC MEMORYSTATUS. This situation indicates that many multipage allocations exist. KB 917035

8721 – Will dump information into the error log when AutoStat has been run.

9259 – In SQL Server 2005 or higher, an access violation occurs on running a query marked by the following message and a dump in the log folder: KB 970279 or 971490

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Leave a Reply

Your email address will not be published. Required fields are marked *