Continuing from my first post on this subject where we looked at creating memory optimized tables and querying these with T-SQL, lets look at creating natively compiled stored procedures. So what are new natively compiled stored procedures? Stored procedures that are marked with NATIVE_COMPILATION are natively compiled. This means the Transact-SQL statements in the procedure are all compiled to native code for efficient execution of performance-critical business logic.These are destined for heavy OLTP operations, such as heavy inserts and updates vs. reporting\DSS style queries, which require joins and heavy aggregations.
These stored procedures contain what is known as ATOMIC blocks. concept in SQL Server 2014 that can be used only with native procs. The basic thing an ATOMIC block gives you is that all statements within the block either succeed or fail, atomically. That means that you don’t have to explicitly do BEGIN TRAN…COMMIT…ROLLBACK inside natively compiled procs.
As with all transactions that touch memory-optimized tables, with natively compiled stored procedures you will need to consider retry logic to deal with potential failures such as write conflicts (error 41302) or dependency failures (error 41301). You can implement this logic in two ways:
- On the client side. The client application catches an error as thrown from SQL Server and retry based on above error codes
- On the server side, using a “wrapper stored procedure”. The client would call an interpreted T-SQL stored procedure that calls the native proc. The wrapper procedure then uses try/catch logic to catch the error and retry the procedure call if needed. You do need to consider in this case the possibility that results are returned to the client before the failure, and the client would not know to discard them. Therefore, to be safe, it is best to use this method only with native procs that do not return any result sets to the client. In addition, consider that using a wrapper stored procedure does impact performance. For optimal performance, use client-side retries.
For more on retry logic guidelines see SQL BOL article
So simplest example based on our test memory optimized tables is here
-- Drop stored procedure if it already exists
IF OBJECT_ID('dbo.insertOderItem') IS NOT NULL
DROP PROCEDURE dbo.InsertOrderItem
CREATE PROCEDURE dbo.InsertOrderItem
(@OrderItemID int,@ItemId int, @OrderId int)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
INSERT INTO dbo.OrderItems VALUES (@OrderItemID,@ItemId,@OrderId)
Some interesting syntax items to note:
Natively Compiled Stored Procedure must be bound to the schema of the objects used in the procedure. So tables cannot be dropped that are referenced by the procedure.
- BEGIN ATOMIC: That is the atomic block, I referenced above.
This will ensure, if the Stored Procedure is invoked outside the active transaction context, that it will start a new transaction. An ATOMIC block must have two required options TRANSACTION ISOLATION LEVEL and LANGUAGE.
TRANSACTION ISOLATION LEVEL. I will not go into SQL Server Isolation Level’s here, but you can see more in SQL BOL article. Also see my previous post on some findings with Isolation Level with Websphere AS and JDBC.
Avoid implicit conversions by matching parameter types passed in with parameter types in the procedure and memory optimized table schema. The Exists clause is one of the T-SQL constructs that are not supported, but you can simulate it like this:
DECLARE @exists BIT = 0
SELECT TOP 1 @exists = 1 FROM MyTable WHERE …
IF @exists = 1
In the next post I will try to move into monitoring and troubleshooting in-Memory OLTP.