Использовать awe для выделения памяти sql 2008 что это

Does "use AWE to allocate memory" do anything on a SQL Server 2008 R2 64bit Windows 2003 system?

After checking the box I noticed that queries that would timeout after 30 seconds would complete execution.

The TechNet article says «AWE is not required and cannot be configured on 64-bit operating systems.»

So did checking the box do anything?

SQL Server 2008 R2 / Version 10.52.4000.0

1 Answer 1

For 64 bit OS, you dont need AWE and even enabling AWE using sp_configure awe enabled has no meaning.

In 64-bit SQL Server, the SQL Server account still requires the Lock Pages in Memory permission in order to be able to allocate locked pages, via AllocateUserPhysicalPages(), but there are a couple of big differences:

The underlying reliance on AWE-mapped memory is removed. You do not need AWE in 64-bit SQL Server; the awe enabled sp_configure option has no meaning. The continued use of the same AWE API function is purely to ensure that the allocated pages are locked.

Memory allocated via AllocateUserPhysicalPages() can be used for both the data cache and plan cache. In 64-bit SQL Server, the plan cache is no longer allocated separately (it now uses stolen pages from the buffer pool)

The memory allocations for AWE in 32bit use the AllocateUserPhysicalPages() API. The memory allocations for lock pages in 64bit use the same API, but they aren’t using AWE because the VAS in a 64-bit process is 8TB for user mode space and it doesn’t require special mappings through AWE.

Queries timing out has least to do with AWE. There might be missing indexes, outdated stats, poorly configured memory settings and many other stuff that can lead to queries timing out.

What specific version of sql server are you running (along with patch level) ?

Memory Fundamentals for SQL Server — AWE ( Address Windowing Extensions), /PAE Switch (Physical Address Extension)

2. Enabling AWE through GUI

Physical Address Extension /PAE

We’ve covered how to increase memory for user mode access by using the /3GB switch. We have also seen how to utilize the available physical memory more than 4GB using AWE. There is yet another switch called the Physical Address Extension /PAE switch which allows access for upto 128GB of RAM. Its a hardware related enhancement where the address bus is 36-bit. So now the number of addresses supported is (2^26).In terms of memory management there still exists page directories and page table entries for virtual memory mapping. In addition another level of page directory pointer table is added. The PTE is 4KB instead of 8KB. With this switch the Page Directory Table and Page Table Entries are 64-bit long so more number of addresses can be mapped. Thus unlike AWE there is a change in the memory structures and maps.

How to Enable /PAE Switch
Similar to /3GB switch you add /PAE switch in the BOOT.INI file.

Microsoft recommends the thumb rule as follows
If your system has < 4 GB — use only /3GB
If your system has > 4 GB and < 16 GB — use /3GB + /PAE + AWE
If your system has > 16 GB — use /PAE + AWE

Now that the basics are clear in the coming blogs we shall see how memory and CPU execute a user process like SQL server.

Name already in use

sql-docs / docs / relational-databases / memory-management-architecture-guide.md

• Go to file T
• Go to line L
• Copy path
• Copy permalink

• Open with Desktop
• View raw
• Copy raw contents Copy raw contents

Copy raw contents

Copy raw contents

Memory management architecture guide

Windows Virtual Memory Manager

The committed regions of address space are mapped to the available physical memory by the Windows Virtual Memory Manager (VMM).

For more information on the amount of physical memory supported by different operating systems, see the Windows documentation on Memory Limits for Windows Releases.

Virtual memory systems allow the over-commitment of physical memory, so that the ratio of virtual-to-physical memory can exceed 1:1. As a result, larger programs can run on computers with various physical memory configurations. However, using significantly more virtual memory than the combined average working sets of all the processes can cause poor performance.

SQL Server memory architecture

[!INCLUDEssNoVersion] dynamically acquires and frees memory as required. Typically, an administrator doesn’t have to specify how much memory should be allocated to [!INCLUDEssNoVersion], although the option still exists and is required in some environments.

One of the primary design goals of all database software is to minimize disk I/O because disk reads and writes are among the most resource-intensive operations. [!INCLUDEssNoVersion] builds a buffer pool in memory to hold pages read from the database. Much of the code in [!INCLUDEssNoVersion] is dedicated to minimizing the number of physical reads and writes between the disk and the buffer pool. [!INCLUDEssNoVersion] tries to reach a balance between two goals:

• Keep the buffer pool from becoming so large that the entire system is low on memory.
• Minimize physical I/O to the database files by maximizing the size of the buffer pool.

In a heavily loaded system, some large queries that require a large amount of memory to run can’t get the minimum amount of requested memory, and receive a time-out error while waiting for memory resources. To resolve this, increase the query wait Option. For a parallel query, consider reducing the max degree of parallelism Option.

In a heavily loaded system under memory pressure, queries with merge join, sort and bitmap in the query plan can drop the bitmap when the queries don’t get the minimum required memory for the bitmap. This can affect the query performance and if the sorting process can’t fit in memory, it can increase the usage of worktables in tempdb database, causing tempdb to grow. To resolve this problem, add physical memory, or tune the queries to use a different and faster query plan.

Conventional (virtual) memory

All SQL Server editions support conventional memory on 64-bit platform. The SQL Server process can access virtual address space up to Operating System maximum on x64 architecture (SQL Server Standard Edition supports up to 128 GB). With IA64 architecture, the limit was 7 TB (IA64 not supported in SQL Server 2012 (11.x) and above). See Memory Limits for Windows for more information.

Address Windows Extensions (AWE) memory

By using Address Windowing Extensions (AWE) and the Lock pages in memory (LPIM) privilege required by AWE, you can keep most of SQL Server process memory locked in physical RAM under low virtual memory conditions. This happens in both 32-bit and 64-bit AWE allocations. The locking of memory occurs because AWE memory doesn’t go through the Virtual Memory Manager in Windows, which controls paging of memory. The AWE memory allocation API requires the Lock pages in memory (SeLockMemoryPrivilege) privilege; see AllocateUserPhysicalPages notes. Therefore, the main benefit of using the AWE API is to keep most of the memory resident in RAM if there is memory pressure on the system. For information on how to allow SQL Server to use AWE, see Enable the Lock pages in memory option.

If LPIM is granted, we strongly recommend that you set max server memory (MB) to a specific value, rather than leaving the default of 2,147,483,647 megabytes (MB). For more information, see Server Memory Server Configuration: Set options manually and Lock pages in memory (LPIM).

If LPIM isn’t enabled, SQL Server will switch to using conventional memory and in cases of OS memory exhaustion, error 17890 may be reported in the error log. The error resembles the following example:

Changes to memory management starting with [!INCLUDEssSQL11]

In older versions of [!INCLUDEssNoVersion], memory allocation was done using five different mechanisms:

• Single-Page Allocator (SPA), including only memory allocations that were less than, or equal to 8 KB in the [!INCLUDEssNoVersion] process. The max server memory (MB) and min server memory (MB) configuration options determined the limits of physical memory that the SPA consumed. The Buffer Pool was simultaneously the mechanism for SPA, and the largest consumer of single-page allocations.
• Multi-Page Allocator (MPA), for memory allocations that request more than 8 KB.
• CLR Allocator, including the SQL CLR heaps and its global allocations that are created during CLR initialization.
• Memory allocations for thread stacks in the [!INCLUDEssNoVersion] process.
• Direct Windows allocations (DWA), for memory allocation requests made directly to Windows. These include Windows heap usage and direct virtual allocations made by modules that are loaded into the [!INCLUDEssNoVersion] process. Examples of such memory allocation requests include allocations from extended stored procedure DLLs, objects that are created by using Automation procedures (sp_OA calls), and allocations from linked server providers.

Starting with [!INCLUDEssSQL11], Single-Page allocations, Multi-Page allocations and CLR allocations are all consolidated into an «Any size» Page Allocator, and included in memory limits controlled by max server memory (MB) and min server memory (MB) configuration options. This change provided a more accurate sizing ability for all memory requirements that go through the [!INCLUDEssNoVersion] memory manager.

[!IMPORTANT]
Carefully review your current max server memory (MB) and min server memory (MB) configurations after you upgrade to [!INCLUDEssSQL11] and later. This is because starting in [!INCLUDEssSQL11], such configurations now include and account for more memory allocations compared to earlier versions. These changes apply to both 32-bit and 64-bit versions of [!INCLUDEssSQL11] and [!INCLUDEssSQL14], and 64-bit versions of [!INCLUDEsssql16-md] and later.

The following table indicates whether a specific type of memory allocation is controlled by the max server memory (MB) and min server memory (MB) configuration options:

Type of memory allocation	[!INCLUDEssVersion2005], [!INCLUDEsql2008-md] and [!INCLUDE sql2008r2-md]	Starting with [!INCLUDEssSQL11]
Single-page allocations	Yes	Yes, consolidated into «any size» page allocations
Multi-page allocations	No	Yes, consolidated into «any size» page allocations
CLR allocations	No	Yes
Thread stacks memory	No	No
Direct allocations from Windows	No	No

Starting with [!INCLUDEssSQL11], [!INCLUDEssNoVersion] might allocate more memory than the value specified in the max server memory (MB) setting. This behavior may occur when the Total Server Memory (KB) value has already reached the Target Server Memory (KB) setting, as specified by max server memory (MB). If there is insufficient contiguous free memory to meet the demand of multi-page memory requests (more than 8 KB) because of memory fragmentation, [!INCLUDEssNoVersion] can perform over-commitment instead of rejecting the memory request.

As soon as this allocation is performed, the Resource Monitor background task starts to signal all memory consumers to release the allocated memory, and tries to bring the Total Server Memory (KB) value below the Target Server Memory (KB) specification. Therefore, [!INCLUDEssNoVersion] memory usage could briefly exceed the max server memory (MB) setting. In this situation, the Total Server Memory (KB) performance counter reading will exceed the max server memory (MB) and Target Server Memory (KB) settings.

This behavior is typically observed during the following operations:

• Large columnstore index queries
• Large batch mode on rowstore queries
• Columnstore index (re)builds, which use large volumes of memory to perform Hash and Sort operations
• Backup operations that require large memory buffers
• Tracing operations that have to store large input parameters

Changes to memory_to_reserve starting with [!INCLUDEssSQL11]

In older versions of [!INCLUDE ssnoversion-md], the [!INCLUDEssNoVersion] memory manager set aside a part of the process virtual address space (VAS) for use by the Multi-Page Allocator (MPA), CLR Allocator, memory allocations for thread stacks in the SQL Server process, and Direct Windows allocations (DWA). This part of the virtual address space is also known as «Mem-To-Leave» or «non-Buffer Pool» region.

The virtual address space that is reserved for these allocations is determined by the memory_to_reserve configuration option. The default value that [!INCLUDEssNoVersion] uses is 256 MB.

Because the «any size» page allocator also handles allocations greater than 8 KB, the memory_to_reserve value doesn’t include the multi-page allocations. Except for this change, everything else remains the same with this configuration option.

The following table indicates whether a specific type of memory allocation falls into the memory_to_reserve region of the virtual address space for the [!INCLUDEssNoVersion] process:

Type of memory allocation	[!INCLUDEssVersion2005], [!INCLUDEsql2008-md] and [!INCLUDE sql2008r2-md]	Starting with [!INCLUDEssSQL11]
Single-page allocations	No	No, consolidated into «any size» page allocations
Multi-page allocations	Yes	No, consolidated into «any size» page allocations
CLR allocations	Yes	Yes
Thread stacks memory	Yes	Yes
Direct allocations from Windows	Yes	Yes

Dynamic memory management

The default memory management behavior of the [!INCLUDEssDEnoversion] is to acquire as much memory as it needs without creating a memory shortage on the system. The [!INCLUDEssDEnoversion] does this by using the Memory Notification APIs in Microsoft Windows.

When [!INCLUDEssNoVersion] 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, [!INCLUDEssNoVersion] releases memory to the OS. If more memory is free, [!INCLUDEssNoVersion] may allocate more memory. [!INCLUDEssNoVersion] adds memory only when its workload requires more memory; a server at rest doesn’t increase the size of its virtual address space. If you notice that Task Manager and Performance Monitor show a steady decrease in available memory when [!INCLUDEssNoVersion] is using dynamic memory management, this is the default behavior and shouldn’t be perceived as a memory leak.

Max server memory controls the [!INCLUDEssNoVersion] memory allocation, compile memory, all caches (including the buffer pool), query execution memory grants, lock manager memory, and CLR 1 memory (essentially any memory clerk found in sys.dm_os_memory_clerks).

1 CLR memory is managed under max_server_memory allocations starting with [!INCLUDEssSQL11].

The following query returns information about currently allocated memory:

Memory for thread stacks 1 , CLR 2 , extended procedure .dll files, the OLE DB providers referenced by distributed queries, automation objects referenced in [!INCLUDEtsql] statements, and any memory allocated by a non [!INCLUDEssNoVersion] DLL, are not controlled by max server memory (MB).

1 Refer to the article on how to Configure the max worker threads Server Configuration Option, for information on the calculated default worker threads for a given number of affinitized CPUs in the current host. [!INCLUDEssNoVersion] stack sizes are as follows:

SQL Server architecture	OS architecture	Stack size
x86 (32-bit)	x86 (32-bit)	512 KB
x86 (32-bit)	x64 (64-bit)	768 KB
x64 (64-bit)	x64 (64-bit)	2048 KB
IA64 (Itanium)	IA64 (Itanium)	4096 KB

2 CLR memory is managed under max_server_memory allocations starting with [!INCLUDEssSQL11].

[!INCLUDEssNoVersion] uses the memory notification API QueryMemoryResourceNotification to determine when the [!INCLUDEssNoVersion] memory manager may allocate memory and release memory.

When [!INCLUDEssNoVersion] starts, it computes the size of virtual address space for the buffer pool based on several parameters such as amount of physical memory on the system, number of server threads and various startup parameters. [!INCLUDEssNoVersion] reserves the computed amount of its process virtual address space for the buffer pool, but it acquires (commits) only the required amount of physical memory for the current load.

The instance then continues to acquire memory as needed to support the workload. As more users connect and run queries, [!INCLUDEssNoVersion] acquires more physical memory on demand. A [!INCLUDEssNoVersion] instance continues to acquire physical memory until it either reaches its max server memory (MB) allocation target or the OS indicates there is no longer an excess of free memory; it frees memory when it has more than the min server memory setting, and the OS indicates that there is a shortage of free memory.

As other applications are started on a computer running an instance of [!INCLUDEssNoVersion], they consume memory and the amount of free physical memory drops below the [!INCLUDEssNoVersion] target. The instance of [!INCLUDEssNoVersion] adjusts its memory consumption. If another application is stopped and more memory becomes available, the instance of [!INCLUDEssNoVersion] increases the size of its memory allocation. [!INCLUDEssNoVersion] can free and acquire several megabytes of memory each second, allowing it to quickly adjust to memory allocation changes.

Effects of min and max server memory

The min server memory and max server memory configuration options establish upper and lower limits to the amount of memory used by the buffer pool and other caches of the [!INCLUDEssDE-md]. The buffer pool doesn’t immediately acquire the amount of memory specified in min server memory. The buffer pool starts with only the memory required to initialize. As the [!INCLUDEssDEnoversion] workload increases, it keeps acquiring the memory required to support the workload. The buffer pool doesn’t free any of the acquired memory until it reaches the amount specified in min server memory. Once min server memory is reached, the buffer pool then uses the standard algorithm to acquire and free memory as needed. The only difference is that the buffer pool never drops its memory allocation below the level specified in min server memory, and never acquires more memory than the level specified in max server memory (MB).

[!NOTE]
[!INCLUDEssNoVersion] as a process acquires more memory than specified by max server memory (MB) option. Both internal and external components can allocate memory outside of the buffer pool, which consumes additional memory, but the memory allocated to the buffer pool usually still represents the largest portion of memory consumed by [!INCLUDEssNoVersion].

The amount of memory acquired by the [!INCLUDEssDEnoversion] is entirely dependent on the workload placed on the instance. A [!INCLUDEssNoVersion] instance that isn’t processing many requests may never reach min server memory.

If the same value is specified for both min server memory and max server memory (MB), then once the memory allocated to the [!INCLUDEssDEnoversion] reaches that value, the [!INCLUDEssDEnoversion] stops dynamically freeing and acquiring memory for the buffer pool.

If an instance of [!INCLUDEssNoVersion] is running on a computer where other applications are frequently stopped or started, the allocation and deallocation of memory by the instance of [!INCLUDEssNoVersion] may slow the startup times of other applications. Also, if [!INCLUDEssNoVersion] is one of several server applications running on a single computer, the system administrators may need to control the amount of memory allocated to [!INCLUDEssNoVersion]. In these cases, you can use the min server memory and max server memory (MB) options to control how much memory [!INCLUDEssNoVersion] can use. The min server memory and max server memory options are specified in megabytes. For more information including recommendations on how to set these memory configurations, see Server Memory Configuration Options.

Memory used by SQL Server objects specifications

The following list describes the approximate amount of memory used by different objects in [!INCLUDEssNoVersion]. The amounts listed are estimates and can vary depending on the environment and how objects are created:

• Lock (as maintained by the Lock Manager): 64 bytes + 32 bytes per owner
• User connection: Approximately (3 * network_packet_size + 94 KB)

The network packet size is the size of the tabular data stream (TDS) packets that are used to communicate between applications and the [!INCLUDEssDE-md]. The default packet size is 4 KB, and is controlled by the network packet size configuration option.

When multiple active result sets (MARS) are enabled, the user connection is approximately (3 + 3 * num_logical_connections) * network_packet_size + 94 KB.

Effects of min memory per query

The min memory per query configuration option establishes the minimum amount of memory (in kilobytes) that will be allocated for the execution of a query. This is also known as the minimum memory grant. All queries must wait until the minimum memory requested can be secured, before execution can start, or until the value specified in the query wait server configuration option is exceeded. The wait type that is accumulated in this scenario is RESOURCE_SEMAPHORE .

• Increased competition for memory resources.
• Decreased concurrency by increasing the amount of memory for every single query, even if the required memory at runtime is lower that this configuration.

Memory grant considerations

For row mode execution, the initial memory grant can’t be exceeded under any condition. If more memory than the initial grant is needed to execute hash or sort operations, then these will spill to disk. A hash operation that spills is supported by a Workfile in tempdb , while a sort operation that spills is supported by a Worktable.

A spill that occurs during a Sort operation is known as a Sort warning. Sort warnings indicate that sort operations don’t fit into memory. This doesn’t include sort operations involving the creation of indexes, only sort operations within a query (such as an ORDER BY clause used in a SELECT statement).

A spill that occurs during a hash operation is known as a Hash warning. These occur when a hash recursion or cessation of hashing (hash bailout) has occurred during a hashing operation.

• Hash recursion occurs when the build input doesn’t fit into available memory, resulting in the split of input into multiple partitions that are processed separately. If any of these partitions still don’t fit into available memory, it is split into subpartitions, which are also processed separately. This splitting process continues until each partition fits into available memory or until the maximum recursion level is reached.
• Hash bailout occurs when a hashing operation reaches its maximum recursion level and shifts to an alternate plan to process the remaining partitioned data. These events can cause reduced performance in your server.

For batch mode execution, the initial memory grant can dynamically increase up to a certain internal threshold by default. This dynamic memory grant mechanism is designed to allow memory-resident execution of hash or sort operations running in batch mode. If these operations still don’t fit into memory, then these will spill to disk.

For more information on execution modes, see the Query Processing Architecture Guide.

The primary purpose of a [!INCLUDEssNoVersion] database is to store and retrieve data, so intensive disk I/O is a core characteristic of the Database Engine. And because disk I/O operations can consume many resources and take a relatively long time to finish, [!INCLUDEssNoVersion] focuses on making I/O highly efficient. Buffer management is a key component in achieving this efficiency. The buffer management component consists of two mechanisms: the buffer manager to access and update database pages, and the buffer cache (also called the buffer pool), to reduce database file I/O.

How buffer management works

A buffer is an 8-KB page in memory, the same size as a data or index page. Thus, the buffer cache is divided into 8-KB pages. The buffer manager manages the functions for reading data or index pages from the database disk files into the buffer cache and writing modified pages back to disk. A page remains in the buffer cache until the buffer manager needs the buffer area to read in more data. Data is written back to disk only if it is modified. Data in the buffer cache can be modified multiple times before being written back to disk. For more information, see Reading Pages and Writing Pages.

When [!INCLUDEssNoVersion] starts, it computes the size of virtual address space for the buffer cache based on several parameters such as the amount of physical memory on the system, the configured number of maximum server threads, and various startup parameters. [!INCLUDEssNoVersion] reserves this computed amount of its process virtual address space (called the memory target) for the buffer cache, but it acquires (commits) only the required amount of physical memory for the current load. You can query the committed_target_kb and committed_kb columns in the sys.dm_os_sys_info catalog view to return the number of pages reserved as the memory target and the number of pages currently committed in the buffer cache, respectively.

The interval between [!INCLUDEssNoVersion] startup and when the buffer cache obtains its memory target is called ramp-up. During this time, read requests fill the buffers as needed. For example, a single 8-KB page read request fills a single buffer page. This means the ramp-up depends on the number and type of client requests. Ramp-up is expedited by transforming single page read requests into aligned eight page requests (making up one extent). This allows the ramp-up to finish much faster, especially on machines with a lot of memory. For more information about pages and extents, see Pages and Extents Architecture Guide.

Because the buffer manager uses most of the memory in the [!INCLUDEssNoVersion] process, it cooperates with the memory manager to allow other components to use its buffers. The buffer manager interacts primarily with the following components:

• Resource Manager to control overall memory usage and, in 32-bit platforms, to control address space usage.
• Database Manager and the [!INCLUDEssNoVersion] Operating System (SQLOS) for low-level file I/O operations.
• Log Manager for write-ahead logging.

The buffer manager supports the following features:

The buffer manager is non-uniform memory access (NUMA) aware. Buffer cache pages are distributed across hardware NUMA nodes, which allows a thread to access a buffer page that is allocated on the local NUMA node rather than from foreign memory.

The buffer manager supports Hot Add Memory, which allows users to add physical memory without restarting the server.

The buffer manager supports large pages on 64-bit platforms. The page size is specific to the version of Windows.

The buffer manager provides extra diagnostics that are exposed through dynamic management views. You can use these views to monitor various operating system resources that are specific to [!INCLUDEssNoVersion]. For example, you can use the sys.dm_os_buffer_descriptors view to monitor the pages in the buffer cache.

The buffer manager only performs reads and writes to the database. Other file and database operations such as open, close, extend, and shrink are performed by the database manager and file manager components.

Disk I/O operations by the buffer manager have the following characteristics:

• All I/Os are performed asynchronously, which allows the calling thread to continue processing while the I/O operation takes place in the background.
• All I/Os are issued in the calling threads unless the affinity I/O option is in use. The affinity I/O mask option binds [!INCLUDEssNoVersion] disk I/O to a specified subset of CPUs. In high-end [!INCLUDEssNoVersion] online transactional processing (OLTP) environments, this extension can enhance the performance of [!INCLUDEssNoVersion] threads issuing I/Os.
• Multiple page I/Os are accomplished with scatter-gather I/O, which allows data to be transferred into or out of noncontiguous areas of memory. This means that [!INCLUDEssNoVersion] can quickly fill or flush the buffer cache while avoiding multiple physical I/O requests.

Long I/O requests

The buffer manager reports on any I/O request that has been outstanding for at least 15 seconds. This helps the system administrator distinguish between [!INCLUDEssNoVersion] problems and I/O subsystem problems. Error message 833 is reported and appears in the [!INCLUDEssNoVersion] error log as follows:

A long I/O may be either a read or a write; it isn’t currently indicated in the message. Long-I/O messages are warnings, not errors. They don’t indicate problems with [!INCLUDEssNoVersion] but with the underlying I/O system. The messages are reported to help the system administrator find the cause of poor [!INCLUDEssNoVersion] response times more quickly, and to distinguish problems that are outside the control of [!INCLUDEssNoVersion]. As such, they don’t require any action, but the system administrator should investigate why the I/O request took so long, and whether the time is justifiable.

Causes of long I/O requests

A long I/O message may indicate that an I/O is permanently blocked and will never complete (known as lost I/O), or merely that it just hasn’t completed yet. It isn’t possible to tell from the message which scenario is the case, although a lost I/O will often lead to a latch timeout.

Long I/Os often indicate a [!INCLUDEssNoVersion] workload that is too intense for the disk subsystem. An inadequate disk subsystem may be indicated when:

• Multiple long I/O messages appear in the error log during a heavy [!INCLUDEssNoVersion] workload.
• Performance Monitor counters show long disk latencies, long disk queues, or no disk idle time.

Long I/Os may also be caused by a component in the I/O path (for example, a driver, controller, or firmware) continually postponing servicing an old I/O request in favor of servicing newer requests that are closer to the current position of the disk head. The common technique of processing requests in priority based upon which ones are closest to the current position of the read/write head is known as «elevator seeking.» This may be difficult to corroborate with the Performance Monitor tool because most I/Os are being serviced promptly. Long I/O requests can be aggravated by workloads that perform large amounts of sequential I/O, such as backup and restore, table scans, sorting, creating indexes, bulk loads, and zeroing out files.

Isolated long I/Os that don’t appear related to any of the previous conditions may be caused by a hardware or driver problem. The system event log may contain a related event that helps to diagnose the problem.

Memory pressure detection

Memory pressure is a condition resulting from memory shortage, and can result in:

• Extra I/Os (such as very active lazy writer background thread)
• Higher recompile ratio
• Longer running queries (if memory grant waits exist)
• Extra CPU cycles

This situation can be triggered by external or internal causes. External causes include:

• Available physical memory (RAM) is low. This causes the system to trim working sets of currently running processes, which may result in overall slowdown. [!INCLUDEssNoVersion] may reduce the commit target of the buffer pool and start trimming internal caches more often.
• Overall available system memory (which includes the system page file) is low. This may cause the system to fail memory allocations, as it is unable to page out currently allocated memory.

Internal causes include:

• Responding to the external memory pressure, when the [!INCLUDEssDEnoversion] sets lower memory usage caps.
• Memory settings were manually lowered by reducing the max server memory configuration.
• Changes in memory distribution of internal components between the several caches.

The [!INCLUDEssDEnoversion] implements a framework dedicated to detecting and handling memory pressure, as part of its dynamic memory management. This framework includes the background task called Resource Monitor. The Resource Monitor task monitors the state of external and internal memory indicators. Once one of these indicators changes status, it calculates the corresponding notification and it broadcasts it. These notifications are internal messages from each of the engine components, and stored in ring buffers.

Two ring buffers hold information relevant to dynamic memory management:

• The Resource Monitor ring buffer, which tracks Resource Monitor activity like was memory pressure signaled or not. This ring buffer has status information depending on the current condition of RESOURCE_MEMPHYSICAL_HIGH , RESOURCE_MEMPHYSICAL_LOW , RESOURCE_MEMPHYSICAL_STEADY , or RESOURCE_MEMVIRTUAL_LOW .
• The Memory Broker ring buffer, which contains records of memory notifications for each Resource Governor resource pool. As internal memory pressure is detected, low memory notification is turned on for components that allocate memory, to trigger actions meant to balance the memory between caches.

Memory brokers monitor the demand consumption of memory by each component and then based on the information collected, it calculates and optimal value of memory for each of these components. There is a set of brokers for each Resource Governor resource pool. This information is then broadcast to each of the components, which grow or shrink their usage as required.

For more information about memory brokers, see sys.dm_os_memory_brokers.

Database pages can use one of two optional mechanisms that help ensure the integrity of the page from the time it is written to disk until it is read again: torn page protection and checksum protection. These mechanisms allow an independent method of verifying the correctness of not only the data storage, but hardware components such as controllers, drivers, cables, and even the operating system. The protection is added to the page just before writing it to disk, and verified after it is read from disk.

[!INCLUDEssNoVersion] will retry any read that fails with a checksum, torn page, or other I/O error four times. If the read is successful in any one of the retry attempts, a message will be written to the error log and the command that triggered the read will continue. If the retry attempts fail, the command will fail with error message 824.

The kind of page protection used is an attribute of the database containing the page. Checksum protection is the default protection for databases created in [!INCLUDEssVersion2005] and later. The page protection mechanism is specified at database creation time, and may be altered by using ALTER DATABASE SET . You can determine the current page protection setting by querying the page_verify_option column in the sys.databases catalog view or the IsTornPageDetectionEnabled property of the DATABASEPROPERTYEX function.

[!NOTE]
If the page protection setting is changed, the new setting does not immediately affect the entire database. Instead, pages adopt the current protection level of the database whenever they are written next. This means that the database may be composed of pages with different kinds of protection.

Torn page protection

Torn page protection, introduced in [!INCLUDE ssversion2000-md], is primarily a way of detecting page corruptions due to power failures. For example, an unexpected power failure may leave only part of a page written to disk. When torn page protection is used, a specific 2-bit signature pattern for each 512-byte sector in the 8-kilobyte (KB) database page and stored in the database page header when the page is written to disk.

When the page is read from disk, the torn bits stored in the page header are compared to the actual page sector information. The signature pattern alternates between binary 01 and 10 with every write, so it is always possible to tell when only a portion of the sectors made it to disk: if a bit is in the wrong state when the page is later read, the page was written incorrectly and a torn page is detected. Torn page detection uses minimal resources; however, it doesn’t detect all errors caused by disk hardware failures. For information on setting torn page detection, see ALTER DATABASE SET Options (Transact-SQL).

Checksum protection, introduced in [!INCLUDEssVersion2005], provides stronger data integrity checking. A checksum is calculated for the data in each page that is written, and stored in the page header. Whenever a page with a stored checksum is read from disk, the database engine recalculates the checksum for the data in the page and raises error 824 if the new checksum is different from the stored checksum. Checksum protection can catch more errors than torn page protection because it is affected by every byte of the page, however, it is moderately resource-intensive.

When checksum is enabled, errors caused by power failures and flawed hardware or firmware can be detected any time the buffer manager reads a page from disk. For information on setting checksum, see ALTER DATABASE SET Options (Transact-SQL).

[!IMPORTANT]
When a user or system database is upgraded to [!INCLUDEssVersion2005] or later, the PAGE_VERIFY value ( NONE or TORN_PAGE_DETECTION ) is retained. We highly recommend that you use CHECKSUM . TORN_PAGE_DETECTION may use fewer resources, but provides a minimal subset of the CHECKSUM protection.

Understand non-uniform memory access

[!INCLUDEssNoVersion] is non-uniform memory access (NUMA) aware, and performs well on NUMA hardware without special configuration. As clock speed and the number of processors increase, it becomes increasingly difficult to reduce the memory latency required to use this additional processing power. To circumvent this, hardware vendors provide large L3 caches, but this is only a limited solution. NUMA architecture provides a scalable solution to this problem.

[!INCLUDEssNoVersion] has been designed to take advantage of NUMA-based computers without requiring any application changes. For more information, see How to: Configure SQL Server to Use Soft-NUMA.

Dynamic partition of memory objects

Heap allocators, called memory objects in [!INCLUDEssNoVersion], allow the [!INCLUDEssDE-md] to allocate memory from the heap. These can be tracked using the sys.dm_os_memory_objects DMV.

CMemThread is a thread-safe memory object type that allows concurrent memory allocations from multiple threads. For correct tracking, CMemThread objects rely on synchronization constructs (a mutex) to ensure only a single thread is updating critical pieces of information at a time.

[!NOTE]
The CMemThread object type is utilized throughout the [!INCLUDEssDE-md] code base for many different allocations, and can be partitioned globally, by node or by CPU.

However, the use of mutexes can lead to contention if many threads are allocating from the same memory object in a highly concurrent fashion. Therefore, [!INCLUDEssNoVersion] has the concept of partitioned memory objects (PMO) and each partition is represented by a single CMemThread object. The partitioning of a memory object is statically defined and can’t be changed after creation. As memory allocation patterns vary widely based on aspects like hardware and memory usage, it is impossible to come up with the perfect partitioning pattern upfront.

In most cases, using a single partition will suffice, but in some scenarios this may lead to contention, which can be prevented only with a highly partitioned memory object. It isn’t desirable to partition each memory object as more partitions may result in other inefficiencies and increase memory fragmentation.

[!NOTE]
Before [!INCLUDEsssql16-md], trace flag 8048 could be used to force a node-based PMO to become a CPU-based PMO. Starting with [!INCLUDEssSQL14] SP2 and [!INCLUDEsssql16-md], this behavior is dynamic and controlled by the engine.

Использовать awe для выделения памяти sql 2008 что это

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

• ProfileText
• Sign in

Answered by:

Question

We have a Server running Windows Server 2008 32-bit (Enterprise Edition) and SQL Server 2008 (SP2) 32-bit (Enterprise Edition) installed.

The server has 32 GB Memory and we assigned 22 GB to SQL Server but its using only 1.5 GB.

We have to enable the AWE Option on SQL Server but:

1- Do we have to enable\configure any options on Windows before enabling the option on SQL Server ?

2- How much memory can be assigned to SQL Server on the Enterprise Windows (Max) ?

3- Is there any other thing we should take care of before applying since its a production server ?

• Moved by Olaf Helper MVP Sunday, January 15, 2017 12:56 PM Moved from "Database Engine" to a more related forum

Answers

Just because SQL is currently only using 1.5 GB memory, doesn’t mean it won’t use more later as heavy production workload ramps up.

According to these guidelines, you can set SQL max_server_memory value = 28GB, since your server has 32GB RAM:
How much memory does my SQL Server actually need

Typically, you’ll also want to enable LPIM for your SQL Service startup account:
How to enable the Lock Pages in Memory option

If the SQL Service startup account does not have local admin rights, you should configure Instant File Initialization:
How and Why to Enable Instant File Initialization

If you haven’t already applied SP4 update to the SQL instance, you should apply that as well, since it is the only supported (by MS) version of SQL2008, now:
SQL Server 2008 Service Pack 4 release information

Hope that helps.

Phil Streiff, MCDBA, MCITP, MCSA

• Marked as answer by Osama Waly Wednesday, January 4, 2017 10:10 AM

All replies

The server has 32 GB Memory and we assigned 22 GB to SQL Server but its using only 1.5 GB.

Do you see it uses 1.5 GB memory in task manager?

sp_configure ‘show advanced options’, 1

sp_configure ‘awe enabled’, 1

sp_configure ‘max server memory’, 22528

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

Thanks Uri, i know the configurations but i’m asking about if there are any prerequisites.

1- Do we have to enable\configure any options on Windows before enabling the option on SQL Server ?

2- How much memory can be assigned to SQL Server on the Enterprise Windows (Max) ?

3- Is there any other thing we should take care of before applying since its a production server ?

Changing the memory configuration on old versions of Windows is complicated. Your versions of SQL and Windows are very old. And it’s likely that changing the settings will not help your performance much.

In short, what you are attempting is probably not a good idea. And instead you should start planning for an upgrade at least to a modern 64bit version of Windows, and possibly to a newer 64bit version of SQL Server.

Microsoft Technology Center — Dallas

• Edited by davidbaxterbrowne Microsoft employee Tuesday, January 3, 2017 3:31 PM

Just because SQL is currently only using 1.5 GB memory, doesn’t mean it won’t use more later as heavy production workload ramps up.

According to these guidelines, you can set SQL max_server_memory value = 28GB, since your server has 32GB RAM:
How much memory does my SQL Server actually need

Typically, you’ll also want to enable LPIM for your SQL Service startup account:
How to enable the Lock Pages in Memory option

If the SQL Service startup account does not have local admin rights, you should configure Instant File Initialization:
How and Why to Enable Instant File Initialization

If you haven’t already applied SP4 update to the SQL instance, you should apply that as well, since it is the only supported (by MS) version of SQL2008, now:
SQL Server 2008 Service Pack 4 release information

Hope that helps.

Phil Streiff, MCDBA, MCITP, MCSA

• Marked as answer by Osama Waly Wednesday, January 4, 2017 10:10 AM

You also need to decide whether to set the /3GB switch.

Microsoft Technology Center — Dallas

Just because SQL is currently only using 1.5 GB memory, doesn’t mean it won’t use more later as heavy production workload ramps up.

According to these guidelines, you can set SQL max_server_memory value = 28GB, since your server has 32GB RAM:
How much memory does my SQL Server actually need

Typically, you’ll also want to enable LPIM for your SQL Service startup account:
How to enable the Lock Pages in Memory option

If the SQL Service startup account does not have local admin rights, you should configure Instant File Initialization:
How and Why to Enable Instant File Initialization

If you haven’t already applied SP4 update to the SQL instance, you should apply that as well, since it is the only supported (by MS) version of SQL2008, now:
SQL Server 2008 Service Pack 4 release information

Hope that helps.

Phil Streiff, MCDBA, MCITP, MCSA

I would like to correct here, this is 32 bit SQL Server and on 32 bit OS it can only access 2 G of memory at max no matter what is workload( under default config).

Setting max server memory unless PAE is enabled is of no use.

In this case what Osama is asking you need to first enable PAE so that windows server can see more than 4 G and then enable AWE so that SQL Server can use more than 2 G but only for data and index pages.

Similar thread by Osama

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it