Talking and exploring In-Memory topics in SQL Server 2014 has been interesting to me. When I wrote the blog around table variable not being just an In-Memory structure, one of my course listener (SQL Server 2014 Administration New Features) pinged me on twitter to ask, if In-Memory OLTP was really In-Memory? Wouldn’t SQL Server like to swap the data or memory data to pagination file when there is memory pressure? I told them the concept of In-Memory is that data always resides in memory and the reason for a feature name “In-Memory OLTP”. Let us see how we can fix errors related to insufficient system memory.
The next question that came out of this interaction, what happens when we hit the memory boundary? Will SQL Server error out even if there is Memory available in the system or do something else. This was an easy setup to try.
We will create an In-Memory database, restrict it to have limited memory and add some rows to see if there is any error. The basic 4 steps to follow are:
-- Step 1: Create the Resource Pool. Limit to 5% memory. CREATE RESOURCE POOL InMemory_Pool WITH (MAX_MEMORY_PERCENT = 5); ALTER RESOURCE GOVERNOR RECONFIGURE; GO -- Step 2: Create the InMemory DB CREATE DATABASE InMemory ON PRIMARY(NAME = InMemoryData, FILENAME = 'd:\data\InMemoryData.mdf', size=100MB), -- Memory Optimized Data FILEGROUP [InMem_FG] CONTAINS MEMORY_OPTIMIZED_DATA( NAME = [InMemory_InMem_dir], FILENAME = 'd:\data\InMemory_InMem_dir') LOG ON (name = [InMem_demo_log], Filename='d:\data\InMemory.ldf', size=50MB) GO -- Step 3: Bind the resource pool to DB EXEC sp_xtp_bind_db_resource_pool 'InMemory', 'InMemory_Pool' GO -- Step 4: For RG to take effect, make DB Offline and Online USE MASTER GO ALTER DATABASE InMemory SET OFFLINE GO ALTER DATABASE InMemory SET ONLINE GO After this we will create a wide table and add rows to exhaust the memory for this resource pool. USE InMemory GO -- Step 5: Create a Memeory Optimized Table CREATE TABLE DummyTable_Mem (ID INT NOT NULL, Name CHAR(8000) NOT NULL CONSTRAINT ID_Clust_DummyTable_Mem PRIMARY KEY NONCLUSTERED HASH (ID) WITH (BUCKET_COUNT=1000000)) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY) GO -- Step 6: Add a lot of rows to get the error SET NOCOUNT ON DECLARE @counter AS INT = 1 WHILE (@counter &lt;= 1000000) BEGIN INSERT INTO dbo.DummyTable_Mem VALUES(@counter, 'SQLAuthority') SET @counter = @counter + 1 END GO
The Step 6 will not complete because of insufficient memory. We will get an error as shown below:
The statement has been terminated. Msg 701, Level 17, State 103, Line 49 There is insufficient system memory in resource pool ‘InMemory_Pool’ to run this query.
To add more rows to the table:
- Make sure more memory is allocated to the resource pool and reconfigure the same.
- Delete some rows from In-Memory tables on this database to make space for new allocations.
Let me help you clean up the script after the experiment.
-- Clean up USE MASTER; DROP DATABASE InMemory GO DROP RESOURCE POOL InMemory_Pool ALTER RESOURCE GOVERNOR RECONFIGURE; GO
I hope this blog post explains how we can fix error related to insufficient system memory.
To learn such interesting SQL Server 2014 enhancements, feel free to listen to my Pluralsight course for more such topics.
Reference: Pinal Dave (https://blog.sqlauthority.com)