Friday, October 18, 2013

In-Memory OLTP: Inserting 3 millions records in 3 seconds

One of the sessions we delivered with SQL Server Sri Lanka User Group Meeting – October 2013 was related to SQL Server 2014: In-Memory OLTP. I am sure that everyone was thrilled seeing, inserting millions of records into SQL Server table within seconds. Initially it amazed me too :), I believe that this is the key for 2014, just like Columnstore in 2012.

Here is the demo code I used with the session.

During the demonstration, I showed two scenarios based on three tables;

  1. Normal disk-based table
  2. In-Memory Durable table
  3. In-Memory non-durable table.

Once scenario is inserting records from another table. Other is inserting another set with a stored procedure. Before going through the code, let’s understand In-Memory OLTP.

In-Memory OLTP is a database engine component which is optimized for accessing memory-resident tables. This was started 4 years ago with codename “Hekaton”. It is mainly for OLTP scenario and it significantly improves the performance of OLTP workloads. Here are some of key points on it;

  • Currently available with SQL Server 2014 - 64bit CTP1, CTP2.
  • Tables can be created in memory as fully Durable or Non-Durable.
  • Content of durable tables will not be lost in case of server crash.
  • Non-durable tables are fully in memory. Content will be lost at server crash (including service restart).
  • Indexes created for in-memory tables are in memory only.
  • T-SQL can be written for accessing both in-memory tables and disk tables.
  • In-memory tables are fully supported for ACID.
  • Stored procedures written for accessing only in-memory tables can be made as natively compiled procedures.

Statistics

Let’s see the code. I have already created a DB called TestDatabase01 and it has a table called dbo.FactSales. This table is from ContosoRetailDW which is available at: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=18279.

First step is enable the database for in-memory tables. It is done by adding a file group specifically for in-memory tables and add a file to it.

ALTER DATABASE [TestDatabase01] ADD FILEGROUP FG_MemoryOptiimizedData 
CONTAINS MEMORY_OPTIMIZED_DATA
GO
 
ALTER DATABASE TestDatabase01 ADD FILE 
    (NAME = 'TestDatabase01_MemoryOptiimizedData'
    , FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDatabase01_MemoryOptiimizedData.ndf') 
TO FILEGROUP FG_MemoryOptiimizedData
GO

Next step is creating tables. This code creates three tables as mentioned above.

-- creating a normal disk table
CREATE TABLE dbo.NewFactSales
    (
        SalesKey int NOT NULL PRIMARY KEY 
        , DateKey datetime NOT NULL
        , channelKey int NOT NULL
        , StoreKey int NOT NULL
        , ProductKey int NOT NULL
        , PromotionKey int NOT NULL
        , CurrencyKey int NOT NULL
        , UnitCost money NOT NULL
        , UnitPrice money NOT NULL
        , SalesQuantity int NOT NULL
        , ReturnQuantity int NOT NULL
        , ReturnAmount money NULL
        , DiscountQuantity int NULL
        , DiscountAmount money NULL
        , TotalCost money NOT NULL
        , SalesAmount money NOT NULL
        , ETLLoadID int NULL
        , LoadDate datetime NULL
        , UpdateDate datetime NULL
    )
GO
 
-- creating an in-memory table.
-- Option DURABILITY = SCHEMA_AND_DATA makes table durable
CREATE TABLE dbo.FactSales_MemoryOptimized_Durable
 
        SalesKey int NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 3000000)
        , DateKey datetime NOT NULL
        , channelKey int NOT NULL
        , StoreKey int NOT NULL
        , ProductKey int NOT NULL
        , PromotionKey int NOT NULL
        , CurrencyKey int NOT NULL
        , UnitCost money NOT NULL
        , UnitPrice money NOT NULL
        , SalesQuantity int NOT NULL
        , ReturnQuantity int NOT NULL
        , ReturnAmount money NULL
        , DiscountQuantity int NULL
        , DiscountAmount money NULL
        , TotalCost money NOT NULL
        , SalesAmount money NOT NULL
        , ETLLoadID int NULL
        , LoadDate datetime NULL
        , UpdateDate datetime NULL
    )
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
 
-- creating another in-memory table.
-- Option DURABILITY = SCHEMA_ONLY, this is non-durable
CREATE TABLE dbo.FactSales_MemoryOptimized_NonDurable
    (
        SalesKey int NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 3000000)
        , DateKey datetime NOT NULL
        , channelKey int NOT NULL
        , StoreKey int NOT NULL
        , ProductKey int NOT NULL
        , PromotionKey int NOT NULL
        , CurrencyKey int NOT NULL
        , UnitCost money NOT NULL
        , UnitPrice money NOT NULL
        , SalesQuantity int NOT NULL
        , ReturnQuantity int NOT NULL
        , ReturnAmount money NULL
        , DiscountQuantity int NULL
        , DiscountAmount money NULL
        , TotalCost money NOT NULL
        , SalesAmount money NOT NULL
        , ETLLoadID int NULL
        , LoadDate datetime NULL
        , UpdateDate datetime NULL
    )
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)
GO

Let’s insert records. Insert three statements separately for seeing the difference.

-- Insert into disk table
INSERT INTO dbo.NewFactSales
(
   SalesKey, DateKey, channelKey, StoreKey
   , ProductKey, PromotionKey, CurrencyKey
   , UnitCost, UnitPrice, SalesQuantity, ReturnQuantity
   , ReturnAmount, DiscountQuantity, DiscountAmount
   , TotalCost, SalesAmount, ETLLoadID, LoadDate, UpdateDate
)
SELECT 
   SalesKey, DateKey, channelKey, StoreKey
   , ProductKey, PromotionKey, CurrencyKey
   , UnitCost, UnitPrice, SalesQuantity, ReturnQuantity
   , ReturnAmount, DiscountQuantity, DiscountAmount
   , TotalCost, SalesAmount, ETLLoadID, LoadDate, UpdateDate
FROM dbo.FactSales
 
-- Insert into in-memory durable table
INSERT INTO dbo.FactSales_MemoryOptimized_Durable
(
   SalesKey, DateKey, channelKey, StoreKey
   , ProductKey, PromotionKey, CurrencyKey
   , UnitCost, UnitPrice, SalesQuantity, ReturnQuantity
   , ReturnAmount, DiscountQuantity, DiscountAmount
   , TotalCost, SalesAmount, ETLLoadID, LoadDate, UpdateDate
)
SELECT 
   SalesKey, DateKey, channelKey, StoreKey
   , ProductKey, PromotionKey, CurrencyKey
   , UnitCost, UnitPrice, SalesQuantity, ReturnQuantity
   , ReturnAmount, DiscountQuantity, DiscountAmount
   , TotalCost, SalesAmount, ETLLoadID, LoadDate, UpdateDate
FROM dbo.FactSales
 
-- Insert into in-memory non-durable table
INSERT INTO dbo.FactSales_MemoryOptimized_NonDurable
(
   SalesKey, DateKey, channelKey, StoreKey
   , ProductKey, PromotionKey, CurrencyKey
   , UnitCost, UnitPrice, SalesQuantity, ReturnQuantity
   , ReturnAmount, DiscountQuantity, DiscountAmount
   , TotalCost, SalesAmount, ETLLoadID, LoadDate, UpdateDate
)
SELECT 
   SalesKey, DateKey, channelKey, StoreKey
   , ProductKey, PromotionKey, CurrencyKey
   , UnitCost, UnitPrice, SalesQuantity, ReturnQuantity
   , ReturnAmount, DiscountQuantity, DiscountAmount
   , TotalCost, SalesAmount, ETLLoadID, LoadDate, UpdateDate
FROM dbo.FactSales

My VM is configured with 5.5GB memory. Three INSERT statements took 00:01:46, 00:00:17, and 00:00:09 respectively. As you see clearly, in-memory optimized tables give better performance than disk based tables. Let’s run the same with stored procedures. Below code adds three stored procedures. Note the second and third. Few options have been added to them, making them as natively compiled procedures. In addition to that, isolation level and language options are added too. Currently they are required for these procedures.

Note that current version requires manual statistics updates for in-memory tables.

UPDATE STATISTICS dbo.FactSales_MemoryOptimized_Durable WITH FULLSCAN, NORECOMPUTE
UPDATE STATISTICS dbo.FactSales_MemoryOptimized_NonDurable WITH FULLSCAN, NORECOMPUTE

Now let’s add procedures and run, and see the time each takes.

-- Procedure inserts records to disk table
CREATE PROCEDURE dbo.Insert_FactSales
AS
BEGIN
 
    DECLARE @a int = 4000000
    WHILE (@a < 7000000)
    BEGIN
        INSERT INTO dbo.NewFactSales
            (SalesKey,DateKey,channelKey,StoreKey,ProductKey,PromotionKey,CurrencyKey,UnitCost
            ,UnitPrice,SalesQuantity,ReturnQuantity,ReturnAmount,DiscountQuantity,DiscountAmount
            ,TotalCost,SalesAmount,ETLLoadID,LoadDate,UpdateDate)
        VALUES
            (@a,'2013-01-01',1,1,1,1,1,1
            ,1,1,1,1,1,1
            ,1,1,1,'2013-01-01','2013-01-01')
 
        SET @a = @a + 1
    END
END
GO
 
-- Procedure inserts records to in-memory durable table
CREATE PROCEDURE dbo.Insert_FactSales_MemoryOptimized_Durable
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
    WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_English')
 
    DECLARE @a int = 4000000
    WHILE (@a < 7000000)
    BEGIN
        INSERT INTO dbo.FactSales_MemoryOptimized_Durable
            (SalesKey,DateKey,channelKey,StoreKey,ProductKey,PromotionKey,CurrencyKey,UnitCost
            ,UnitPrice,SalesQuantity,ReturnQuantity,ReturnAmount,DiscountQuantity,DiscountAmount
            ,TotalCost,SalesAmount,ETLLoadID,LoadDate,UpdateDate)
        VALUES
            (@a,'2013-01-01',1,1,1,1,1,1
            ,1,1,1,1,1,1
            ,1,1,1,'2013-01-01','2013-01-01')
 
        SET @a = @a + 1
    END
END
GO
 
-- Procedure inserts records to in-memory non-durable table
CREATE PROCEDURE dbo.Insert_FactSales_MemoryOptimized_NonDurable
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
    WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_English')
 
    DECLARE @a int = 4000000
    WHILE (@a < 7000000)
    BEGIN
        INSERT INTO dbo.FactSales_MemoryOptimized_NonDurable
            (SalesKey,DateKey,channelKey,StoreKey,ProductKey,PromotionKey,CurrencyKey,UnitCost
            ,UnitPrice,SalesQuantity,ReturnQuantity,ReturnAmount,DiscountQuantity,DiscountAmount
            ,TotalCost,SalesAmount,ETLLoadID,LoadDate,UpdateDate)
        VALUES
            (@a,'2013-01-01',1,1,1,1,1,1
            ,1,1,1,1,1,1
            ,1,1,1,'2013-01-01','2013-01-01')
 
        SET @a = @a + 1
    END
END
GO
 
EXEC dbo.Insert_FactSales
EXEC dbo.Insert_FactSales_MemoryOptimized_Durable
EXEC dbo.Insert_FactSales_MemoryOptimized_NonDurable

Again, natively compiled procedures against give better performance than others. Here is the summary;

Insert 3 million records Disk table In-Memory table – Durable In-Memory table - NonDurable
INSERT statement 00:01:46 00:00:17 00:00:09
SP 00:15:19 00:00:19
(Natively Compiled)
00:00:03
(Natively Compiled)

Expect more codes on this with future posts. Code related to this can be downloaded from: http://sdrv.ms/18sAxEa

The presentation used for the session can be downloaded from: http://sdrv.ms/1d2xSoe

SQL 2014 is still being developed. However, for testing purposes, you can download CTP 2 from here: http://technet.microsoft.com/en-us/evalcenter/dn205290.aspx

Here are useful links on this;

Thursday, October 17, 2013

SQL Server 2014 CTP1: Error 41342 - The model of the processor on the system does not support creating filegroups with MEMORY_OPTIMIZED_DATA….

You might have already faced for this, or you will be, if you try to create a file group for your in-memory optimized tables. The reason for this is, in-memory optimized tables require a processor that supports atomic compare and exchange operations on 128-bit values (as per this:http://msdn.microsoft.com/en-us/library/dn232521(v=sql.120).aspx, I do not have much idea on it :)). This requires assembly instruction CMPXCHG16B. Certain models do not support this. Certain virtual environments do not enable this by default.

My virtual environment is VirtualBox. Here is the way of enabling CMPXCHG16B instruction set;

  • Get the list of all VMs configured using VBoxManage.exe list vms
  • Then enable it using VBoxManage.exe setextradata “VM Name” VBoxInternal/CPUM/CMPXCHG16B 1

VirtualBox error

The internal changes on executing this command is unknown. Hence be cautious on this before enabling this in production environments.

Wednesday, October 16, 2013

SQL Server 2014 CTP2 is available for downloading

SQL Server 2014 CTP2 is available for downloading;

http://technet.microsoft.com/en-us/evalcenter/dn205290.aspx

Few important things to remember with this installation;

  • The Microsoft SQL Server 2014 CTP2 release is NOT supported by Microsoft Customer Services and Support (CSS).
  • The Microsoft SQL Server 2014 CTP2 release is available for testing purposes only and should NOT be installed and used in production environments.
    • Side-by-Side installation with down-level production SQL Server instances as well as in-place Upgrades of down-level production SQL Server instances, is NOT supported.
  • Upgrading from Microsoft SQL Server 2014 CTP1 to Microsoft SQL Server 2014 CTP2 is NOT supported.

Sunday, October 6, 2013

SQL Server 2008 R2 Error: Login failed for user ''. Reason: Server is in script upgrade mode. Only administrator can connect at this time. (Microsoft SQL Server, Error: 18401)

I started experiencing above error with my SQL Server 2008 R2 instance when trying to connect and spent about 2 hours for sorting it out. It said that the instance was being upgraded but I was 100% sure that no upgrades were added. I searched for this error and almost all who have experienced the same had accepted that this was due to an upgrade and almost all cases had automatically been resolved within few minutes.

As the next step, error log was checked and found the reason;

2013-10-06 18:30:15.89 spid7s      Error: 5133, Severity: 16, State: 1.
2013-10-06 18:30:15.89 spid7s      Directory lookup for the file "D:\Databases\Relational\2008R2\temp_MS_AgentSigningCertificate_database.mdf" failed with the operating system error 3(The system cannot find the path specified.).
2013-10-06 18:30:15.89 spid7s      Error: 1802, Severity: 16, State: 1.
2013-10-06 18:30:15.89 spid7s      CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
2013-10-06 18:30:15.89 spid7s      Error: 912, Severity: 21, State: 2.
2013-10-06 18:30:15.89 spid7s      Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 598, state 1, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting.

As per the log, server still looks an old path for databases. I had changed the default database path recently but not today. However now it started looking the old path. Reason could be hibernating the machine without shutting down. Now how can I instruct SQL Server not to look for this path?

As per the search I made, there are two locations to be checked;

  1. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.<<instance name>>\Setup
    • DefaultData string value
    • DefaultLog string value
  2. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.<<instance name>>\MSSQLServer
    • SQLDataRoot string value

ErrorLog1

ErrorLog2

In my case, path in the first key was wrong. Issue was resolved once the string value is updated with the correct path. You may experience the same and this could be the solution ....

Saturday, October 5, 2013

Indexing with SQL Server – SS SLUG Aug 2013 - Presentation

Indexin with SQL Server

Here is the presentation used for discussing SQL Server indexing. This discusses index structures, storage patters and different implementations addressing all versions, including SQL Server 2014.

http://sdrv.ms/1e1iAOn

This contains 40 slides including links for demo codes. Slides include;

  • SQL Server Table Structures
  • SQL Server Index Structures – Rowstore indexes
  • Managing Rowstore Indexes
  • SQL Server Index Structures – Columnstore indexes

What is Columnstore Index - SS SLUG Aug 2013 – Demo V

If you have worked with large databases (or data warehouses), you have already seen enough of scenarios where you do not get much benefits out of indexes, particularly with large data tables. SQL Server 2012 ColumnStore index structure was specifically introduced for addressing this issue.

This new structure is based on Microsoft Vertipaq technology and it goes as a non-clustered index. The different between this and traditional indexing is the storage. The indexes we have worked so far store data in a row-wise structure where as this stores data in a column-wise structure. Another different which make this unusable with OLTP databases is, structure becomes Read-Only.

 ColumnStore1

How this exactly stores data was discussed in the user group meeting. Here are set of images that explains how SQL Server organizes data for ColumnStore indexes.

ColumnStore2

The first image shows a table that contains millions of records with hundreds of columns. If you execute a command for creating a ColumnStore non-clustered index, the first step of SQL Server is grouping records (image 2). Then it creates segments for each column in each group. Third image shows how 16 segments are created. SQL Server uses Dictionary Compression for compressing these segments. These segments will be stored in LOBs and become unit of transfer between the disk and the memory.

Let’s see how it can be used and the performance benefit. This sample code uses ContosoRetailDW and it can be downloaded at: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=18279.

Have a look on following query. The query accesses three tables (one is very large) and performs set of aggregations.

USE [ContosoRetailDW]
GO
 
SET STATISTICS IO ON
SET STATISTICS TIME ON
 
SELECT 
    p.ProductName
    , d.CalendarYear
    , d. CalendarMonth
    , COUNT(*) TotalOrders
    , SUM(f.SalesQuantity) Quantity
    , SUM(SalesAmount) Amount
    , SUM(CASE WHEN f.DateKey = '2009-12-31 00:00:00.000' THEN SalesAmount ELSE 0 END) AmountToday
FROM dbo.FactSales f
    INNER JOIN dbo.DimDate d
        ON d.Datekey = f.DateKey
    INNER JOIN dbo.DimProduct p
        ON p.ProductKey = f.ProductKey
GROUP BY
    p.ProductName
    , d.CalendarYear
    , d. CalendarMonth
ORDER BY 1, 2, 3
 
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

It has read around 19,000 pages and has taken 40 seconds.

Statistics

Now, let’s create a ColumnStore index on the table. The recommended way is, include all columns when creating the index.

CREATE COLUMNSTORE INDEX IX_FactSales_CStore ON dbo.FactSales (
        SalesKey, DateKey, channelKey, StoreKey, ProductKey, PromotionKey, CurrencyKey
        , UnitCost, UnitPrice, SalesQuantity, ReturnQuantity, ReturnAmount, DiscountQuantity
        , DiscountAmount, TotalCost, SalesAmount,ETLLoadID, LoadDate, UpdateDate)

This might takes minutes based on the number of records in the table. Once it is created, run the SELECT again and see the result.

Statistics2

As you see, number of pages that have been read is around 8,000 and time taken is 2 seconds. It is a significant improvement. However the biggest issue with this is, now table has become a read-only table. However this can be applied in partition level and partitions that contain history data can be benefited from this.

This link: http://technet.microsoft.com/en-us/library/gg492088.aspx gives all info regarding ColumnStore indexes. Have a look on it for understanding restrictions, limitations and scenarios you can use it.

Wednesday, October 2, 2013

Analysis Services - Backup and restore errors: File '.abf' specified in Restore command is damaged or is not an AS backup file. The following system error occurred: Access is denied. (Microsoft SQL Server 2008 R2 Analysis Services)

If you experience this error when an SQL Server Analysis Services backup is restored, most probably the reason is Analysis Services – Service account has no permission on the file (or folder). Simply grant permission for the account by accessing its properties – Security tab.

Analysis Services