Help: SQL Server

Imagine that you are participating in a 400 meters running competition and you are the only one to get discount of 300 meters and allowed to start 100 meters before the finish line. What would be the result? All participants have to run 400 meters and you would run only 100 meters. Of course, You would win!!! (terms and condition apply). In the same way, what would happen in SQL world, if we finish a significant amount of work i.e. compilation of stored procedure during creation phase. Yes, you are correct. The time spent in execution of the stored procedure would be less. In this post we would try to learn internals of native compilation.

In earlier “T-SQL Constructs” post of this series, we have seen syntax of creation of natively compiled stored procedure. Native compilation of the stored procedure causes machine language code (i.e. DLL) to be generated and loaded in SQL Server Memory. Natively compiled stored procedures allows us to execute T-SQL in the fastest way, which includes accessing data in memory-optimized tables. In addition, disk-based tables are not allowed to be accessed at all inside natively compiled stored procedures. If we attempt to create a natively compiled procedure which accesses disk based table, we would get below error. In my test, the table name was DiskBasedTable.

Msg 10775, Level 16, State 1, Procedure InsertName, Line 14
Object ‘dbo.DiskBasedTable’ is not a memory optimized table and cannot be accessed from a natively compiled stored procedure.

The advantages of natively complies stored procedure is that most of the work (permission check, object existence check, query plan generation) is done during compilation/creation of stored procedure. Due to this there are many limitations on the Transact-SQL that is allowed, the data types and collations that can be accessed and processed in natively compiled procedures. SQL Server Product Documentation team has done good amount of work in documenting them (refer Supported Data Types, Unsupported Transact-SQL Constructs)

To get a list of natively complied stored procedures we can use catalog views as shown below. Highlighted below is the new column added to support in-memory OLTP.

SELECT uses_native_compilation, 
       object_id, 
       Object_name(object_id) 
FROM   sys.sql_modules 
WHERE  uses_native_compilation = 1 
go 

SELECT uses_native_compilation, 
       object_id, 
       Object_name(object_id) 
FROM   sys.all_sql_modules 
WHERE  uses_native_compilation = 1 
go

Here is the output in SSMS

To convert a stored procedure to DLL, SQL Server engine uses C compiler. Does this mean we need to have C installed on the machine when we install in-memory OLTP? Well, we just need to complie the C code so we just need compiler related files. SQL Server product comes with compiler files. As soon as SQL Engine is installed, these files are installed under <SQL Server Installation Directory>\MSSQL\Binn\Xtp folder. Main compilation files are located under ..\VC\bin folder.

Sometimes, we might face stored procedure creation failure due to issues in compilation of the C code. To make our life easier, SQL Server generates intermediate files and they are useful for troubleshooting by members of product support team. Here is the text of .out file.

Microsoft (R) C/C++ Optimizing Compiler Version 17.00.60605.1 for x64

Copyright (C) Microsoft Corporation.  All rights reserved.

xtp_p_8_581577110.c

Microsoft (R) Incremental Linker Version 11.00.60605.1

Copyright (C) Microsoft Corporation.  All rights reserved.

/out:xtp_p_8_581577110.dll

/ltcg

/debug

/dll

/implib:xtp_p_8_581577110.lib

"/LIBPATH:C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\Xtp\gen\lib"

"/LIBPATH:C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\Xtp\VC\lib"

/noentry

/nodefaultlib

/incremental:no

/ltcg

/wx

/FUNCTIONPADMIN

xtp_p_8_581577110.obj

hkgenlib.lib

hkruntime.lib

hkengine.lib

hkgenexp.exp

hkcrt.lib

hkk32.lib

hkversion.obj

Generating code

Finished generating code

If you are a C developer, it may be easy for you to understand the switches passed during calling of CL.exe. There is no real need for a DBA to know all these options. In case you are too excited, read here for the parameters.

Before I conclude, I would like you to throw ideas about below error, while creation of natively compiled stored procedure.

Here is the text of the message:

Msg 41312, Level 16, State 4, Procedure InsertName1, Line 2

Unable to call into the C compiler. GetLastError = 2.

Of course, I broke something and above is the error I am getting. Can you guess? Go ahead and comment on the post.

Stay Tuned for next part of the series.

Cheers,

Balmukund Lakhani

Twitter @blakhani

Author: SQL Server 2012 AlwaysOn – Paperback, Kindle