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.

Good Boy!

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.

Bad Boy!

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)

Conclusion

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.

Stay Tuned!