In our last part of A-Z Series, we learned about Memory Optimization Advisor (wizard in SSMS) which can be used to migrate disk based tables to in-memory tables. This post is dedicated to another wizard which is for stored procedure. It is important to remember that, unlike Memory Optimization Advisor, this wizard is NOT to convert regular stored procedures into natively complied stored procedure. The idea of the “Native Compilation Advisor” is to scan the code of stored procedure and give advise about what to change in the stored procedure.
Where is the wizard?
The “Native Compilation Advisor” wizard can be launched by right clicking the stored procedure which we wish to convert to natively complied. In below screenshot, we can notice the option difference when we right click on regular stored procedure vs. natively complied stored procedure.
Image#1 – Comparison of right click.
What is the purpose?
As explained earlier, this Advisor would NOT convert regular stored procedure to natively complied but provide just the advice and pointers. Let me explain them via good boy and bad boy. We would use same database which we have been using earlier. Here is the blog to get script for database and tables.
Since we already have database SQLSeverHelp_IMO and tables (DiskBasedTable and MyFirstMemporyOptimizedTable), Let’s create regular stored procedure as below.
-- Create regular stored procedure in database and referencing IMO table Use SQLSeverHelp_IMO GO CREATE PROCEDURE [dbo].[InsertName] @iID INT, @vFName VARCHAR(20), @vLName VARCHAR(20) AS BEGIN INSERT INTO dbo.MyFirstMemporyOptimizedTable VALUES (@iID, @vFName, @vLName); END GO
Now, let’s launch the wizard by right clicking on InsertName stored procedure in SQL Server Management Studio (shown in image 1). As soon as we click on Native Compilation Advisor, we get welcome screen.
Image#2 – Welcome Screen.
As we can read on the welcome screen – “The Native Compilation Advisor will assist you to discover and evaluate Transact-SQL elements in your stored procedure that are not supported in Native Compilation”. See, I told you! Okay, Let’s hit next.
Below screen appears and you would notice that Next button is disabled.
Image#3 – Stored Procedure Validation – no errors
This is because there is no T-SQL element which is not supported by Natively complied stored procedures. Cancel the wizard to exit.
Now, I am going to create stored procedure with some (actually many) elements which are not supported by natively complied stored procedure. Please note that this is for CTP2 and things might change in RTM.
-- Create regular stored procedure in database -- with many elements not supported by natively complied stored procedures USE sqlseverhelp_imo go IF Object_id('usp_InsertName_Errors') IS NOT NULL DROP PROCEDURE usp_insertname_errors go CREATE PROCEDURE [dbo].[Usp_insertname_errors] @iID INT, @vFName VARCHAR(20), @vLName VARCHAR(20) AS BEGIN -- four part naming SELECT * FROM hk.sqlseverhelp_imo.dbo.myfirstmemporyoptimizedtable -- three part naming SELECT * FROM sqlseverhelp_imo.dbo.myfirstmemporyoptimizedtable -- multivalued insert INSERT INTO dbo.myfirstmemporyoptimizedtable VALUES (@iID, @vFName, @vLName), (@iID + 1, @vFName, @vLName) -- Subquery SELECT vfname FROM diskbasedtable WHERE iid IN (SELECT is_published FROM sys.objects) -- goto GOTO hi -- cursor DECLARE c CURSOR FOR SELECT iid, vfname FROM dbo.diskbasedtable HI: -- cte WITH cte (i, j) AS (SELECT iid, vfname FROM dbo.diskbasedtable) SELECT * FROM cte END go
if we run wizard for above stored procedure, we would have next button enabled. This is one of those next button which is not going to make your happy. Agree?
Image#4 – Stored Procedure Validation – with errors
Fine, so there are problems. Hitting next would tell us more.
Image#5 – Final Results!
As highlighted in image # 5 , we also have the option to “generate report” for these unsupported elements. Here is the report which I saved in html format. It gives us code, line number and they are grouped under T-SQL Element (like *, Four-part names etc.)
Native Compilation Advisor evaluation results for [SQLSeverHelp_IMO].[usp_InsertName_Errors]
Report Date/Time:2/3/2014 12:16 PM
Transact-SQL Element Transact-SQL Code Line Number * SELECT * FROM hk.sqlseverhelp_imo.dbo.myfirstmemporyoptimizedtable 3 SELECT * FROM sqlseverhelp_imo.dbo.myfirstmemporyoptimizedtable 7 WITH cte (i, j) AS (SELECT iid, vfname FROM dbo.diskbasedtable) SELECT * FROM cte 37 Four-part names hk.sqlseverhelp_imo.dbo.myfirstmemporyoptimizedtable 4 Three-part names sqlseverhelp_imo.dbo.myfirstmemporyoptimizedtable 8 Multi-row table-valued constructor VALUES (@iID, @vFName, @vLName), (@iID + 1, @vFName, @vLName) 12 One-part names diskbasedtable 21 cte 42 IN iid IN (SELECT is_published FROM sys.objects) 22 Subquery (SELECT is_published FROM sys.objects) 22 GOTO GOTO hi 26 Cursors CURSOR FOR SELECT iid, vfname FROM dbo.diskbasedtable 30 WITH clause WITH cte (i, j) AS (SELECT iid, vfname FROM dbo.diskbasedtable) 37 cte (i, j) AS (SELECT iid, vfname FROM dbo.diskbasedtable) 37
Here is the link which is there in image # 5 http://go.microsoft.com/fwlink?LinkID=311587 talks about common patterns and their workarounds. (link might get change after release of the product)
Native Compilation wizard can be used to get advice from SQL Server about the changes which are needed to covert a regular stored procedure to natively compiled stored procedure. There are some common pattern of the problems where workaround can be used by following the link provided by the advisor. Conversation of stored procedure is still manual process and not as clean as Table Advisor (Memory Optimization Advisor). Using both wizards, we can get advice to take action prior to a migration. At the end I would say that this is the only wizard I have seen which doesn’t have finish button.
Have you installed SQL Server 2012 CTP2 yet? If you haven’t yet, be sure to check out the SQL Server 2014 CTP2 so that you can follow this series and get your hands dirty.