Difference between Lock and Latch

Locks everywhere is a good start to understand how SQL Server provides logical consistency. Every operation has a lock and latch.

What does Latch mean? Latch protects memory on Buffer Pool, is a method that provides physical consistency.

SQL Server does operations in memory, that means, it read the page from disk and put that page on buffer pool to work there. If someone tries to update the data, the page is changed in memory and SQL Server writes the change in the transaction log file. (More about logging)

Basically, Latches are physical locks and hold the lock only for the duration of the physical operation, while the Locks are logical and maintain the lock until the transaction finishes. Both types guarantee data consistency.

LATCH

There is insufficient system memory in resource pool

Doing crash and recovery tests on my local machine I got the SQL Server instance not going online. After trying the third time to bring my instance online thinking was something else problem I saw the SQL Server errolog file and I could see the problem.

Not enough memory, but wasn’t on my machine, was in the resource pool. So, what is a resource pool?

A resource pool represents a subset of the physical resources of an instance of the Database Engine and in my case was insufficient memory. Let’s see the errorlog file:

2018-03-14 16:19:58.09 spid56s     [ERROR] Recovery failed with error 0x83000000 on database 18. This error will be mapped to 'HK_E_RESTORE_INSUFFICIENT_MEMORY' (0x8200002e). (sql\ntdbms\hekaton\runtime\src\hkruntime.cpp : 4805 - 'HkRtRestoreDatabase')
2018-03-14 16:19:58.09 spid34s     [INFO] HkCkptCtrlUninitialize(): Database ID: [18]. Cleaning up StorageArray. LastClosedCheckpointEndTs: '158'
2018-03-14 16:19:58.09 Server      Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2018-03-14 16:19:58.09 Server      Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2018-03-14 16:19:58.09 Server      Error: 28709, Severity: 16, State: 19. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2018-03-14 16:19:58.09 spid37s     Error: 701, Severity: 17, State: 137.
2018-03-14 16:19:58.09 spid37s     There is insufficient system memory in resource pool 'default' to run this query.
2018-03-14 16:19:58.09 spid39s     Error: 701, Severity: 17, State: 137.
2018-03-14 16:19:58.09 spid39s     There is insufficient system memory in resource pool 'default' to run this query.
2018-03-14 16:19:58.11 spid55s     [ERROR] Recovery failed with error 0x83000000 on database 15. This error will be mapped to 'HK_E_RESTORE_INSUFFICIENT_MEMORY' (0x8200002e). (sql\ntdbms\hekaton\runtime\src\hkruntime.cpp : 4805 - 'HkRtRestoreDatabase')
2018-03-14 16:19:58.11 spid31s     [INFO] HkCkptCtrlUninitialize(): Database ID: [15]. Cleaning up StorageArray. LastClosedCheckpointEndTs: '155'
2018-03-14 16:19:58.20 spid31s     SQL Server shutdown has been initiated
2018-03-14 16:19:58.21 spid31s     Error: 19032, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2018-03-14 16:19:58.28 spid34s     SQL Server shutdown has been initiated

After starting the service SQL Server was doing the redo and undo process, this means it was reading the log files, create the compensate log records if was found any uncommitted transaction.

SQL Server will need memory in buffer pool to complete the redo and undo process and I didn’t remember I changed any SQL Server memory configuration.

So, my approach was to connect SQL Server via command line while the instance was still up and run sp_configure to see how much memory was configured. I got only 512mb set for Max Server Memory and that was the problem. (Max server memory controls the SQL Server memory allocation, compile memory, all caches (including the buffer pool), query execution memory grants, lock manager memory, and CLR memory).

In my environment with 26 databases and my crash recovery tests, 512mb for my pool memory wasn’t enough and when I changed the configuration to 4096mb I could bring the instance online again.

Conclusion

First, read the errorlog file to have more information what SQL Server is doing and also know transaction log operations, log records, checkpoints and how crash  recovery works is fundamental.

SQL Server as a process acquires more memory than specified by max server memory 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 SQL Server.