Blog

I was trying to upgrade my MACD technical analysis calculation to use the new In Memory technologies introduced in SQL Server 2014. The statement used in the SQL 2012 calculation worked just fine until I put it in a natively compiled stored procedure.

[red_box]
Location: e:\sql12_main_t\sql\ntdbms\query\include\qpoptim.h:1605
Expression: false
SPID: 52
Process ID: 2260

Msg 3624, Level 20, State 1, Procedure InMemoryErrorNatCompSP, Line 46
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a Hotfix from Technical Support.

Msg 0, Level 20, State 0, Line 40
A severe error occurred on the current command. The results, if any, should be discarded.
[/red_box]

To simplify, I created a database that is simple enough to just generate the error. The following script will generate the 3624 error and a stack dump file will be created. Since the severity is 20, your connection will be closed (and don’t run the script in a production environment). To run the script, you need to change the path to the location for the in memory database file.

/**************************
DATABASE
**************************/
CREATE DATABASE InMemoryError
GO

USE InMemoryError
GO

ALTER DATABASE InMemoryError ADD FILEGROUP InMemoryErrorFileGroup CONTAINS MEMORY_OPTIMIZED_DATA
GO

ALTER DATABASE InMemoryError ADD FILE
	(
		NAME = InMemoryErrorFile
		--CHANGE!
		,FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL12.SS2014\MSSQL\DATA\InMemoryErrorFile'
	)
TO FILEGROUP InMemoryErrorFileGroup
GO

/**************************
TABLE
**************************/
CREATE TABLE dbo.InMemoryErrorTable
	(
		RowId INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000)
	)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);


INSERT dbo.InMemoryErrorTable VALUES (1), (2)
GO

/**************************
NATIVELY COMPILED
STORED PROCEDURE

ERROR!!!
**************************/
CREATE PROCEDURE dbo.InMemoryErrorNatCompSP
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS 
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')

	--JUST A NONSENSE SQL STATEMENT TO GENERATE THE ERROR
	SELECT
		RowId,
		SUM(RowId) OVER (ORDER BY RowId) AS MA20
	FROM
		dbo.InMemoryErrorTable

END
GO

/**************************
THE SAME SQL STATEMENT
WITHOUT SP WORKS
**************************/
SELECT
	RowId,
	SUM(RowId) OVER (ORDER BY RowId) AS MA20
FROM
	dbo.InMemoryErrorTable

Check your SQL log to find the location of the stack trace. The information is written as one of the first records when the error happens, so you need to scroll down a bit:

This is the third machine I am getting the error on, so it isn’t just me 🙂 The setups have always been SQL Server 2014 Developer Edition 64-bit. But the OS has been Windows 7, Windows 8.1 and Windows Server Standard 2012 R2.

It seems to be the combination of an aggregate statement (such as SUM or AVG) together with the OVER clause. Either ORDER BY or PARTITION BY or both can be used to create the error.

LAG and LEAD seems to work fine.

Connect item 880545.

I installed CU1, but the error remains.