[Translation] Playing Locked Pages, AWE, Task Manager and Working Set

Http://blogs.msdn.com/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx

I know that AWE is very confusing about "Locked Pages. I think it is normal for anyone to get dizzy. However, I also know that we have blog posts and discussions on this topic countless times, exaggerating to the point where we even know every cell of it. Of course, I know that this topic is not very interesting (but I have a deceptive title ). However, I still frequently hear about these features in 32-bit and 64-bit SQL Server from Microsoft and its customers. Therefore, I think it is valuable to write a blog that summarizes several frequently-used questions. You may be able to get started after reading. Many people like the FAQ form, so I also use it this time. I suggest you read these Frequently Asked Questions in sequence, because some answers can be understood only after you have understood the previous frequently asked questions.

1. What is the difference between AWE on a 32-bit system and "Locked Page" on a 64-bit system?

These two are just different names. Before we enter the world of 64-bit SQL Server, SQL Server introduces a "new feature" called "AWE ". This concept is used to extend the SQL Server's capability to apply for more memory and break through the Virtual Address Space (VAS) limit (2 Gb-3 Gb) of the 32-bit system ). However, the SQL development team cannot do this out of thin air. Because the Windows development team has created an API -- Address processing wing Extensions (AWE) to support this feature. If you want to further study the differences between Physical Address Extensions (PAE) and AWE, read this article .

An interesting effect on all applications using awe apis is that all memory applied by these Apis does not belong to the working set of the process. Therefore, Windows considers the memory to be locked (that is, it cannot be paged to the hard disk ). Therefore, applications that use AWE APIs must have a "Locked Pages in Memory" permission set (for example, a service account for SQL Server ).

The SQL development team has introduced the x64-bit version of SQL Server since SQL Server 2005. Because there is no VAS limit for 32-bit running processes in the x64 environment (the 64-bit ceiling is exaggerated. Theoretically, a 64-bit process can have a maximum of 16 Exabyte (KB, MB, GB, TB, PB, EB, ZB, YB ). In fact, Windows currently limits VAS to 8 TB while physical memory to 2 TB ...), There is no need to use any special API to apply for memory larger than VAS. In other words, SQL Server can still use the previous VirtualAlloc () to apply for memory. However, product developers will find that there are two scenarios if they still use unnecessary AWE APIs to allocate memory:
A. There will be a slight performance advantage in the kernel. For more details, refer to this article from Slava Oks:
Http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx
B. Like a 32-bit system, any memory allocated using the AWE API is not part of the working set. Therefore, it cannot be paged to the hard disk and is regarded as locked.

Therefore, the concept of "locked pages" called the 64-bit SQL Server was born.

So do you still remember the awe api requirements for any Windows application? The user account that runs the process must have the "Locked Pages in Memory" permission set. Therefore, if this permission is set within the 64-bit SQL Server engine, a unit check is performed. For details, see the following FAQ). We use the awe api to allocate memory. By using the awe api, SQL Server uses the "locked pages" feature.

So to sum up, awe api is used for different purposes for 32-bit and 64-bit SQL Server systems. For 32-bit systems, it is indeed used to access memory larger than 4 GB or to allow AWE features. For 64-bit systems, it is likely to be used to improve performance and use the "lock pages" feature for the buffer zone.

2. I am using a 64-bit system. Do I need to enable the sp_configure option of "awe enabled" to "lock pages?

No. In fact, the 64-bit SQL Server Code ignores the sp_configure option. It is a "no-op" command. In this case, you may ask why I just told you that awe api is used for "lock pages" in the 64-bit SQL Server system "?

The answer is the purpose of the sp_configure option. On a 32-bit system, the sp_configure option is used to enable the AWE feature. I have also explained that you must have the "Locked Pages in Memory" permission to use the AWE API. Therefore, if you use the sp_configure option on a 32-bit system to enable the "awe enabled" feature, but "Locked Pages in Memory" is not set, this command will fail.
(Here, it seems that some answers are not correct. The question is how the 64-bit system answers the question to 32-bit... Or I don't really understand it)

3. Why does the Task Manager show all memory allocated to SQL Server?

You may encounter the following situation:
You have installed a 64-bit SQL Server. The customer tells you that the computer has 8 GB of physical Memory. The performance counters of SQL Server, such as "Total Server Memory", show that SQL Server uses about 3 GB of Memory. However, when you open the Job Manager of Windows Server 2008 and find the line of SQLSERVR. EXE, you find the following situation:

(Sqlservr.exe indicates that only 100 + Mb of memory is occupied)
In this example, you can see that the "Memory" column of the task manager only shows a value of about MB. However, if you check Perfmon and check "Total Server Memory", you can see:

(Occupied 3 Gb + Memory)
Why is this difference? Well, the main reason is that I have previously explained that 64-bit SQL Server instances use "locked pages ". Note that the column in the task manager is named Memory (Private Working Set ). Remember that if the 64-bit SQL Server instance uses "locked pages", this part of memory does not belong to the working set. This is why the memory is not displayed in the task manager. In Windows Server 2003, the column name is "Mem Usage", but it also displays the working set of the process.

How can we prove that the difference is caused by locked pages? Well, there are more than one method, but the simplest method is to query sys. dm_ OS _process_memory DMV in SQL Server 2008. On my computer, I got the following results:

(The value of locked_page_allocation_kb is 3169600)
You can see from this DMV that the value of the locked_page_allocation_kb column is close to the Total Server Memory perfmon counter and displays the "locked" Memory.

4. I know that the 64-bit SQL Server can use "Locked Pages", so which part of the memory is Locked?

The simplest answer to this question is all the memory used by the Buffer Pool Manager of SQL Server. What is buffer pool management? Does this mean that only the "Database" page is locked? From SQL Server 2005, all memory allocations (in other words, all Windows API accesses to memory) are passed through the SQLOS component of the engine. All codes in the SQL engine that need to allocate <= 8 KB of memory are called "Single Page Allocator, SPA)" In SQLOS )". SQLOS then redirects all SPA requests to the buffer pool for management. The code of this buffer pool has not changed since SQL 7.0, and the memory allocation task is executed. Therefore, all memory that applies for "single page" will eventually pass through the buffer pool. The buffer pool also contains code that uses the awe api lock page.

Therefore, the buffer pool management is behind the SPA. All the code that uses the SPA will "Lock" its memory when the buffer pool management uses the lock page ". So which codes use SPA? You may have guessed that the database page will be used. You can also query sys. dm_ OS _memory_clerks DMV to find out what type of memory is using SPA. Search for any row in the single_pages_kb column> 0. If you run this query on your server, you will probably see the clerk types of CACHESTORE_OBJCP and CACHESTORE_SQLCP. This process caches the memory. I will not list more.

5. Does 32-bit SQL Server support AWE? What is the 64-bit support for "Locked Pages?

Some people have asked me and other Microsoft colleagues about whether the 32-bit SQL Server Standard Edition supports the "AWE" feature. I think this obfuscation is caused by knowing that SQL Server of the Standard Edition does not support page locking.

Let's clarify the confusion here:
A. 32-bit Standard Edition SQL Server 2005 and 2008 support AWE features. For any evidence, see http://msdn.microsoft.com/en-us/library/cc645993.aspx. "Dynamic AWE" is listed as the features of the Standard Edition and Enterprise Edition. I also checked our code and found that the logic for "AWE allowed" is based on the Enterprise Edition or Standard Edition.
B. The "locked pages" feature was not supported until recently, as I mentioned earlier. Based on Customer feedback, we modified this in the recent 2005 and 2008 accumulation updates. To enable it in the Standard Edition, you need a trace flag. For more details, see: http://blogs.msdn.com/psssql/archive/2009/05/19/an-update-for-standard-sku-support-for-locked-pages.aspx.

6. How do I know that AWE is enabled on 32-bit SQL Server? How do I know that "Locked Pages" works on 64-bit SQL Server?

You can read the following blog to learn more about the algorithm:
Http://blogs.msdn.com/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx

There is a tip for 32-bit systems. If you set "awe enabled" to 1, but you do not see "Address processing wing Extensions is enabled" in the error log, there are two possibilities: You have not run RECONFIGURE, or the "Lock Pages in Memory" permission is not set. If the permission is not set, the RECONFIGURE Command reports an error:

Msg 5845, Level 16, State 1, Line 1
Address drawing wing Extensions (AWE) requires the 'lock pages in memory 'privilege which is not currently present in the access token of the process.

(At the end of the translation, I found that I still cannot determine whether some professional terms such as "Lock Pages in Memory" are translated in English or Chinese ...)