SQLWorkbooks_01_Isolation-and-Memory-Optimized-Tables.sql

/*****************************************************************************
Copyright (c) 2017 SQL Workbooks LLC
Terms of Use: https://sqlworkbooks.com/terms-of-service/
Contact: help@sqlworkbooks.com

Setup:
    Download BabbyNames.bak.zip (41 MB zipped database backup)
    https://github.com/LitKnd/BabbyNames/releases/tag/v1.1

Then review and run the script below on a SQL Server 2017 dedicated test instance
    Developer Edition recommended (Enteprise and Evaluation Editions will work too)

The script
    Restores the database (edit the file locations for your instance)
    Duration on my test instance (4 vCPUs, SSDs): ~5 minutes
    

*****************************************************************************/

SET XACT_ABORT, NOCOUNT, ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE
GO

EXEC sp_configure 'max degree of parallelism', 4;
GO

EXEC sp_configure 'cost threshold for parallelism', 100
GO

EXEC sp_configure 'max server memory (MB)', 4000;
GO

RECONFIGURE
GO


/****************************************************
Restore small BabbyNames database
****************************************************/
USE master;
GO

IF DB_ID('BabbyNamesXTP') IS NOT NULL 
BEGIN
    IF (SELECT state_desc FROM sys.databases WHERE name='BabbyNamesXTP') = 'ONLINE'
    ALTER DATABASE BabbyNamesXTP
        SET SINGLE_USER
        WITH ROLLBACK IMMEDIATE;
END
GO

RESTORE DATABASE BabbyNamesXTP
    FROM DISK=N'S:\MSSQL\Backup\BabbyNames.bak'
    WITH
        MOVE 'BabbyNames' TO 'S:\MSSQL\DATA\BabbyNamesXTP.mdf',
        MOVE 'BabbyNames_log' TO 'S:\MSSQL\DATA\BabbyNamesXTP_log.ldf',
        REPLACE,
        RECOVERY;
GO

ALTER DATABASE BabbyNamesXTP SET RECOVERY SIMPLE;
GO



/****************************************************
Enable Memory Optimized Tables
****************************************************/

ALTER DATABASE BabbyNamesXTP ADD FILEGROUP MemoryOptimizedData CONTAINS MEMORY_OPTIMIZED_DATA;
GO

ALTER DATABASE BabbyNamesXTP 
    ADD FILE( NAME = 'MemoryOptimizedData' , FILENAME = 'S:\MSSQL\Data\BabbyNamesXTP_MemoryOptimizedData') 
    TO FILEGROUP MemoryOptimizedData;  
GO



/****************************************************
Configure Resource Governor
This is an Enterprise Edition feature
It's important for critical workloads for in-memory tables
****************************************************/
USE master;
GO

IF 0 = (
    SELECT COUNT(*)
    FROM sys.resource_governor_resource_pools
    WHERE name = 'BabbyNamesXTP_Pool'
)
BEGIN 
    EXEC ('
        CREATE RESOURCE POOL BabbyNamesXTP_Pool
          WITH ( MIN_MEMORY_PERCENT = 1, MAX_MEMORY_PERCENT = 50 );  

        ALTER RESOURCE GOVERNOR RECONFIGURE;  

        EXEC sp_xtp_bind_db_resource_pool ''BabbyNamesXTP'', ''BabbyNamesXTP_Pool'' 
        '
    );
    /* This is required to make the binding take effect */
    EXEC ('

        ALTER DATABASE BabbyNamesXTP
            SET OFFLINE
            WITH ROLLBACK IMMEDIATE;

        ALTER DATABASE BabbyNamesXTP
            SET ONLINE;

    ')
END
GO

SELECT 
    pool_id,  
    name, 
    min_memory_percent,  
    max_memory_percent,
    used_memory_kb/1024. AS used_memory_MB,
    max_memory_kb/1024. AS max_memory_MB,
    target_memory_kb/1024. AS target_memory_MB  
FROM sys.dm_resource_governor_resource_pools;
GO

SELECT 
    physical_memory_kb/1024. as physical_memory_MB,
    committed_kb/1024. as committed_MB,
    committed_target_kb/1024. as committed_target_MB
from sys.dm_os_sys_info;
GO


/****************************************************
Create and populate some tables
****************************************************/

USE BabbyNamesXTP
GO

CREATE TABLE ref.FirstNameXTP(
	FirstNameId int IDENTITY(1,1) NOT NULL,
	FirstName varchar(255) NOT NULL,
	NameLength  AS (len(FirstName)),
	FirstReportYear int NOT NULL,
	LastReportYear int NOT NULL,
	TotalNameCount bigint NOT NULL,
    CONSTRAINT pk_ref_FirstNameXTP PRIMARY KEY NONCLUSTERED (FirstNameId),
    CONSTRAINT uq_ref_FirstNameXTP_FirstName UNIQUE NONCLUSTERED (FirstName)
) 
WITH (MEMORY_OPTIMIZED = ON);
GO

CREATE TABLE agg.FirstNameByYearStateXTP (
	ReportYear int NOT NULL,
	StateCode char(2) NOT NULL,
	FirstNameId int NOT NULL,
	Gender char(1) NOT NULL,
	NameCount int NOT NULL,
    CONSTRAINT pk_agg_FirstNameByYearStateXTP 
        PRIMARY KEY NONCLUSTERED (ReportYear ASC, StateCode ASC, FirstNameId ASC, Gender ASC)
) 
WITH (MEMORY_OPTIMIZED = ON);
GO

SET IDENTITY_INSERT ref.FirstNameXTP ON;  
GO

INSERT ref.FirstNameXTP ([FirstNameId], [FirstName], [FirstReportYear], [LastReportYear], [TotalNameCount])
SELECT [FirstNameId], [FirstName], [FirstReportYear], [LastReportYear], [TotalNameCount]
FROM ref.FirstName;
GO

SET IDENTITY_INSERT ref.FirstNameXTP OFF;  
GO

INSERT [agg].[FirstNameByYearStateXTP] ([ReportYear], [StateCode], [FirstNameId], [Gender], [NameCount])
SELECT [ReportYear], [StateCode], [FirstNameId], [Gender], [NameCount]
FROM agg.FirstNameByYearState;
GO

/* Foreign key time! */
ALTER TABLE agg.FirstNameByYearStateXTP  
    WITH CHECK 
    ADD CONSTRAINT fk_FirstNameByYearStateXTP_FirstName 
    FOREIGN KEY(FirstNameId)
REFERENCES ref.FirstNameXTP (FirstNameId);
GO

/* How much memory are we using for the XTP tables? */
SELECT 
    pool_id,  
    name, 
    min_memory_percent,  
    max_memory_percent,
    used_memory_kb/1024. AS used_memory_MB,
    max_memory_kb/1024. AS max_memory_MB,
    target_memory_kb/1024. AS target_memory_MB  
FROM sys.dm_resource_governor_resource_pools;
GO



/*****************************************************
Isolation Demos...

Note: This demo explores isolation levels for In-Memory OLTP through
interpreted TSQL (not natively compiled procedures).

This is a demo of "cross-container" transactions.
More info: https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/transactions-with-memory-optimized-tables#cross-container-transaction
*****************************************************/

/* Compare these statements */
--#1 Autocommit transaction
SELECT COUNT(*) AS NameCount
FROM ref.FirstNameXTP;
GO

--#2 Explicit transaction
BEGIN TRAN
    SELECT COUNT(*) AS NameCount
    FROM ref.FirstNameXTP;
COMMIT
GO

--#3 Implicit transaction
SET IMPLICIT_TRANSACTIONS ON;
GO
SELECT COUNT(*) AS NameCount
FROM ref.FirstNameXTP;
GO
SET IMPLICIT_TRANSACTIONS OFF;
GO



/*****************************************************
AUTO COMMIT TRANSACTIONS
*****************************************************/

SELECT 
    snapshot_isolation_state_desc, 
    is_read_committed_snapshot_on, 
    is_memory_optimized_elevate_to_snapshot_on
FROM sys.databases
WHERE name='BabbyNamesXTP';
GO


/* What transaction isolation level does this auto-commit transaction use?
While this is running, run from another session:

exec sp_WhoIsActive @get_additional_info = 1
*/
declare @foo int
WHILE 1=1
    SELECT @foo=COUNT(*)
    FROM ref.FirstNameXTP;
GO



/* Interesting.

Can we see the effects of snapshot?
 */



/* Uncomment and run this in another session ... */
--USE BabbyNamesXTP;
--GO
--SET NOCOUNT ON;
--GO
--UPDATE ref.FirstNameXTP SET FirstName='ZZZaaban' WHERE FirstName='Aaban';

--UPDATE ref.FirstNameXTP SET FirstName='Aaban' WHERE FirstName='ZZZaaban';

--GO 100000


DROP TABLE IF EXISTS dbo.NameCount;
GO
CREATE TABLE dbo.NameCount ( NameCount int);
GO

DECLARE @i int = 1
WHILE @i <= 2000
BEGIN
    INSERT dbo.NameCount (NameCount)
    SELECT COUNT(*) AS NameCount
    FROM ref.FirstNameXTP WITH (INDEX([uq_ref_FirstNameXTP_FirstName]));

    SET @i = @i + 1;
END
GO

/* How many names did we count? 
Reminder: there were ALWAYS 95,025 names, we were just updating the name value.
Under read committed for a disk based nonclustered index, we'll see both low and high counts.

But this is NOT a disk based index! It's structured VERY differently -- no 8KB pages,
no moving rows on updates*/
SELECT NameCount, 
    COUNT(*) as NumberCounted
FROM dbo.NameCount
GROUP BY NameCount
ORDER BY 1;
GO




/*****************************************************
EXPLICIT TRANSACTIONS & ESCALATION
*****************************************************/

SELECT 
    snapshot_isolation_state_desc, 
    is_read_committed_snapshot_on, 
    is_memory_optimized_elevate_to_snapshot_on
FROM sys.databases
WHERE name='BabbyNamesXTP';
GO

BEGIN TRAN
    SELECT COUNT(*) AS NameCount
    FROM ref.FirstNameXTP;
COMMIT
GO

BEGIN TRAN
    SELECT COUNT(*) AS NameCount
    FROM ref.FirstNameXTP WITH (SNAPSHOT);
COMMIT
GO

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO
BEGIN TRAN
    SELECT COUNT(*) AS NameCount
    FROM ref.FirstNameXTP;
COMMIT
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

/* Kinda confusing, right? It wants the HINT, not the session level */

ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ON;
GO

SELECT 
    snapshot_isolation_state_desc, 
    is_read_committed_snapshot_on, 
    is_memory_optimized_elevate_to_snapshot_on
FROM sys.databases
WHERE name='BabbyNamesXTP';
GO

BEGIN TRAN
    SELECT COUNT(*) AS NameCount
    FROM ref.FirstNameXTP /* Escalation adds the table hint: WITH (SNAPSHOT) */
    ;
COMMIT
GO



/*****************************************************
Snapshot isolation
Error 41302
Uncommittable transaction
*****************************************************/

/* This example is just for illustrating isolation levels,
NOT to show anything about data modeling or optimal TSQL  ;)  */
IF OBJECT_ID('SeatAssignmentsXTP') IS NOT NULL
    EXEC ('ALTER TABLE dbo.SeatAssignmentsXTP SET ( SYSTEM_VERSIONING = OFF )')
GO
DROP TABLE IF EXISTS dbo.SeatAssignmentsXTP;
GO
DROP TABLE IF EXISTS dbo.SeatAssignmentsXTPHistory;
GO

/* In memory tables can be temporal tables. 
The history table is disk based: https://docs.microsoft.com/en-us/sql/relational-databases/tables/system-versioned-temporal-tables-with-memory-optimized-tables
*/
CREATE TABLE dbo.SeatAssignmentsXTP (
    SeatId INT IDENTITY,
    Seat varchar(5) NOT NULL,
    AssignedTo VARCHAR(256) NULL,
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime),
    CONSTRAINT pk_SeatAssignmentsXTP_SeatId PRIMARY KEY NONCLUSTERED (SeatId)
) WITH 
    (SYSTEM_VERSIONING = ON, 
    MEMORY_OPTIMIZED = ON);
GO


/* Our plane has 1000 seats */
INSERT dbo.SeatAssignmentsXTP (Seat, AssignedTo)
SELECT TOP 1000
    CAST(ROW_NUMBER() OVER (ORDER BY fn1.ReportYear) as varchar(4)) + seats.cha,
    NULL
FROM agg.FirstNameByYear AS fn1
CROSS JOIN (VALUES  ('A'),('B'),('C'),('D') ) as seats(cha)
GO

/* Assign all the seats but one */
UPDATE dbo.SeatAssignmentsXTP
SET AssignedTo = 'Someone else'
WHERE Seat <> '1A';
GO


/* Here's the seats... only one left!
Yay it's in the FRONT!*/
SELECT *
FROM dbo.SeatAssignmentsXTP
ORDER BY SeatId;
GO


/* Let's get that seat!

This is an explicit transaction using snapshot isolation via "auto escalate"

This is not a natively compiled stored procedure-- assume we have to
access disk-based tables somewhere in the procedure 
*/
CREATE OR ALTER PROCEDURE dbo.GetMySeat
    @Seat CHAR(2), 
    @Assignee VARCHAR(256)
AS
    BEGIN TRAN

        UPDATE dbo.SeatAssignmentsXTP
        SET AssignedTo = @Assignee
        WHERE Seat = @Seat
            AND AssignedTo IS NULL
 
        /* take care of regulatory requirements, fly to mars, etc */

    COMMIT
GO


/* In this session, I start to reserve my seat. FIRST CLASS! YESSSSSS! */

/* Step into this with the debugger. 
Stop on the COMMIT */
exec dbo.GetMySeat @Seat='1A', @Assignee='Kendar';
GO


/* Start up another SSMS app instance and run this
Nanners is also reserving a seat, and she tries for 1A too ... */
--USE BabbyNamesXTP;
--GO
--exec dbo.GetMySeat @Seat='1A', @Assignee='Nanners';
--GO



/* Finish up in the debugger.*/

/* Who got the seat?*/
SELECT *
FROM dbo.SeatAssignmentsXTP
ORDER BY SeatId;
GO


/* Seat assignment history of seat 1A, through the magic of temporal tables ... */
SELECT Seat, AssignedTo, SysStartTime, SysEndTime
FROM dbo.SeatAssignmentsXTP FOR SYSTEM_TIME ALL  /* <-- yeah, temporal table! */
WHERE Seat = '1A'
ORDER BY [SysStartTime] ASC;
GO



/* Note: this code does not check if it updated a row
and handle it if 0 rows were updated... or do any other error handling :D */

IF OBJECT_ID('SeatAssignmentsXTP') IS NOT NULL
    EXEC ('ALTER TABLE dbo.SeatAssignmentsXTP SET ( SYSTEM_VERSIONING = OFF )')
GO
DROP TABLE IF EXISTS dbo.SeatAssignmentsXTP;
GO
DROP TABLE IF EXISTS dbo.SeatAssignmentsXTPHistory;
GO


/*****************************************************
SNAPSHOT and multi-statement transactions
*****************************************************/

DROP TABLE IF EXISTS dbo.LemonadeStandRevenueXTP;
GO

/* Look, we expect to sell a LOT of lemonade.
We're franchising to a lot of babies!
*/
CREATE TABLE dbo.LemonadeStandRevenueXTP (
    RevenueDate Date NOT NULL,
    RevenueAmount NUMERIC(10,3),
    CONSTRAINT pk_LemonadeStandRevenueXTP PRIMARY KEY NONCLUSTERED (RevenueDate)
) WITH 
    ( MEMORY_OPTIMIZED = ON);
GO

INSERT dbo.LemonadeStandRevenueXTP (RevenueDate, RevenueAmount)
VALUES 
    ('2015-01-01', 5 ),
    ('2016-01-01', 10 ),
    ('2017-01-01', 15 );
GO

/* We've got a report on our lemonade stand,
it has a summary query at the top and a detail query at the bottom. */
BEGIN TRAN
    SELECT SUM (RevenueAmount) as TotalRevenue
    FROM LemonadeStandRevenueXTP;

    /* In another session, uncomment and run:
    --USE BabbyNamesXTP;
    --GO

    -- UPDATE dbo.LemonadeStandRevenueXTP
    -- SET RevenueAmount = 100
    -- WHERE RevenueDate = '2017-01-01'

    --INSERT dbo.LemonadeStandRevenueXTP (RevenueDate, RevenueAmount)
    --VALUES ( '2014-01-01', 1 )
    */

    SELECT 
        RevenueDate, 
        RevenueAmount
    FROM LemonadeStandRevenueXTP;
COMMIT


/* We were automatically elevated to SNAPSHOT for both reads,
So we saw data from the first time data access was done in the 
transaction.

We did NOT see the update or the insert.

This might be what we want in some cases.

But that update was committed before our report was done.
What if we want to make sure that we have the most current data 
at the time of commit?
*/



/*****************************************************
REPEATABLE READ
*****************************************************/

--Reset the table
TRUNCATE TABLE dbo.LemonadeStandRevenueXTP;
GO

--LOL!

DELETE FROM dbo.LemonadeStandRevenueXTP;
GO

INSERT dbo.LemonadeStandRevenueXTP (RevenueDate, RevenueAmount)
VALUES 
    ('2015-01-01', 5 ),
    ('2016-01-01', 10 ),
    ('2017-01-01', 15 );
GO

/* Same report, now with repeatable read hint on the first statement */
BEGIN TRAN
    SELECT SUM (RevenueAmount) as TotalRevenue
    FROM LemonadeStandRevenueXTP WITH (REPEATABLEREAD);

    /* In another session, uncomment and run:
    --USE BabbyNamesXTP;
    --GO

    -- UPDATE dbo.LemonadeStandRevenueXTP
    -- SET RevenueAmount = 100
    -- WHERE RevenueDate = '2017-01-01'

    --INSERT dbo.LemonadeStandRevenueXTP (RevenueDate, RevenueAmount)
    --VALUES ( '2014-01-01', 1 )
    */

    SELECT 
        RevenueDate, 
        RevenueAmount
    FROM LemonadeStandRevenueXTP;
COMMIT

/* This error at commit time is only due to the UPDATE statement.


If you only run the INSERT statement in the second session,
this transaction completes successfully.

That's because the INSERT is a "phantom row".

Repeatable Read only guarantees that the rows we read in the first statement haven't changed.

It does NOT guarantee that new rows haven't been inserted.

For that we need....

*/


/*****************************************************
SERIALIZABLE
*****************************************************/

DELETE FROM dbo.LemonadeStandRevenueXTP;
GO

INSERT dbo.LemonadeStandRevenueXTP (RevenueDate, RevenueAmount)
VALUES 
    ('2015-01-01', 5 ),
    ('2016-01-01', 10 ),
    ('2017-01-01', 15 );
GO

/* Same report, now with serializable hint on the first statement */
BEGIN TRAN
    SELECT SUM (RevenueAmount) as TotalRevenue
    FROM LemonadeStandRevenueXTP WITH (SERIALIZABLE);

    /* In another session, uncomment and run:
    --USE BabbyNamesXTP;
    --GO

    --INSERT dbo.LemonadeStandRevenueXTP (RevenueDate, RevenueAmount)
    --VALUES ( '2014-01-01', 1 )
    */

    SELECT 
        RevenueDate, 
        RevenueAmount
    FROM LemonadeStandRevenueXTP WITH (SERIALIZABLE);;
COMMIT




/*****************************************************
BONUS: NOLOCK
This hint is allowed.

I KNOW, RIGHT? What's up with that???
What does it do?
*****************************************************/

DELETE FROM dbo.LemonadeStandRevenueXTP;
GO

INSERT dbo.LemonadeStandRevenueXTP (RevenueDate, RevenueAmount)
VALUES 
    ('2015-01-01', 5 ),
    ('2016-01-01', 10 ),
    ('2017-01-01', 15 );
GO

/* Same report, now with serializable hint on the first statement */
BEGIN TRAN
    SELECT SUM (RevenueAmount) as TotalRevenue
    FROM LemonadeStandRevenueXTP;

    /* In another session, uncomment and run:
    --USE BabbyNamesXTP;
    --GO

    --INSERT dbo.LemonadeStandRevenueXTP (RevenueDate, RevenueAmount)
    --VALUES ( '2014-01-01', 1 )
    */

    SELECT 
        RevenueDate, 
        RevenueAmount
    FROM LemonadeStandRevenueXTP WITH (NOLOCK);;
COMMIT


--Although the NOLOCK hint is allowed, it doesn't do dirty reads against an
--in-memory table. The hint is ignored.


--This is kind of a joke, maybe? In-memory tables don't use locks.
--Maybe practically it helps with code portability, kinda--- but
--just porting code that bad and running it against an in-memory OLTP table
--seems like a recipe for disaster.



/*****************************************************
Cleanup
*****************************************************/

EXEC sp_xtp_unbind_db_resource_pool 'BabbyNamesXTP'
GO  

DROP RESOURCE POOL BabbyNamesXTP_Pool;
GO

DROP TABLE IF EXISTS agg.FirstNameByYearStateXTP;
GO
DROP TABLE IF EXISTS ref.FirstNameXTP;
GO

DROP TABLE IF EXISTS dbo.SeatAssignmentsXTP;
GO
DROP TABLE IF EXISTS dbo.SeatAssignmentsXTPHistory;
GO