Shulou(Shulou.com)06/01 Report--

SQLServer 2012 memory

Translated from: https://mssqlwiki.com/tag/sql-server-2012-memory-architecture/

SQL Server 2012 compared to previous versions, the memory manager has made many changes in the efficient way to manage SQL Server memory consumption. Important changes in SQL Server 2012 memory that every DBA should know have been documented in Chinese in this blog post. If you are not familiar with previous versions of the SQL Server memory architecture, I recommend reading this article before you continue to understand the changes to the Denali memory manager.

MaxServer Memory

In previous versions of SQL Server, "Max Server Memory" controlled the maximum physical memory of the user address space that Single page allocator (BPOOL) could consume in SQL Server.

Only when Single page allocator is part of BPOOL, Max server memory only controls BPOOL, so the following allocation comes from outside BPOOL (Max server memory).

Multi-Page allocation from SQL Server (these allocation requests are larger than 8KB and require persistent memory)

CLR assignments (these include SQLCLR heaps and global allocations created during startup)

Memory usage for threadstacks in SQL Server processes (Max worker threads * threadstack size). Thread stack size is 512K in 32-bit SQL Server, 904K in WOW, and 2MB in 64-bit

Direct Windows allocation generated through Non-SQL Server dll (these include Windows heap usage and direct virtual allocation loaded into the SQL Server process through modules. For example: allocation from the extended stored procedure DLL, object creation using OLE automated procedures (sp_OA calls), allocation of linked server providers loaded into the SQL Server process)

The SQL Server 2012 memory manager now combines singlepage allocator and multipage allocator as any-size page allocator. As a result, any-size paeallocator now manages allocation, while in the past it was classified as single page and Multi-page allocation.

"Max server memory" now controls and contains "Multi pages allocations".

Previous versions of SQL Server CLR allocation are in addition to BPOOL (Max server memory). SQL Server 2012 contains memory allocated by SQL CLR in "Max server memory".

The SQL Server 2012 "Max servermemory" configuration contains more than just the following assignments:

Memory allocation for threadstacks in the SQL Server process.

Memory allocation requests directly for Windows (for example, allocations from third-party DLL loaded into the SQL Server process (Heap,Virtualalloc calls), object creation using OLE Automation procedures (sp_OA), and so on).

These changes allow DBA to configure and control SQL Server more accurately based on memory needs and the use of resource regulators.

-g startup parameters

We use the-g startup option to modify the default value of an area in the SQL Server user address space called "Memory-To-Reserve". This area is also known as "Memory-To-Leave" or MTL. This "Memory-To-Reserve" or-g configuration option is only relevant to 32-bit SQL Server instances.

In previous versions of SQL Server, until SQL Server 2008 R2 Multi pages allocation and CLR were part of Memory-To-Reserve (- g), they were part of BPOOL from Denali (controlled by Max server memory), so if you set it up in the previous version to allocate more space for multipage allocator or CLR, and now migrate to Denali, you need to remove-g.

AWE feature has been removed from SQL Server2012

The AWE feature was used to address larger than 4GB memory in previous 32-bit versions of SQLServer. This feature has now been removed from Denali. Reference: "AWE deprecation". So if you need more memory, you need to migrate to 64-bit SQL Server.

Lock the page in memory

Trace flag 845 no longer needs to lock the page in memory. As long as the SQL Server startup account has "Lock pages in memory" permission, the data Center, Enterprise, Standard and Business Intelligence editions will use the AWE allocator API for allocating memory in BPOOL, which will be locked.

Dynamic virtual address space management

In previous versions of 32-bit SQL Server, we reserved Bpool at startup, and the rest of the address space was used for MTL (Memory to reserve or Memory to leave). Virtual address space management in Denali is dynamic (we don't reserve it at startup), so more memory can be used for third-party components than with the-g parameter configuration.

SQLCLR loads at startup

In previous versions of SQLServer, the Common language runtime (CLR) function was initialized within the SQL CLR process when the first SQLServer procedure or function was called. SQL Server 2012 performs SQL CLR initialization at startup. This initialization does not depend on the "clr enabled" configuration option.

You will notice the following information in the SQL Server error log during service startup:

2012-10-18 15:23:13.250spid8s Starting up database 'master'.

2012-10-18 15:23:13.930Server CLR version v4.0.30319 loaded.

Total physical memory and memory module usage

The total physically available memory and memory module usage on the server is recorded in the SQL Server error log.

2012-10-18 15:23:06.690Server Detected 131067 MB of RAM. This isan informational message; no user action is required.

2012-10-18 15:23:06.700Server Using locked pages in the memorymanager

2012-10-22 15:32:20.450Server Detected 131067 MB of RAM. This isan informational message; no user action is required.

2012-10-22 15 32 20 450 Server Using conventional memory in the memory manager.

DMV and performance counter changes

In previous versions of SQL Server, most DMV used single_pages_kb and multi_pages_kb for allocations related to SQL Server within BPOOL and outside BPOOL. Now they are represented as pages_kb together. For more details, please click here.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.