Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Archive for January, 2014

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.

image

Step by Step

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

image

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.

image

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,
 CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED 
(
    [AddressID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [Address] ADD  CONSTRAINT [DF_Address_rowguid]  DEFAULT (newid()) FOR [rowguid]
GO

ALTER TABLE [Address] ADD  CONSTRAINT [DF_Address_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
GO

 

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.

image

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

image

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

image

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. http://go.microsoft.com/fwlink?LinkID=306707 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

image

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)

image

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. 

image

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]
GO

EXEC dbo.sp_rename @objname = N'[dbo].[DiskBasedTable]', @newname = N'DiskBasedTable_old', @objtype = N'OBJECT'
GO

USE [SQLSeverHelp_IMO]
GO

SET ANSI_NULLS ON
GO

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

CONSTRAINT [DiskBasedTable_primaryKey] PRIMARY KEY NONCLUSTERED HASH 
(
    [iID]
)WITH ( BUCKET_COUNT = 16384)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

GO

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

Summary

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
  • Advertisements

    Posted in A - Z Series, In Memory OLTP, In-Memory OLTP, SQL Server 2014 | Tagged: , , , , , , , , , , , , , | 3 Comments »

    A-Z of In-Memory OLTP : Durable vs. Non-durable tables

    Posted by blakhani on January 28, 2014


    Can you think of a situation where you don’t want to log anything in SQL Server and you are OK even if data is lost? What comes to your mind? Staging tables used in loading phase of Data warehouses? ASP Session state? Temporary logging tables?

    If you have such situations where data is temporary and you are OK to lose the data due to SQL Server restart then you can think of non-durable tables in SQL Server in-memory OLTP. By default, tables are durable. We have briefly discussed here in earlier blog of this series. This is defined by DURABILITY parameter during created table syntax. This indicates whether we want data in the table to be persisted when database is restarted (via offline/online, SQL Service restart or SQL crash). Non durable tables would have SCHEMA_ONLY as the value passed to the durability parameter. Note that these tables are different from the temporary table which we create in tempdb (#table and ##table). One major difference is that tempdb tables are not memory optimized and they won’t be able to survive after restart, including schema.

    We have already discussed “How In-Memory Table is durable” in this blog. Since transaction logging is not done for non-durable table, the data insertion is faster (actually much.. much faster) than durable table, of course at the cost of data durability. Let’s have a look at them in action. We would created two tables with same schema, one being durable (SCHEMA_AND_DATA durability) and another being non-durable (SCHEMA_DURABILITY)

    -- Create database with IMO Filegroup, If exists drop it.
    Use Master
    go
    If db_id('SQLSeverHelp_IMO') is NOT NULL
    drop database SQLSeverHelp_IMO
    GO
    CREATE DATABASE SQLSeverHelp_IMO 
        ON PRIMARY (
        NAME = [SQLSeverHelp_IMO_data]
        ,FILENAME = 'C:\IMO_Database\SQLSeverHelp_IMO.mdf'
        )
        
        ,FILEGROUP [SQLSeverHelp_IMO_FG] CONTAINS MEMORY_OPTIMIZED_DATA ( 
        NAME = [SQLSeverHelp_IMO_dir]
        ,FILENAME = 'C:\IMO_Database\SQLSeverHelp_IMO_dir'
        ) 
        
        LOG ON (
        NAME = [SQLSeverHelp_IMO_log]
        ,Filename = 'C:\IMO_Database\SQLSeverHelp_IMO_log.ldf'
        ) 
    GO
    
    
    -- In-Memory table, Durable
    Use SQLSeverHelp_IMO
    GO
    CREATE TABLE DurableInMemoryOptimizedTable
    (
    iID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
    vFName VARCHAR(20) NOT NULL,
    vLName VARCHAR(20) NOT NULL
    ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
    GO
    
    -- In-Memory table, non-durable
    Use SQLSeverHelp_IMO
    GO
    CREATE TABLE NonDurableInMemoryOptimizedTable
    (
    iID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
    vFName VARCHAR(20) NOT NULL,
    vLName VARCHAR(20) NOT NULL
    ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)
    GO
    
    

    Since both tables are memory optimized, both would have their own DLLs generated. We have discussed DLLs for table in earlier blog here.

    image

    Let’s load same amount of data in both tables. I have randomly selected 90000 rows (multiple of three – as I have to insert three important names). First I am inserting into Durable table using below script.

    -- Inserting 90000 rows into Durable table.
    set nocount on
    go
    DECLARE @counter INT 
    SET @counter = 1
    
    WHILE @counter <= 90000 
      BEGIN 
          INSERT INTO dbo.DurableInMemoryOptimizedTable 
          VALUES      (@counter, 'SQLServer-Help.com','Balmukund Lakhani'),
                      (@counter+1, 'ExtremeExperts.com','Vinod Kumar M'),
                      (@counter+2, 'SQLAuthority.com','Pinal Dave') 
    
          SET @counter = @counter + 3
      END
    

    image

    as we can see it was around 30 seconds on my VM. and now.. non-durable table using below script.

    set nocount on
    go
    DECLARE @counter INT 
    SET @counter = 1
    
    WHILE @counter <= 90000 
      BEGIN 
          INSERT INTO dbo.NonDurableInMemoryOptimizedTable 
          VALUES      (@counter, 'SQLServer-Help.com','Balmukund Lakhani'),
                      (@counter+1, 'ExtremeExperts.com','Vinod Kumar M'),
                      (@counter+2, 'SQLAuthority.com','Pinal Dave') 
    
          SET @counter = @counter + 3
      END
    

    image

    How much time do you see in above screen shot? zero ?Trust me, it was almost instantaneous! You don’t trust me? Let count rows from both the tables.

    -- Verify data is present in both tables.
    select COUNT(*) from NonDurableInMemoryOptimizedTable
    go
    select COUNT(*) from DurableInMemoryOptimizedTable
    go
    

    image

    Still don’t trust me? Try yourself!

    Now, the test of survival! I would take database offline and bring it online. For SCHEMA_ONLY durable table, the data would be cleaned, but schema would remain. Let’s test.

    -- take database offline and bring online.
    Use Master
    go
    ALTER DATABASE SQLSeverHelp_IMO SET OFFLINE WITH ROLLBACK IMMEDIATE
    GO
    ALTER DATABASE SQLSeverHelp_IMO SET ONLINE
    GO
    -- count the rows again
    USE SQLSeverHelp_IMO
    go
    select COUNT(*) from NonDurableInMemoryOptimizedTable
    go
    select COUNT(*) from DurableInMemoryOptimizedTable
    go
    
    

    image

    As expected, rows in NonDurableInMemoryOptimizedTable couldn’t survive database restart.

    To summarize, for non durable table only schema (table definition) would be available after restart/reboot/crash. In other words, it’s a permanent table with temporary data (consistent till restart). Non-durable table are faster in data loading at the cost of data durability. They would be a perfect fit for certain requirements need but useless for others. You MUST evaluate and understand risk of non durable tables before putting them in production. 

    Hope you have learned something new.

  • 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: , , , , , , , , , , , , , , , , | 5 Comments »