An error occurred while trying SQL Server 2014 OLTP memory-optimized table

SQL Server 2014 introduces a memory database, which is a very good function. Data can be directly stored in the memory, which can be operated directly on the memory, greatly improving the performance.

Today, I think of a problem. What happens if the table grows bigger and the database memory is insufficient. Because Microsoft's msdns writes the following content:

A computer with enough memory to hold the data in memory-optimized tables. Memory-optimized data must not use more than 80% of the maximum server memory.

That is to say, it should not exceed 80% of the memory of the entire instance, but the growth of the database is sometimes unpredictable. Start my test below:

-- Create database with memory-optimizeddata filegroup

Create Database hekaton_demo

On

Primary (name = [hekaton_demo_data],

Filename = 'C: \ data \ hekaton_demo_data.mdf ', size = 500 MB)

, Filegroup [hekaton_demo_fg] containsmemory_optimized_data (

Name = [hekaton_demo_dir],

Filename = 'C: \ data \ hekaton_demo_dir ')

Log On (name = [hekaton_demo_log], filename = 'C: \ data \ hekaton_demo_log.ldf ', size = 500 MB)

Collate latin1_general_100_bin2;

Go

 

Use hekaton_demo;

Go

 

Create Table destination1
(
-- See the section on bucket_count for more details on setting the bucket count.
Col1 int not null primary key nonclustered hash with (bucket_count = 1000000 ),
Col2 varchar (1000) not null,
Col3 varchar (1000) not null
) With (memory_optimized = on, durability = schema_and_data)
Go

 

An error is reported when you try to multiply bucket_count by 100 times:

 

MSG 701, Level 17, stateworkflow, line 37

There is insufficientsystem memory in resource pool 'default' to run this query.

 

-- Try to insert a large amount of data:

Declare @ int

Set @ Int = 1

While @ int <1000000

Begin

Insert into destination1 values (@ int, replicate ('A', 1000), replicate ('A', 1000 ))

Set @ Int = @ int + 1

End

 

After the result is executed for a period of time, the database connection is directly disconnected. The error message shown in SSMs is:

 

Timeout expired. The timeout period elapsed prior tocompletion of the operation or the server is not responding. (. NET sqlclientdata provider)

 

Database Error Log:

 

MSG 701, Level 17, stateworkflow, line 37

There is insufficientsystem memory in resource pool 'default' to run this query.

 

An error occurred while restarting:

 

13-06-30 18:23:55. 23 logon error: 17188, severity: 16, state: 1.

2013-06-30 18:23:55. 23 logon SQL Server cannot accept newconnections, because it is shutting down. The connection has been closed. [client: <Local Machine>]

 

Restart again:

 

2013-06-30 18:30:40. 98 spid23s error: 41316, severity: 23, state: 4.

2013-06-30 18:30:40. 98 spid23s restore operation failed for database 'hekaton _ demo '.

 

2013-06-30 18:13:18. 08 spid38s error: 802, severity: 17, state: 0.

2013-06-30 18:13:18. 08 spid38s there is insufficient memory available inthe buffer pool.

 

Failed. You cannot directly Delete the database and restart it.

 

I don't know how SQL Server calculates memory when creating a table, but how does it calculate variables? Because the value is unknown. If this problem occurs later when no error is reported during table creation, it is more serious than the performance problem.

 

Go to the document to see how Microsoft explained it.