July, 2017 | Ned Otter Blog

As part of a conversation on the #sqlhelp, I was giving feedback to a member of the Microsoft Tiger team about how In-Memory OLTP might be improved. One of my responses was about implementing a hot/cold data solution, and Adam Machanic (b|t) responded, suggesting that partitioned views might work. I had already tested partitioned views and memory-optimized tables for queries, and confirmed that it worked as expected, but I had never tested DML for this combination of features.

Based on my new testing, I can state with confidence that partitioned view support for DML with memory-optimized tables will properly handle INSERT and DELETE operations, but some UPDATE operations might fail, and that failure occurs in a most ungraceful way. After verifying that this is an issue on SQL 2016 SP1/CU3 and SQL 2017 CTP2.1, I filed this Connect item. Microsoft has confirmed this issue, but has not yet determined how it will be fixed. For example, they could decide to disallow all UPDATEs when a memory-optimized table belongs to a partitioned view, or instead decide to support it under limited circumstances. I’ll update this post when I have more detail from Microsoft.

Let’s assume that we have two tables that belong to a partitioned view. Both tables can be memory-optimized, or one table can be memory-optimized, and the other on-disk.

Success condition

an UPDATE occurs to a row in a table, and the UPDATE does not change where the row would reside, i.e. does not cause it to “move” to another table, based on defined CONSTRAINTS

Failure conditions:

a. UPDATE occurs to a row in the memory-optimized table that causes it to move to either another memory-optimized table, or a on-disk table

b. UPDATE occurs to a row in the on-disk table that causes it to move to the memory-optimized table

The failure looks like this:

Repro script:

/*
	Repro for UPDATEs on a partitioned view
*/

SET NOCOUNT ON 

DROP VIEW IF EXISTS dbo.vwPartitioned;
GO
DROP TABLE IF EXISTS dbo.Table1;
GO
DROP TABLE IF EXISTS dbo.Table2;
GO

CREATE TABLE dbo.Table1
(
    TxDate DATE
   ,OrderID INT 
   CONSTRAINT PK_Table1 PRIMARY KEY NONCLUSTERED (TxDate, OrderID)
)
--WITH (DURABILITY = SCHEMA_AND_DATA, MEMORY_OPTIMIZED = ON);
GO
CREATE TABLE dbo.Table2
(
    TxDate DATE
   ,OrderID INT
   CONSTRAINT PK_Table2 PRIMARY KEY NONCLUSTERED (TxDate, OrderID)
)
WITH (DURABILITY = SCHEMA_AND_DATA, MEMORY_OPTIMIZED = ON);
GO

INSERT dbo.Table1
(
    TxDate
   ,OrderID
)
VALUES
 ('2014-01-01', 1)
,('2014-01-10', 2)
,('2014-03-15', 3)
,('2014-07-20', 4);

INSERT dbo.Table2
(
    TxDate
   ,OrderID
)
VALUES
 ('2015-01-07', 5)
,('2015-02-10', 6)
,('2015-03-18', 7)
,('2015-10-28', 8);
GO

CREATE VIEW dbo.vwPartitioned
WITH SCHEMABINDING AS
(
	SELECT
	    TxDate
	   ,OrderID
	FROM dbo.Table1
	UNION ALL
	SELECT
	    TxDate
	   ,OrderID
	FROM dbo.Table2
);
GO
SET STATISTICS IO ON;
GO

ALTER TABLE dbo.Table2 
ADD CONSTRAINT CC_Table2_TxDate CHECK (TxDate &gt;= '2015-01-01');
GO
ALTER TABLE dbo.Table1 
ADD CONSTRAINT CC_Table1_TxDate CHECK (TxDate &lt; '2015-01-01');
GO

ALTER VIEW dbo.vwPartitioned
WITH SCHEMABINDING AS
(
	SELECT
	    TxDate
	   ,OrderID
	FROM dbo.Table1
	UNION ALL
	SELECT
	    TxDate
	   ,OrderID
	FROM dbo.Table2
);
GO

INSERT dbo.vwPartitioned
(
    TxDate
   ,OrderID
)
VALUES
 ('2014-01-13', 91)
,('2015-01-08', 85);
GO
UPDATE dbo.vwPartitioned 
SET OrderID = 2
WHERE TxDate = '2014-01-13';
GO
DELETE dbo.vwPartitioned
WHERE TxDate = '2014-01-13';
GO
-- check location of rows
SELECT *
FROM dbo.Table2
ORDER BY OrderID
GO
SELECT *
FROM dbo.Table1
ORDER BY OrderID
GO

-- This UPDATE succeeds, because it does NOT attempt to "move" the row to another table
UPDATE dbo.vwPartitioned
SET OrderID = 9999
WHERE TxDate = '2015-01-07';
GO

-- This UPDATE fails with a stack dump, due to row "movement"
UPDATE dbo.vwPartitioned
SET TxDate = '2015-01-01'
WHERE TxDate = '2014-01-01';
GO

-- This UPDATE fails with a stack dump, due to row "movement"
UPDATE dbo.vwPartitioned
SET TxDate = '2014-01-01'
WHERE OrderID = 9999;
GO

-- check location of rows
SELECT *
FROM dbo.Table2
ORDER BY OrderID
GO
SELECT *
FROM dbo.Table1
ORDER BY OrderID

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

Repro for UPDATEs on a partitioned view

SET NOCOUNT ON

DROP VIEW IF EXISTS dbo.vwPartitioned;

DROP TABLE IF EXISTS dbo.Table1;

DROP TABLE IF EXISTS dbo.Table2;

CREATE TABLE dbo.Table1

(

TxDate DATE

,OrderID INT

CONSTRAINT PK_Table1 PRIMARY KEY NONCLUSTERED (TxDate, OrderID)

)

--WITH (DURABILITY = SCHEMA_AND_DATA, MEMORY_OPTIMIZED = ON);

CREATE TABLE dbo.Table2

(

TxDate DATE

,OrderID INT

CONSTRAINT PK_Table2 PRIMARY KEY NONCLUSTERED (TxDate, OrderID)

)

WITH (DURABILITY = SCHEMA_AND_DATA, MEMORY_OPTIMIZED = ON);

INSERT dbo.Table1

(

TxDate

,OrderID

)

VALUES

('2014-01-01', 1)

,('2014-01-10', 2)

,('2014-03-15', 3)

,('2014-07-20', 4);

INSERT dbo.Table2

(

TxDate

,OrderID

)

VALUES

('2015-01-07', 5)

,('2015-02-10', 6)

,('2015-03-18', 7)

,('2015-10-28', 8);

CREATE VIEW dbo.vwPartitioned

WITH SCHEMABINDING AS

(

SELECT

TxDate

,OrderID

FROM dbo.Table1

UNION ALL

SELECT

TxDate

,OrderID

FROM dbo.Table2

);

SET STATISTICS IO ON;

ALTER TABLE dbo.Table2

ADD CONSTRAINT CC_Table2_TxDate CHECK (TxDate >= '2015-01-01');

ALTER TABLE dbo.Table1

ADD CONSTRAINT CC_Table1_TxDate CHECK (TxDate < '2015-01-01');

ALTER VIEW dbo.vwPartitioned

WITH SCHEMABINDING AS

(

SELECT

TxDate

,OrderID

FROM dbo.Table1

UNION ALL

SELECT

TxDate

,OrderID

FROM dbo.Table2

);

INSERT dbo.vwPartitioned

(

TxDate

,OrderID

)

VALUES

('2014-01-13', 91)

,('2015-01-08', 85);

UPDATE dbo.vwPartitioned

SET OrderID = 2

WHERE TxDate = '2014-01-13';

DELETE dbo.vwPartitioned

WHERE TxDate = '2014-01-13';

-- check location of rows

SELECT *

FROM dbo.Table2

ORDER BY OrderID

SELECT *

FROM dbo.Table1

ORDER BY OrderID

-- This UPDATE succeeds, because it does NOT attempt to "move" the row to another table

UPDATE dbo.vwPartitioned

SET OrderID = 9999

WHERE TxDate = '2015-01-07';

-- This UPDATE fails with a stack dump, due to row "movement"

UPDATE dbo.vwPartitioned

SET TxDate = '2015-01-01'

WHERE TxDate = '2014-01-01';

-- This UPDATE fails with a stack dump, due to row "movement"

UPDATE dbo.vwPartitioned

SET TxDate = '2014-01-01'

WHERE OrderID = 9999;

-- check location of rows

SELECT *

FROM dbo.Table2

ORDER BY OrderID

SELECT *

FROM dbo.Table1

ORDER BY OrderID

Those who have studied In-Memory OLTP are aware that in the event of “database restart”, durable memory-optimized data must be streamed from disk to memory. But that’s not the only time data must be streamed, and the complete set of events that cause this is not intuitive. To be clear, if your database had to stream data back to memory, that means all your memory-optimized data was cleared from memory. The amount of time it takes to do this depends on:

the amount of data that must be streamed
the number of indexes that must be rebuilt
the number of containers in the memory-optimized database, and how many volumes they’re spread across
how many indexes must be recreated (SQL 2017 has a much faster index rebuild process, see below)
the number of LOB columns
BUCKET count being properly configured for HASH indexes

The following list is for standalone SQL Server instances (under some circumstances, the streaming requirements are different between FCIs and AGs).

Database RESTORE – this is a no brainer – if you restore a database with durable memory-optimized data, of course your data must be streamed from disk into memory. And if you are under the impression that SQL Server verifies if your server has enough memory to complete the RESTORE, you would be mistaken. See my post here.

SQL Service restart – in this case, all databases will go through the recovery process, and all memory-optimized databases will stream durable memory-optimized data to memory.

Server reboot – same as “SQL Service restart”

In addition to the list above, there are a few database settings that cause data to be streamed.

Changing a database from READ_ONLY to READ_WRITE, or from READ_WRITE to READ_ONLY
Setting READ_COMMITTED_SNAPSHOT OFF or ON
Taking a database OFFLINE and then ONLINE

A database that contains durable memory-optimized data will not be online until all memory-optimized data is finished streaming, which affects the availability of “traditional” tables (while a database is waiting for streaming to complete, the wait type is “WAIT_XTP_RECOVERY”). There’s nothing you can do to speed up the process, other than having initially defined enough containers on enough volumes, so that streaming executes in parallel.

SQL 2017 enhancements

Because modifications to HASH and NONCLUSTERED/RANGE indexes are not logged for memory-optimized tables, they must be rebuilt when data is streamed to memory. Both SQL 2014 and SQL 2016 have a limit of 8 NONCLUSTERED indexes per memory-optimized table (any combination of HASH and RANGE). Microsoft has designed a new process for enhancing index rebuild speed in SQL 2017. This dovetails perfectly with the removal of the 8-indexes-per-table limit in SQL 2017 (I have personally created a table with 298 NONCLUSTERED indexes in SQL 2017).

Ned Otter Blog

SQL Server DBA and Musician

Monthly Archives: July 2017

DML for memory-optimized tables in partitioned views

Which events cause durable memory-optimized data to be streamed to memory?