SQL SERVER Memory Learning Series (i)

　　　

When I recently helped the customer with the issue of publishing subscriptions, I suddenly encountered an issue in which the following errors were reported in the publication subscription, and an error occurred while performing the ALTER TABLE operation:

　

　　　　

The question is weird. What is the relationship between a publish subscription and the CLR? What is MemToLeave memory? Recall learning architecture, I did see MemToLeave memory, but what has been completely can't remember, so pick up the book to relive the pleasure of learning ...

---------------Reprint Please specify the source------------http://www.cnblogs.com/double-K/p/5049417.html--------------------

Let's just take a look at the memory structure of SQL Server:

1. SQL Server memory allocation from the operating system level: Windows and SQL Server are like the same as June and the other applications like Notepad, Windows under the rule of the same time, when the operating system level is memory tight SQL Server is as tense as the memory. So it's also important to understand the memory structure of windows.

• • • Virtual address space
    • Physical memory (physical RAM)
    • Reserved memory (Reserved)
    • Committed memory (Commit RAM)
    • Shared memory
  • Wait a variety of interested can see for yourself

2.SQL Server memory Usage classification:

• • • By application:
      • Database cache: The buffer that holds the data page, the data in the SQL Server database is stored in 8KB for a page, and when the user needs to use this page, SQL Server will transfer the whole into memory. So 8KB is the smallest unit of data access.
      • Various types of Consumer:sql server components, they must request memory to support their work, such as: SQL Server needs to allocate a data structure for each connection, store information about the connection, and allocate input and output buffer pools (instructions sent by the customer/ The result that the instruction obtains, waits for the client to take away), the statement's cache plan, and so on.
      • Thread Memory: SQL allocates 0.5MB of memory for each thread within the process to hold the structure and related information within the thread.
      • Third-party code requests for memory: such as the CLR, linkedsever, COM, and so on, this part of memory is generally not very large, but if a linked server distributed query needs to retrieve a large amount of data from the remote, then this memory will be used very large. so the monitoring of this memory can not be ignored.
    • By Application Method:
      • Reserve a large memory in advance, then use a small piece of commit
      • Request a commit directly from the address space, this way becomes stolen. (The AWE function is invalid, the extended memory can only be used for database cache, others need to be resolved in 2GB)
    • Size By Application:
      • Less than or equal to 8KB (buffer pool)
      • Greater than 8KB (multi-page 2008 formerly known as MemToLeave)

We follow the classification of the size as a description: The application is less than or equal to 8KB these pages are centrally managed, this memory is called buffer pool, the other part of the request is greater than 8KB called multi-page (MemToLeave), MemToLeave a large portion of the memory allocation for this area is not controlled by SQL Server itself, or it can be said to be allocated only within SQL Servers, but is allocated by other DLLs that are loaded inside SQL Server, such as the CLR.

In the 32-bit era, we need to keep the address space we need to configure the MemToLeave size (which includes the worker thread required), and then give the rest to buffer pool.

After the 64-bit era, because the address space can be understood as infinite, SQL Server no longer controls the size of the MemToLeave space, and can even rob the database cache.

Summarize it in a single table:

Type	Database Cache	Consumer	Third-party code	Threads
Reserved/commit	Is	Generally not	Generally not	No
Stolen	No	Is	Is	Is
Buffer pool	All	Most	No	No
MemToLeave	No	A small part	All	All

---------------Reprint Please specify the source------------http://www.cnblogs.com/double-K/p/5049417.html--------------------

Simply explain the memory of SQL, there are a few things to note also simple mention:

1.WINDOWS does not lack memory SQL will not lack of memory it?

A: This is very wrong (1) if it is 32 bits, SQL may not continue to request memory due to virtual address space limitations.  Some people will say I can open awe, the previous article also mentioned AWE is not omnipotent, there are 64 why also choose 32? Right. (2) SQL Server probably set the max server memory constraint on the ability of SQL to continue to request memory, this has been encountered, 256G memory of the server max server memories is set to 2 G.

1. 1. 2. We set the maximum memory of SQL to 20G, why do we sometimes observe that the memory usage of SQL process exceeds 20G? Isn't this useless?

      A: The maximum value of memory-----max server memory settings, which is the size of the buffer pool, does not contain a space of memtoleave, so it is sometimes seen that SQL uses more memory than the set Max Server memory

   2. 3. I am a SQL Server dedicated server so you do not need to set max server memory to SQL Server.
   3. A: This problem is a lot of users encountered problems, seemingly no problem, but ignored a little bit of SQL Server is a very like the memory of the application so it is likely to eat a lot of memory resulting in Windows system does not have enough memory to use, then the monarch relationship is the most vividly, June (Windows To die (free memory) the minister had to die ... This release to a certain extent is not only enough for Windows, it is likely to cause a steep drop of SQL memory, so that SQL short-term animation (operation is unresponsive), generally I recommend operating system 20%, SQL Server 80% if the server has other applications also in the SQL Reduce the amount of memory that the application occupies.

　　　　　　　　　　

---------------Reprint Please specify the source------------http://www.cnblogs.com/double-K/p/5049417.html--------------------

Having said so much about returning to my opening question, why is this situation memtoleave memory shortage? You may already have an answer after reading the article.

I said the steps to troubleshoot the problem, when the customer sent me an error when the first reaction must be to understand the customer's environment, because not remote so I asked the operating system version of the SQL version, patches and memory settings and so on ... Tell me 64-bit Windows SERVER2008, SQL SERVER2008R2, unfortunately the client didn't have a SELECT @ @version confirm the SQL version, and I took it for granted that the 64-bit SQL Server was definitely installed!! After looking through the information on the Internet to find the answer is always wondering why the emergence of this show ... Finally, the customer was asked to confirm the SQL version only to discover that the 64-bit operating system accidentally installed 32-bit SQL SERVER, the problem is immediately clear.

How big is the memory of this 32-bit memtoleave? 256M + 256 (thread) X 0.5MB = 384MB, can be modified by modifying the startup Parameters +-G 1024 (allocate 1024MB) to solve this problem, but the problem is not to modify the startup parameters, this is only the table name phenomenon, the final operation must be to reinstall SQL SERV ER x64!!! Of course, this will have a great impact on the user's application.

---------------Reprint Please specify the source------------http://www.cnblogs.com/double-K/p/5049417.html--------------------

Thinking of this case I suddenly found my architecture so fragile ... Encountered the problem although has seen but did not have the consummation knowledge system to support, therefore prepares to study again the SQL Memory Chapter!! At the same time will be a follow-up study articles!!

Oh, ~ ~ ~

SQL SERVER Memory Learning Series (i)