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

Exception:

DB: SQL Server2012SP3

OS: Windows2012

The front-line engineer responded that the system access to DB suddenly jammed, DBA login DB check SQLServer found that DB could not log in, and any SQL could not be executed in the session to which SMSS had been connected before. More than 99% of the server CPU uses 10% memory. After ten minutes, the system memory itself has dropped below 90%, and DB can access it.

Analysis:

Check SQLServer error log:

There is only a record of stack dump during an abnormal period, but there is not much valuable information.

Check the windows performance counters:

During the abnormal period, the OS memory is indeed exhausted.

Check windows log:

There is not enough virtual memory warning.

The sqlserver.exe process uses 28GB (the maximum DB memory usage set by DBA), and the dllhost.exe process uses 12GB

Just fill up the server's 32GB + virtual memory set 8GB! So the error is out of memory!

Resolve:

There must be a problem with the use of 12GB in the above dllhost.exe process. This block may be patched at the system level or related to viruses, which has been handed over to the system engineer for analysis.

But is there a way that when OS is out of memory, DBA can be set without affecting the normal operation of SQL server?

The answer is yes. Soft Micro officially provides a locked memory page (LPIM) solution, which uses the Windows policy to determine which accounts can use processes to keep data in physical memory, thus preventing the system from paging data into virtual memory on disk. This will have minimal impact on SQL server even if OS runs out of memory.

Settings:

Add add SQL Server startup account in cmd- > gpedit.msc- > local computer policy- > computer configuration- > windows settings- > security settings- > local policies- > user rights assignment- > Lock pages memory

At this point, the lock memory page (LPIM) setting is complete.

After continuous observation for a period of time, there is no problem of insufficient SQLServer hang memory in the system.

-

The following is attached, soft micro official documentation description:

Https://docs.microsoft.com/zh-cn/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver15

Lock memory pages (LPIM)

This Windows policy determines which accounts can use processes to keep data in physical memory, thereby preventing the system from paging data into virtual memory on disk. Locking pages in memory keeps the server responsive when paging memory to disk occurs. When the Windows locked memory Page (LPIM) user right has been granted to an account that has permission to run sqlservr.exe, the Lock memory Page option is set to Open in SQL Server Standard Edition and later instances.

To disable the Lock memory Page option for SQL Server, remove the Lock memory Page user right for the account that has the right to run the sqlservr.exe (SQL Server startup account) startup account.

Setting this option enables you to expand or shrink memory at the request of other memory allocators without affecting SQL Server dynamic memory management. When using the Lock memory pages user right, it is recommended that you set an upper limit for max server memory as described above.

important

This option should be set only when necessary, when there are signs that the sqlservr process is being swapped out. In this case, the error log reports error 17890, similar to the following example: A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: # seconds. Working set (KB): #, committed (KB): #, memory utilization: # #%. Starting with SQL Server 2012 (11.x), Standard Edition does not need trace flag 845 to use "locked pages".

Enable Lock memory Page

Enable the Lock memory Page option:

On the start menu, click run. In the Open box, type gpedit.msc

The Group Policy dialog box opens.

On the Group Policy console, expand computer configuration, and then expand Windows Settings.

Expand Security Settings, and then expand Local policies.

Select the user Rights assignments folder.

The policy appears in the details pane.

In this pane, double-click Lock memory Page.

In the Local Security Policy Settings dialog box, add an account that has permission to run sqlservr.exe (SQL Server Startup account).

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.