How not to call Hekaton natively-compiled stored procedures

Note: This post was originally published only in our eBook, High Performance Techniques for SQL Server, Volume 2. You can find out about our eBooks here. Also note that some of these things may change with the planned enhancements to In-Memory OLTP in SQL Server 2016.

There are some habits and best practices that a lot of us develop over time with regard to Transact-SQL code. With stored procedures in particular, we strive to pass parameter values of the correct data type, and name our parameters explicitly rather than rely solely on ordinal position. Sometimes, though, we might get lazy about this: we might forget to prefix a Unicode string with N, or just list the constants or variables in order instead of specifying the parameter names. Or both.

In SQL Server 2014, if you are using In-Memory OLTP ("Hekaton") and natively compiled procedures, you might want to adjust your thinking on these things a little bit. I'll demonstrate with some code against the SQL Server 2014 RTM In-Memory OLTP Sample on CodePlex, which extends the AdventureWorks2012 sample database. (If you are going to set this up from scratch to follow along, please take a quick glance at my observations in a previous post.)

Let's take a look at the signature for the stored procedure Sales.usp_InsertSpecialOffer_inmem:

CREATE PROCEDURE [Sales].[usp_InsertSpecialOffer_inmem] 
	@Description    NVARCHAR(255)  NOT NULL, 
	@DiscountPct    SMALLMONEY     NOT NULL = 0,
	@Type           NVARCHAR(50)   NOT NULL,
	@Category       NVARCHAR(50)   NOT NULL,
	@StartDate      DATETIME2      NOT NULL,
	@EndDate        DATETIME2      NOT NULL,
	@MinQty         INT            NOT NULL = 0,
	@MaxQty         INT                     = NULL,
	@SpecialOfferID INT OUTPUT
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC 
WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE=N'us_english')

	DECLARE @msg nvarchar(256)

        -- validation removed for brevity

	INSERT Sales.SpecialOffer_inmem (Description, 
		DiscountPct,
		Type,
		Category,
		StartDate,
		EndDate,
		MinQty,
		MaxQty) 
	VALUES (@Description, 
		@DiscountPct,
		@Type,
		@Category,
		@StartDate,
		@EndDate,
		@MinQty,
		@MaxQty)

	SET @SpecialOfferID = SCOPE_IDENTITY()
END
GO

I was curious if it mattered whether the parameters were named, or if natively compiled procedures handled implicit conversions as arguments to stored procedures any better than traditional stored procedures. First I created a copy Sales.usp_InsertSpecialOffer_inmem as a traditional stored procedure – this involved merely removing the ATOMIC block and removing the NOT NULL declarations from the input parameters:

CREATE PROCEDURE [Sales].[usp_InsertSpecialOffer] 
	@Description    NVARCHAR(255), 
	@DiscountPct    SMALLMONEY     = 0,
	@Type           NVARCHAR(50),
	@Category       NVARCHAR(50),
	@StartDate      DATETIME2,
	@EndDate        DATETIME2,
	@MinQty         INT            = 0,
	@MaxQty         INT            = NULL,
	@SpecialOfferID INT OUTPUT
AS
BEGIN
	DECLARE @msg nvarchar(256)

        -- validation removed for brevity

	INSERT Sales.SpecialOffer_inmem (Description, 
		DiscountPct,
		Type,
		Category,
		StartDate,
		EndDate,
		MinQty,
		MaxQty) 
	VALUES (@Description, 
		@DiscountPct,
		@Type,
		@Category,
		@StartDate,
		@EndDate,
		@MinQty,
		@MaxQty)

	SET @SpecialOfferID = SCOPE_IDENTITY()
END
GO

To minimize shifting criteria, the procedure still inserts into the In-Memory version of the table, Sales.SpecialOffer_inmem.

Then I wanted to time 100,000 calls to both copies of the stored procedure with these criteria:

 
Using the following batch, copied for the traditional version of the stored procedure (simply removing _inmem from the four EXEC calls):

SET NOCOUNT ON;

CREATE TABLE #x
(
  i INT IDENTITY(1,1),
  d VARCHAR(32), 
  s DATETIME2(7) NOT NULL DEFAULT SYSDATETIME(), 
  e DATETIME2(7)
);
GO

INSERT #x(d) VALUES('Named, proper types');
GO

/* this uses named parameters, and uses correct data types */

DECLARE 
	@p1 NVARCHAR(255) = N'Product 1',
	@p2 SMALLMONEY    = 10,
	@p3 NVARCHAR(50)  = N'Volume Discount',
	@p4 NVARCHAR(50)  = N'Reseller',
	@p5 DATETIME2     = '20140615',
	@p6 DATETIME2     = '20140620',
	@p7 INT           = 10, 
	@p8 INT           = 20, 
	@p9 INT;

EXEC Sales.usp_InsertSpecialOffer_inmem 
	@Description    = @p1,
	@DiscountPct    = @p2,
	@Type           = @p3,
	@Category       = @p4,
	@StartDate      = @p5,
	@EndDate        = @p6,
	@MinQty         = @p7,
	@MaxQty         = @p8,
	@SpecialOfferID = @p9 OUTPUT;

GO 100000

UPDATE #x SET e = SYSDATETIME() WHERE i = 1;
GO

DELETE Sales.SpecialOffer_inmem WHERE Description = N'Product 1';
GO

INSERT #x(d) VALUES('Not named, proper types');
GO

/* this does not use named parameters, but uses correct data types */

DECLARE 
	@p1 NVARCHAR(255) = N'Product 1',
	@p2 SMALLMONEY    = 10,
	@p3 NVARCHAR(50)  = N'Volume Discount',
	@p4 NVARCHAR(50)  = N'Reseller',
	@p5 DATETIME2     = '20140615',
	@p6 DATETIME2     = '20140620',
	@p7 INT           = 10, 
	@p8 INT           = 20, 
	@p9 INT;

EXEC Sales.usp_InsertSpecialOffer_inmem 
	@p1, @p2, @p3, @p4, @p5, 
	@p6, @p7, @p8, @p9 OUTPUT;

GO 100000

UPDATE #x SET e = SYSDATETIME() WHERE i = 2;
GO

DELETE Sales.SpecialOffer_inmem WHERE Description = N'Product 1';
GO

INSERT #x(d) VALUES('Named, improper types');
GO

/* this uses named parameters, but incorrect data types */

DECLARE 
	@p1 VARCHAR(255)  = 'Product 1',
	@p2 DECIMAL(10,2) = 10,
	@p3 VARCHAR(255)  = 'Volume Discount',
	@p4 VARCHAR(32)   = 'Reseller',
	@p5 DATETIME      = '20140615',
	@p6 CHAR(8)       = '20140620',
	@p7 TINYINT       = 10, 
	@p8 DECIMAL(10,2) = 20, 
	@p9 BIGINT;

EXEC Sales.usp_InsertSpecialOffer_inmem 
	@Description    = @p1,
	@DiscountPct    = @p2,
	@Type           = @p3,
	@Category       = @p4,
	@StartDate      = @p5,
	@EndDate        = @p6,
	@MinQty         = '10',
	@MaxQty         = @p8,
	@SpecialOfferID = @p9 OUTPUT;

GO 100000

UPDATE #x SET e = SYSDATETIME() WHERE i = 3;
GO

DELETE Sales.SpecialOffer_inmem WHERE Description = N'Product 1';
GO

INSERT #x(d) VALUES('Not named, improper types');
GO

/* this does not use named parameters, and uses incorrect data types */

DECLARE 
	@p1 VARCHAR(255)  = 'Product 1',
	@p2 DECIMAL(10,2) = 10,
	@p3 VARCHAR(255)  = 'Volume Discount',
	@p4 VARCHAR(32)   = 'Reseller',
	@p5 DATETIME      = '20140615',
	@p6 CHAR(8)       = '20140620',
	@p7 TINYINT       = 10, 
	@p8 DECIMAL(10,2) = 20, 
	@p9 BIGINT;

EXEC Sales.usp_InsertSpecialOffer_inmem 
	@p1, @p2, @p3, @p4, @p5, 
	@p6, '10', @p8, @p9 OUTPUT;

GO 100000

UPDATE #x SET e = SYSDATETIME() WHERE i = 4;
GO
DELETE Sales.SpecialOffer_inmem WHERE Description = N'Product 1';
GO

SELECT d, duration_ms = DATEDIFF(MILLISECOND, s, e) FROM #x;
GO
DROP TABLE #x;
GO

I ran each test 10 times, and here were the average durations, in milliseconds:

Average duration, in milliseconds, of various call methods

With the traditional stored procedure, it is clear that using the wrong data types has a substantial impact on performance (about a 4 second difference), while not naming the parameters had a much less dramatic effect (adding about 700ms). I've always tried to follow best practices and use the right data types as well as name all parameters, and this small test seems to confirm that doing so can be beneficial.

With the natively compiled stored procedure, using the wrong data types still led to a similar drop in performance as with the traditional stored procedure. This time, though, naming the parameters didn't help out so much; in fact, it had a negative impact, adding almost two seconds to the overall duration. To be fair, this is a large number of calls in a fairly short time, but if you're trying to squeeze the absolute most bleeding-edge performance you can out of this feature, every nanosecond counts.

Discovering the Problem

How can you know if your natively compiled stored procedures are getting called with either of these "slow" methods? There's an XEvent for that! The event is called natively_compiled_proc_slow_parameter_passing, and it doesn't seem to be documented in Books Online at this time. You can create the following Extended Events session to monitor for this event:

CREATE EVENT SESSION [XTP_Parameter_Events] ON SERVER 
ADD EVENT sqlserver.natively_compiled_proc_slow_parameter_passing
(
    ACTION(sqlserver.sql_text)
) 
ADD TARGET package0.event_file(SET filename=N'C:\temp\XTPParams.xel');
GO
ALTER EVENT SESSION [XTP_Parameter_Events] ON SERVER STATE = START;

Once the session is running, you could try any of the above four calls individually, and then you can run this query:

;WITH x([timestamp], db, [object_id], reason, batch)
AS
(
  SELECT 
    xe.d.value(N'(event/@timestamp)[1]',N'datetime2(0)'),
    DB_NAME(xe.d.value(N'(event/data[@name="database_id"]/value)[1]',N'int')),
    xe.d.value(N'(event/data[@name="object_id"]/value)[1]',N'int'),
    xe.d.value(N'(event/data[@name="reason"]/text)[1]',N'sysname'),
    xe.d.value(N'(event/action[@name="sql_text"]/value)[1]',N'nvarchar(max)')
  FROM 
    sys.fn_xe_file_target_read_file(N'C:\temp\XTPParams*.xel',NULL,NULL,NULL) AS ft
    CROSS APPLY (SELECT CONVERT(XML, ft.event_data)) AS xe(d)
)
SELECT [timestamp], db, [object_id], reason, batch FROM x;

Depending on what you ran, you should see results similar to this:

DECLARE 
	@p1 NVARCHAR(255) = N'Product 1',
	@p2 SMALLMONEY    = 10,
	@p3 NVARCHAR(50)  = N'Volume Discount',
	@p4 NVARCHAR(50)  = N'Reseller',
	@p5 DATETIME2     = '20140615',
	@p6 DATETIME2     = '20140620',
	@p7 INT           = 10, 
	@p8 INT           = 20, 
	@p9 INT;

EXEC Sales.usp_InsertSpecialOffer_inmem 
	@Description    = @p1,
	@DiscountPct    = @p2,
	@Type           = @p3,
	@Category       = @p4,
	@StartDate      = @p5,
	@EndDate        = @p6,
	@MinQty         = @p7,
	@MaxQty         = @p8,
	@SpecialOfferID = @p9 OUTPUT;

DECLARE 
	@p1 VARCHAR(255)  = 'Product 1',
	@p2 DECIMAL(10,2) = 10,
	@p3 VARCHAR(255)  = 'Volume Discount',
	@p4 VARCHAR(32)   = 'Reseller',
	@p5 DATETIME      = '20140615',
	@p6 CHAR(8)       = '20140620',
	@p7 TINYINT       = 10, 
	@p8 DECIMAL(10,2) = 20, 
	@p9 BIGINT;

EXEC Sales.usp_InsertSpecialOffer_inmem 
	@p1, @p2, @p3, @p4, @p5, 
	@p6, '10', @p8, @p9 OUTPUT;

Sample results from Extended Events

Hopefully the batch column is enough to identify the culprit, but if you have large batches that contain multiple calls to natively compiled procedures and you need to track down the objects that are specifically triggering this problem, you can simply look them up by object_id in their respective databases.

Now, I don't recommend running all 400,000 calls in the text while the session is active, or turning this session on in a highly concurrent, production environment – if you are doing this a lot, it can cause some significant overhead. You are much better off checking for this kind of activity in your development or staging environment, as long as you can subject it to a proper workload covering a full business cycle.

Conclusion

I was definitely surprised by the fact that naming parameters – long considered a best practice – has been turned into a worst practice with natively-compiled stored procedures. And it is known by Microsoft to be enough of a potential problem that they created an Extended Event designed specifically to track it. If you are using In-Memory OLTP, this is one thing you should keep on your radar as you develop supporting stored procedures. I know I am definitely going to have to un-train my muscle memory from using named parameters.