Monday, December 30, 2013

Can we commit inner (or nested) transactions?– SS SLUG Dec 2013 – Brain Bashers - Demo IV

This post is related to the presentation “SQL Brain Bashers” I did at SQL Sever Sri Lanka User Group Meeting. This speaks about committing nested transactions.

Here is the question related to this demo;

Can we commit the inner transaction (or nested transaction) without committing the most outer transaction?

Answer is simple. The concept of nested transaction does not exist with Microsoft SQL Server though we can have multiple BEGIN TRAN and COMMIT TRAN statements respectively.

What exactly happen with inner BEGIN TRAN and inner COMMIT TRAN?
They do nothing but increases and decreases @@TRANCOUNT. Note that @@TRANCOUNT giving a value greater than one does not mean that there are more than one transactions. It means there are more than one BEGIN TRAN statements. Committing all statements is actually done by most outer COMMIT TRAN statement.

Then what is the purpose of nested transaction in SQL Server?
It is meaningless to have multiple BEGIN TRAN statements in a same scope (example, in one stored procedure). However this allows to track the count of BEGIN TRAN in nested operations; Stored procedure executes BEGIN TRAN and then calls another stored procedure that has BEGIN TRAN. The count helps SQL Server to determine at which COMMIT TRAN actions should be committed.

Are you sure?
You may ask that question. Let’s test this and see.

Let’s execute the following code. It creates a database and a table, then inserts two records after starting a transaction with BEGIN TRAN.

CREATE DATABASE TestDB
GO
 
USE TestDB
GO
 
-- create a table 
CREATE TABLE dbo.TestTable
(
    Id int PRIMARY KEY
    , Value char(500) NOT NULL
)
GO
 
-- start a transaction
BEGIN TRAN
 
-- do some actions
INSERT INTO dbo.TestTable
    (Id, Value)
VALUES
    (1, 'Test Value 1')
INSERT INTO dbo.TestTable
    (Id, Value)
VALUES
    (2, 'Test Value 2')

Let’s check the @@TRANCOUNT and bytes used for this transaction.

SELECT @@TRANCOUNT AS TransactionCount
 
SELECT database_transaction_log_bytes_used
FROM sys.dm_tran_database_transactions
WHERE database_id = DB_ID('TestDB')

Transaction1 

As you see, the count is 1 and 1408 bytes used for the transaction. Let’s have another BEGIN TRAN and some INSERTs, and check the same.

-- add another BEGIN TRAN
BEGIN TRAN

-- do some actions
INSERT INTO dbo.TestTable
    (Id, Value)
VALUES
    (3, 'Test Value 3')
INSERT INTO dbo.TestTable
    (Id, Value)
VALUES
    (4, 'Test Value 4')


SELECT @@TRANCOUNT AS TransactionCount

SELECT database_transaction_log_bytes_used
FROM sys.dm_tran_database_transactions
WHERE database_id = DB_ID('TestDB')

Transaction2

It increases the count and bytes used. Now, let’s have one COMMIT TRAN statement and check again.

COMMIT TRAN
 
SELECT @@TRANCOUNT AS TransactionCount
 
SELECT database_transaction_log_bytes_used
FROM sys.dm_tran_database_transactions
WHERE database_id = DB_ID('TestDB')

Transaction3

Now @@TRANCOUNT is again 1 but number of bytes used is same. This clearly shows that inner COMMIT TRAN statements do not do anything other than decreasing @@TRANCOUNT. Let’s execute the COMMIT TRAN again and see.

COMMIT TRAN
 
SELECT @@TRANCOUNT AS TransactionCount
 
SELECT database_transaction_log_bytes_used
FROM sys.dm_tran_database_transactions
WHERE database_id = DB_ID('TestDB')

Transaction4

Everything is clear now. This proves that inner BEGIN TRAN and COMMIT TRAN do not do anything other than changing the @@TRANCOUNT.

Can I rollback part of the actions performed in my transaction?
Yes, it is possible with SAVE TRAN statement. It allows to set savepoints in the transaction and rollback the transaction to the savepoint.

Sunday, December 29, 2013

Best way to pass datetime values to SQL Server – SS SLUG Dec 2013 – Brain Bashers - Demo III

This post is related to the presentation “SQL Brain Bashers” I did at SQL Sever Sri Lanka User Group Meeting. This speaks about different ways of passing datetime values to SQL Server databases.

Here is the question related to this demo;

There are many ways to pass datetime values to SQL Server. Some of ways are;

  1. Value formatted as a string value
  2. Value formatted as a datetime value
  3. Value formatted as a datetime value and accepted via DATEFORMAT option
  4. Value formatted as a ISO 8601 datetime value

What is the best way?

Majority uses the second option. It works fine as long as all applications are using correct datetime formats and all are in same country. However there could be misinterpretation if one of the application uses a different convention. Have a look on below C#.NET code (Note that the code written to show the demo only, it may not be the correct way to be written).

USE tempdb
GO
CREATE PROCEDURE dbo.ShowMonth @DateTimeValue datetime
    , @Month varchar(10) OUTPUT
AS
BEGIN
 
    SET @Month = DATENAME(m, @DateTimeValue)
END
GO

 

string year, month, date, datetime;
            
// making the datetime value from different string values
year = "2013";
month = "05";
date = "01";
datetime = month + "/" + date + "/" + year + " 00:00:00";
DateTime convertedDate = Convert.ToDateTime(datetime);
 
// set connection and command
SqlConnection connection = new SqlConnection(@"Server=SL-DEV001\SQL2012;Database=tempdb;uid=Dinesh;pwd=Dinesh");
SqlCommand command = new SqlCommand("dbo.ShowMonth", connection);
command.CommandType = CommandType.StoredProcedure;
 
SqlParameter parameterDateTime = new SqlParameter("DateTimeValue", SqlDbType.DateTime);
parameterDateTime.Value = convertedDate;
parameterDateTime.Direction = ParameterDirection.Input;
command.Parameters.Add(parameterDateTime);
 
SqlParameter parameterMonth = new SqlParameter("Month", SqlDbType.VarChar, 10);
parameterMonth.Direction = ParameterDirection.Output;
command.Parameters.Add(parameterMonth);
 
connection.Open();
command.ExecuteNonQuery();
connection.Close();
 
Console.WriteLine(parameterMonth.Value.ToString());

When you execute the code, it returns the “month” as follows;

Output1

Now let’s change the “Culture” of the current thread. Add the below code as the first statement of C#.NET code.

Thread.CurrentThread.CurrentCulture = new CultureInfo("fr-FR");

And when the code is run, result is;

Output2

As you see, “month” returns from the stored procedure is now wrong. It means, Date has been misinterpreted by either application or SQL Server.

Now let’s change the way we pass the date and time as per ISO 8601;

//datetime = month + "/" + date + "/" + year + " 00:00:00";
datetime = year + "-" + month + "-" + date +  "T00:00:00";

If you execute the code now, SQL Server identifies the elements of passed value properly regardless of Culture setting and returns the value correctly. Therefore 4th option is the best way to pass datetime values to SQL Server.

ISO 8601
This is an international standard for exchanging dates and times. This standard provides an unambiguous and well-defined method of representing dates and times and avoids misinterpretation of dates and times.

Format of this standard separates date and time using “T” (uppercase). Year needs to be set with 4 digits, month needs to be set with 2 digits and, day needs to be set with 2 digits. Time sets in 24 hours format. Hour, minute and second are separated using “:” and fraction of second is separated using “.”.

For an example, “8-Jan-2013 2:45:44” as per ISO 8601 is “2013-01-08T14:45:44.000”.

When this is used with SQL Server, setting related to SET DATEFORMAT and SET LANGUAGE is ignored.

If you need to convert stored datetime values to ISO 8601 for exchanging them with another party, here is the way;

SELECT CONVERT(char(23), DateValue, 126)

You can use 127 instead of 126 if time zone information is required.

Read more on ISO 8601: http://en.wikipedia.org/wiki/ISO_8601#Dates

Wednesday, December 25, 2013

How SQL Server interprets two-digit years as four-digit years – SS SLUG Dec 2013 – Brain Bashers - Demo II

This is based on the presentation titled “SQL Brain Bashers” I did at SQL Sever Sri Lanka User Group Meeting. This discusses the way SQL Server interprets two-digit years to four-digit years.

Here is the question related to this demo;

You execute the following query. Note that the “years” of input values are sent as two-digit years.

DECLARE @datetime datetime
SET @datetime = '01/01/00'
SELECT YEAR (@datetime)
 
SET @datetime = '01/01/30'
SELECT YEAR (@datetime)
 
SET @datetime = '01/01/40'
SELECT YEAR (@datetime)
 
SET @datetime = '01/01/50'
SELECT YEAR (@datetime)

How SQL Server interprets each year to four-digit years?

In order to answer this question, you need to know the two digit year cutoff which SQL Server uses for determining the year. The default value of two digit year cutoff is 2049. If you pass a two-digit year that is less than or equal to last two digits of cutoff year (2049), SQL Server uses the same century as the cutoff year. Here is the result of above query;

Datetime2

Since the 50 is above cutoff year, it has been interpreted as 1950. All other two-digit years are set with century of the cutoff year.

You can change the cutoff year if required. This option appears only if “show advanced options” is enabled via sp_configure. If “show advanced options” is not enabled, enable it. Then, set the value you need for “two digit year cutoff”. The below code sets it as 2060.

-- enable advanced options
sp_configure 'show advanced options', 1
GO
reconfigure
 
-- set two digit year cutoff as 2060
sp_configure 'two digit year cutoff', 2060
GO
reconfigure

Let’s run the query again and see;

Datetime3

As you see, 50 is less than current cutoff year. Therefore even last value is taken as 2050. Change it back to 2049 to maintain the backward compatibility.

You need to know one more thing on this setting. If you are accepting year values via an OLE Automation object, you need to be aware that its cutoff year is 2030, not 2040. For an example, if a .NET application is used for sending values for a stored procedure and values are set as two-digit years, you may notice unacceptable years. Look at below code and see. If two-digit values are less than last two digit of cutoff year, it uses century of cutoff year.

DateTime dt;
dt = Convert.ToDateTime("01-01-00");
Console.WriteLine(dt.ToString());
dt = Convert.ToDateTime("01-01-30");
Console.WriteLine(dt.ToString());
dt = Convert.ToDateTime("01-01-40");
Console.WriteLine(dt.ToString());
dt = Convert.ToDateTime("01-01-50");
Console.WriteLine(dt.ToString());

Datetime4

Tuesday, December 24, 2013

Filtering DateTime values – SS SLUG Dec 2013 – SQL Brain Bashers - Demo I

This is based on the presentation titled “SQL Brain Bashers” I did at SQL Sever Sri Lanka User Group Meeting. This discusses a tricky (or error-prone) filtering issue with datetime values.

Here is the question related to this demo;

You have a table that contains two columns as below. First column type is int and second is datetime.

Id (int)

Date (datetime)

1

2010-06-12 09:17:19.320

2 2010-06-12 23:59:59.997
3 2010-06-13 00:00:00.000
4 2010-06-13 13:30:00.347
5 2010-06-13 23:59:59.997
6 2010-06-14 00:00:00.000
7 2010-06-14 00:00:00.420

You write a query for getting records related only to 2010-06-13;

SELECT Id, Date
FROM dbo.DateTimeValues
WHERE Date BETWEEN '06/13/2010 00:00:00:000' 
AND '06/13/2010 23:59:59:999'

Does this produce the required resultset?

Answer is “No”. Result includes 6th Id too, which is related to 2010-06-14.

Datetime

The reason for this is, accuracy level of datetime data type and storage mechanism of datetime type. SQL Server only stores time to approximately 1/300th of a second (.00333 seconds), meaning time is stored as;

00:00:00:000
00:00:00:003
00:00:00:007

Because of this, when a value like “2013-12-25 10:30:50:995” is used, it gets rounded up to “2013-12-25 10:30:50:997”. In the query given with the example, “06/13/2010 23:59:59:999” is rounded up to “06/14/2010 00:00:00:000”, resulting 2010-06-14 record in the result.

However, this query can be written in a different way to get the required result;

SELECT Id, Date
FROM dbo.DateTimeValues
WHERE Date >=  '06/13/2010 00:00:00:000' 
    AND Date < '06/14/2010 00:00:00:000'

If you need to accuracy level of datetime for more than 0.00333 seconds, solution is, using datetime2 data type instead datetime. The accuracy of datetime2 data type goes up to 100 nanoseconds.

Monday, December 16, 2013

Finding pages (or physical location) of records: %%PHYSLOC%% and fn_PhysLocCracker

There are many instances which we need to find the physical location, specifically the page, related to a record. Good example could be finding records in a corrupted page, or finding records’ distribution in pages. In most cases, I have use DBCC IND and DBCC PAGE but there two more great functions that can be used for finding the same.

The first function is %%PHYSLOC%%. This returns the RID (Record Identifier) as a hexadecimal value. The RID consists file number, page number and, record number.

SELECT p.BusinessEntityID, p.Title
    , p.FirstName, p.LastName
    , %%PHYSLOC%%
FROM [Person].[Person] p

The second function is a table-valued function which is fn_PhysLocCracker. It accepts the RID returning from %%PHYSLOC%% and returns three-columned table for file number, page number and, record number. Here is the way for using it;

SELECT p.BusinessEntityID, p.Title
    , p.FirstName, p.LastName
    , ph.*
FROM [Person].[Person] p
CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) ph

Table

Sunday, November 24, 2013

Quick Note on SQL Server 2012 Licensing Cost

When it comes to purchasing Microsoft SQL Server, I know for a fact, many finds difficulty on selecting the edition and calculating the cost. Here is a quick note I made on it, it was based on a discussion had for selecting the edition and licensing it.

  Enterprise Business Intelligence Standard
Options available Core-based only Server + CALs only Core-based
or
Server + CALs
Price USD 6,874 per core
(four-core minimum per socket)
Server – USD 8,592
CAL – USD 209
USD 1,793 per core
(four-core minimum per socket)
-
Server – USD 898 CAL – USD 209

Again, calculating the price for core-based is not just core price into number of cores. It is based on something called Core Factor. Refer this table to determine the core factor for your processor;

Processor Type Core Factor
All processors not mentioned below 1
AMD Processors 31XX, 32XX, 41XX, 42XX, 61XX, 62XX Series Processors with 6 or more cores 0.75
Single-Core Processors 4
Dual-Core Processors 2

Number of core licenses to be purchased is based on formula: Number of cores * Core Factor. Here is an example for calculating core-based cost for Enterprise Edition;

1. 2 Intel Xeon 6-core processors

Total physical cores – 12
Core factor – 1
Core licenses required = 12 x 1 = 12
Cost for Enterprise Edition = 12 x USD 6,874 = USD 82,488

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

Sunday, September 29, 2013

Understanding Indexed Views - SS SLUG Aug 2013 – Demo IV

Here is another post on a demo did at Aug meet-up. This post speaks about Indexed Views.

As the name says, views can be made as Indexed Views by adding indexes on them. If you are not familiar with this concept, a general question comes up; Views are not physical objects, SQL Server holds the definitions of them only, then how indexes are maintained with views? Here is the answer if you have the same questions; Once a clustered index is added to a view, it becomes a physical object, it becomes something similar to a table, it becomes an Indexed View.

What is the point of adding an index to a view? Can we improve the performance of a normal view that contains simple, single-table query? No, we cannot. This is not for such queries. This is typically for views that are formed with multiple tables and contains aggregations. Assume that you have a view that joins 2-3 tables with millions of records and then performs set of aggregations. Aggregations reduce number of records returned but it still needs to read all records, increasing IO operations. If all three tables are maintained with 30,000 pages, whenever the view is accessed, all 30,000 pages have to be read and then aggregations should be done. Making this view as an Indexed view, number of pages to be read is eliminated and aggregation is not required to be performed. This definitely improves the performance.

Here is the demo code shown with the presentation on it.

USE AdventureWorks
GO
 
-- create a view from Sales header and details
CREATE VIEW dbo.GetSales
AS
SELECT h.SalesOrderID, h.CustomerID, SUM(d.LineTotal) LineTotal  
FROM Sales.SalesOrderHeader h
    INNER JOIN Sales.SalesOrderDetail d
        ON h.SalesOrderID = d.SalesOrderID
GROUP BY h.SalesOrderID, h.CustomerID
GO
 
-- View data from GetSales view
-- Note the number pages reading during data retrieval
SET STATISTICS IO ON
 
SELECT * FROM dbo.GetSales
 
SET STATISTICS IO OFF

Look at the output and the plan;

image

image

Now let’s make the view as an Indexed view. There are many rules to be followed, key rules are listed below;

  1. All columns in the view must be deterministic.

  2. The SCHEMA_BINDING view option must be should when creating.

  3. The clustered index must be created as unique.

  4. Tables references in the view must be two-part naming.

  5. The COUNT_BIG() function must be included if aggregation is used.

  6. Some aggregations, such as AVG, MAX, MIN are disallowed in indexed views.

-- Making the view as INDEXED VIEW
ALTER VIEW dbo.GetSales
WITH SCHEMABINDING
AS
SELECT h.SalesOrderID
    , h.CustomerID
    , SUM(d.LineTotal) LineTotal
    , COUNT_BIG(*) CountBig -- this is required
FROM Sales.SalesOrderHeader h
    INNER JOIN Sales.SalesOrderDetail d
        ON h.SalesOrderID = d.SalesOrderID
GROUP BY h.SalesOrderID, h.CustomerID
GO
 
-- This makes the view as INDEXED VIEW
CREATE UNIQUE CLUSTERED INDEX IX_GetSales ON dbo.GetSales (SalesOrderID, CustomerID)
GO
 
-- Get data from view and see, note the number of data pages read
-- This shows that view is now physically available and data is loaded from it
SET STATISTICS IO ON
 
SELECT SalesOrderID, CustomerID, LineTotal FROM dbo.GetSales 
 
SET STATISTICS IO OFF

Note the pages accessed and the plan;

image

image

As you see, performance is significantly improved. However there is something needs to be considered when creating Indexed Views. That is the cost for maintaining it. Every changes make to underline tables require an update on the view. It is transparent but a cost is involved. Therefore implement this for tables that are not frequently updated.

Note that if you are using Standard Edition, you need to use NOEXPAND hint with SELECT statement like below. Otherwise SQL Server will not use the added index.

SELECT SalesOrderID, CustomerID, LineTotal FROM dbo.GetSales WITH (NOEXPAND )