Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 1,893,375 hits
  • Select GETDATE()

    June 2021
    M T W T F S S

Posts Tagged ‘SQL Server Management Studio’

A-Z of In-Memory OLTP : Native Compilation Advisor in SSMS

Posted by blakhani on February 4, 2014

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 
CREATE PROCEDURE [dbo].[InsertName] @iID INT, @vFName VARCHAR(20), @vLName VARCHAR(20)

  INSERT INTO dbo.MyFirstMemporyOptimizedTable
  VALUES (@iID, @vFName, @vLName);


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 


IF Object_id('usp_InsertName_Errors') IS NOT NULL 
  DROP PROCEDURE usp_insertname_errors 


CREATE PROCEDURE [dbo].[Usp_insertname_errors] 
@iID    INT, 
@vFName VARCHAR(20), 
@vLName VARCHAR(20) 
      -- 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, 
                  (@iID + 1, 

      -- Subquery 
      SELECT vfname 
      FROM   diskbasedtable 
      WHERE  iid IN (SELECT is_published 
                     FROM   sys.objects) 

      -- goto 
      GOTO hi 

      -- cursor 
        SELECT iid, 
        FROM   dbo.diskbasedtable 


      -- cte 
      WITH cte (i, j) 
           AS (SELECT iid, 
               FROM   dbo.diskbasedtable) 
      SELECT * 
      FROM   cte 


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
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 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.

Stay Tuned!

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in A - Z Series, Hekaton Series, In Memory OLTP, In-Memory OLTP, SQL Server 2014 | Tagged: , , , , , , , , , , , , , | 1 Comment »

    A-Z of In-Memory OLTP : Memory Optimization Advisor

    Posted by blakhani on January 30, 2014

    While trying something new in real life, it would be great to take advice from someone who is expert in that area. On the same lines, SQL Server Management Studio can help us as “advisor” about migrating disk based tables to In-Memory Tables. Here is an earlier blog about various SSMS enhancements to support In-Memory objects handling.

    Where is the Wizard?

    As always, most of the wizards are in the right click on node of SQL Server Management Studio (SSMS). This option to launch this wizard would come once we right click on a disk based table, as shown below. Note that it won’t be visible if table is already in-memory table.


    Step by Step

    When we select the “Memory Optimization Advisor” below migration wizard will be launched:


    Clicking Next would take us to “Memory Optimization Checklist” screen. This would perform checks for the schema of the table and warn about limitations of in-memory table. My table was a simple DiskBasedTable which we have used in earlier blogs, so all green. Note that my blog posts are on CTP2 and few limitation might be removed in RTM so below checks might change.


    In case of any errors, we would not be able to proceed but advisor would give us hints about where to go. For demo purpose, I have created a table with “problems”

    CREATE TABLE [Address](
        [AddressID] [int] IDENTITY(1,1)  NOT NULL,
        [AddressLine1] [nvarchar](60) NOT NULL,
        [AddressLine2] [nvarchar](60) NULL,
        [City] [nvarchar](30) NOT NULL,
        [StateProvinceID] [int] NOT NULL,
        [PostalCode] [nvarchar](15) NOT NULL,
        [SpatialLocation] [geography] NULL,
        [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
        [ModifiedDate] [datetime] NOT NULL,
        [AddressID] ASC
    ALTER TABLE [Address] ADD  CONSTRAINT [DF_Address_rowguid]  DEFAULT (newid()) FOR [rowguid]
    ALTER TABLE [Address] ADD  CONSTRAINT [DF_Address_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]


    and if we run wizard for above table, we would get below screen with few red crosses. Try to compare the errors with the definition above.


    Because of these errors we can see that “Next>” button is grayed out. We must resolve these problems before this table can be migrated to an In-Memory OLTP table. We can also use “Generate report” and get HTML file which would have hyperlink about each “show me how”.


    Let’s get back to the original wizard. Pressing next would present “Memory Optimization Warning” screen


    These Information messages allow us to understand the limitation of usage of In-Memory OLTP tables.  To know “More” about each, we can click on the hyperlink that says “More Information”. More Information would open this link. Let’s hit Next now.

    Next screen is about “Review Optimization Options”. In my case, I already have a In-Memory Optimized file-group in the database so they are greyed out. If this a database with no in-memory optimized filegroup then we would get option to create them (Read here). The next option is a checkbox that says “Also copy table data to the new memory optimize table”.  The default value is checkbox unchecked, which means no data will be copied to the new In-Memory OLTP table. Last option is about durability setting of the table which we discussed in previous blog


    Once we hit next, we need to provide details about Primary Key (which is mandatory). We also need to provide details about index type and bucket count, in case of hash index. You can read more about hash indexes (part 1 and part 2)


    Once we provided the options in all previous screens, next screen is “Verify Migration Actions” which is summary of the actions selected in previous screens. 


    if we click on Script button, below is the output in SSMS. If we read carefully, there are based on options selected in wizard. We selected “rename”, “Non Clustered Hash Primary Key” and “Schema and data durability”

    USE [SQLSeverHelp_IMO]
    EXEC dbo.sp_rename @objname = N'[dbo].[DiskBasedTable]', @newname = N'DiskBasedTable_old', @objtype = N'OBJECT'
    USE [SQLSeverHelp_IMO]
    CREATE TABLE [dbo].[DiskBasedTable]
        [iID] [int] NOT NULL,
        [vFName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [vLName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
    )WITH ( BUCKET_COUNT = 16384)

    If we click on migrate, the script would be run automatically by the wizard.


    As we can see that wizard can be very useful to migrate a disk based table to an In-Memory OLTP table. It can help us in identifying the things you have to do to a table in order to migrate it.  After resolving all the issues we can either generate script to migrate later or use wizard itself to run script.

    Hope you have learned something new today.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in A - Z Series, In Memory OLTP, In-Memory OLTP, SQL Server 2014 | Tagged: , , , , , , , , , , , , , | 3 Comments »