Memory optimized Tables and Natively Compiled Stored Procedures

Memory optimized Tables:

SQL Server 2014 support fully durable and delayed duration transaction by default

Memory-optimized tables are fully durable like traditional/existing disk based tables; they support ACID property in case of Default or fully durable setting.

The primary store for memory-optimized tables is main memory and rows in the table are read from and written to memory. The entire table resides in memory. A second copy of the table data is maintained on disk, but only for durability purposes.

For delayed durability see link.

http://dineshbabuverma.blogspot.com/2014/08/delayed-durability-in-sql-server-2014.html

Besides the default durable memory-optimized tables, SQL Server also supports non-durable memory-optimized tables, which are not logged and their data is not persisted on disk. This means that transactions on these tables do not require any disk IO, but the data will not be recovered if there is a server failover.

In memory table rows versioned. Each row has different versions which allow us concurrent reads and writes, Row versions in memory-optimized tables are addressed using 8-byte memory pointers.

 Data in memory-optimized tables is accessed in two ways:

• Through natively compiled SP’s (only support memory optimized tables).
• Through traditional/interpreted SP’s and Ad-hoc queries.

Natively Compiled Stored Procedures:

Natively compiled stored procedures are compiles to native code that access memory-optimized tables and table types only. Allows fast execution of the queries and business logic in it, they get compiled as and when they created, we cannot alter natively compiled sp’s

As they compiled in first creation we comes to know the divide by zero, type conversion and arithmetic over flow errors prior to executions.

Example:

Let us create memory optimized tables and natively compiled SP's

-- DROP DATABASE MyTestingMOT

CREATE DATABASE mytestingmot 

GO

--------------------------------------
-- create database with a memory-optimized filegroup and a container.
-- to add the file  we should have folder DB on drive C
ALTER DATABASE mytestingmot ADD filegroup mytestingmot_filegrp CONTAINS
memory_optimized_data

ALTER DATABASE mytestingmot ADD FILE (name='MyTestingMOT_File1', filename=
'c:\DB\MyTestingMOT_File1') TO filegroup mytestingmot_filegrp

ALTER DATABASE mytestingmot
SET memory_optimized_elevate_to_snapshot=ON
go 

USE MyTestingMOT

go 

-- create a durable (data will be persisted) memory-optimized table
-- two of the columns are indexed  

  CREATE TABLE dbo.moemployee
  (
     moemployeeid   INT NOT NULL PRIMARY KEY NONCLUSTERED,
     moemployeename VARCHAR(100),
     deptid         INT NOT NULL INDEX IX_DeptId NONCLUSTERED HASH WITH (BUCKET_COUNT=10000),
     hiredate       DATETIME2 NOT NULL,
     salary         MONEY
  )WITH (MEMORY_OPTIMIZED=ON)


INSERT dbo.moemployee
VALUES (1,'Allen', 8798, Getdate(), NULL)

INSERT dbo.moemployee
VALUES (2,'Derren', 23, Getdate(), 4445.4)

INSERT dbo.moemployee
VALUES (3,'Sean', 80, Getdate(), NULL)

INSERT dbo.moemployee
VALUES (4,'Alex', 342, Getdate(), 6665.4) GO

-- verify table contents  
   SELECT * FROM   dbo.moemployee 
  GO
 
--  update statistics on memory-optimized tables  
UPDATE STATISTICS dbo.moemployee WITH fullscan, norecompute 
  GO

-- natively compiled stored procedure for to manipulate salary employee salary based on EmpId
-- DROP PROC uspMOEmployee

CREATE PROCEDURE dbo.uspMOEmployee @MOEmployeeId INT,@Salary MONEY
 WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
 AS
 BEGIN ATOMIC
 WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
   
  IF @MOEmployeeId IS NULL
  THROW 51000, N'The MOEmployeeId does not exist.', 1
 
  UPDATE dbo.MOEmployee SET Salary=@Salary WHERE MOEmployeeId=@MOEmployeeId
 END
GO  

/* Unit test Case
Pass paramters @MOEmployeeId and @Salary
EXEC uspMOEmployee @MOEmployeeId= 2, @Salary= 9900  

*/

Execution OutPut:

MOEmployeeId	MOEmployeeName	DeptId	HireDate	Salary
1	Allen	8798	11:51.4	NULL
2	Derren	23	11:51.4	4445.4
3	Sean	80	11:51.4	NULL
4	Alex	342	11:51.4	6665.4

 Note: 

we can not alter Natively compiles sp's, we need to drop and create them. if we try to alter them it shows Error.

Msg 10794, Level 16, State 25, Procedure uspMOEmployee, Line 3
The operation 'ALTER PROCEDURE' is not supported with natively compiled stored procedures.

Delayed Durability In SQL Server 2014

DELAYED DURABILITY In SQL Server 2014

Delayed durability return the control to client/application before the data is committed to disk from Memory/Log.

let us understand Fully & Delayed durability:

Fully Durable Transaction (SQL Server):  First Data is written to Log --> Data is committed to Disk -->successful commit Command is issued to Client/application

    1 .       No Data loss

    2 .       I/O latency

    3 .       Synchronous

Delayed Durable Transaction (SQL 2014):  First data is written to Log --> Successful commit command is issue to client/application --> later data commits to Disk

    1 .       Data loss of the defined interval

    2.       Less I/O Latency and contention(Data is committed in batches)

    3.       Asynchronous

In Delayed Durable Transaction is visible to other transactions:

   1 .  When fully delayed transaction commits the changes to disk in same database

   2 .   When we manually successfully Executes system defined procedure sys.sp_flush_log to write            the log info to disk.

   3 .    In memory transaction log buffer fills up and flushed the log to disk.

Delayed Durability can be controlled at  Database level, Commit Level, Atomic block Level

ALTER DATABASE DBNameHere

SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }

Delayed Durability Vs SQL Features

S#	SQL Features	With Delayed Durable
1	Always On/ Mirroring	No Guarantee ( Some Data may lost)
2	Failover Clustering	No Guarantee( Some Data may lost)
3	Transactional Replication	No Guarantee(Only durable data is shipped))
4	Log Shipping	No Guarantee(Only durable data is shipped)
5	Log Backup	No Guarantee(Only durable data is backed up)
6	Cross Database and DTC	No Guarantee(Only durable data is backed up)
7	Crash Recovery	Data is consistent only for Durable Data
8	CT & CDC	No Guarantee(Only durable data is backed up)

 

Non Repeatable Reads Or Phantom Reads in SQL Server

Non-repeatable Reads occur when transaction1 in Session1 access/read the data and in Session2 other Transaction2 modify (Commit) the accessed Data.
When we again access/read same data in Session1 by Tranaction1 then we get different/modified value.

Phantom Reads occur when transaction1 in Session1 access/reads the range of data and in Session2 other Transaction2 modify (Insert/Delete) the accessed Data of given range.
Phantom Read is special case of Non-repeatable Reads.

Continuing the table structure from below link:
http://dineshbabuverma.blogspot.com/2014/07/read-committed-vs-repeatable-read-in.html

Let us Create table structure as we created in above link


if exists(SELECT * FROM SYS.OBJECTS WHERE NAME = 'Emp_Temp')
BEGIN
DROP TABLE Emp_Temp
END

if exists(SELECT * FROM SYS.OBJECTS WHERE NAME = 'Dept_TEMP')
BEGIN
DROP TABLE Dept_TEMP
END

CREATE TABLE Emp_Temp (EmpId int primary key,EmpName Varchar(100), DeptId int)
insert Emp_Temp values (1,'Allen',    6)
insert Emp_Temp values (2,'Defriez', 6)
insert Emp_Temp values (3,'Sean',    6)
insert Emp_Temp values (4,'Alex',    6)
insert Emp_Temp values (5,'Blex',    6)

CREATE TABLE Dept_TEMP (DeptId int Primary Key, DeptName varchar(100) , EmpCount int)
-- Now, in session 1 lock the Fourth row of table Emp_Temp
BEGIN TRAN
UPDATE Emp_Temp set EmpId = 4 where EmpId = 4

/* Now , in New session 2 run the following outer join at repeatable read isolation level:*/
set transaction isolation level repeatable read
select * from Emp_Temp e left outer join Dept_TEMP d on e.DeptId = d.deptid

/*This plan scans the first row from Emp_Temp, tries to join it with Dept_TEMP, finds there are no matching rows, and outputs a NULL extended row.
It then blocks waiting for session 1 to release the lock on the second row of Emp_Temp. Finally, in session 1, insert a new row into Dept_TEMP and release the lock:*/

INSERT Dept_TEMP values (6,'IT', 100)
COMMIT TRAN

-- Now go back to session2, we are getting below result set.
EmpId     EmpName      DeptId     DeptId      DeptName   EmpCount
1               Allen               6             NULL        NULL       NULL
2               Defriez            6             NULL        NULL       NULL
3               Sean               6              NULL        NULL       NULL
4               Alex                6              6                IT             100
5               Blex                6              6                 IT            100

/* This happens becuase we have selected info with Lower isolation(repeatable read) in session2
if we select this info with higer isolation(serializable) as below*/
set transaction isolation level Serializable
SELECT * FROM Emp_Temp e LEFT OUTER JOIN Dept_TEMP d on e.DeptId = d.deptid

-- it shows below error.
Msg 1205, Level 13, State 51, Line 10
Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Note: I have explained above with respect to isolations levels.
Lets us Understand Non-repeatable Reads and Phantom Reads in very simple terms

Non-Repeatable Reads:
let us consider, we have Transaction1 in Session1 and Transaction2 in Session2

now in session1 by Transaction1 we have query.
 BEGIN TRAN
Select EmpName from Emp_Temp where EmpId= 2
COMMIT
Query Output.

EmpName

Defriez

now in session2 by Transaction2 we have query.
 BEGIN TRAN
Update Emp_Temp
SET EmpName=  'Jack' where EmpId = 2
 COMMIT
 Query Output.
(1 row(s) affected)

now go back in session1 by Transaction1 execute same Query
BEGIN TRAN
 Select EmpName from Emp_Temp where EmpId= 2
 COMMIT

Query Output.

EmpName

Jack

Note: Same query in session1 returning two different values  this we called Non-repeatable Reads

Phantom Reads:
now in session1 by Transaction1 we have select query with where clause specifying range of data.
 BEGIN TRAN
Select * from Emp_Temp where EmpId> = 2 and Empid < 5
COMMIT
Query Output.

EmpId	EmpName	DeptId
2	Jack	6
3	Sean	6
4	Alex	6

Now in session2 by Transaction2 we have query.
BEGIN TRAN
DELETE Emp_Temp
 where EmpId = 3 COMMIT
 Query Output.
(1 row(s) affected)

now go back in session1 by Transaction1 execute same Query
BEGIN TRAN
Select * from Emp_Temp where EmpId> = 2 and Empid < 5
 COMMIT
 Query Output:

EmpId	EmpName	DeptId
2	Jack	6
4	Alex	6

Note: Same query in session1 returning different number of records this we called Phantom Reads

Hope this mght have helped us to under the different between Non-repeatable Reads and Phantom Reads.