New data compression - Columnstore Archive

All who have used columnstore index must know that columnstore index also compress data.
Many cases after some time this data become historical and use of this data reduces over time.
In this case we can further compress data sung columnsore_archive compression option. Retrieving data takes time after this compression but its acceptable as it reduces storage and its usage frequency is low compare to current data

Lets see with example

CREATE TABLE test

  (

     id   INT,

     data VARCHAR(max)

   

  )

DECLARE @i INT

SET @i = 1

WHILE @i <= 100000

  BEGIN

      INSERT INTO test

      VALUES      (@i,

                   'The value of i is '

                   + CONVERT(VARCHAR(10), @i))

      SET @I = @i + 1

  END

SP_SPACEUSED test

Normal Table

Now we will create clustered columnstore index here.

It will compress data

 

CREATE CLUSTERED COLUMNSTORE INDEX c1 ON test

Table with clustered columnstore index

Now we will compress more using columnstore_archive option 

This will compress data even more. 

ALTER INDEX c1 ON test REBUILD WITH (data_compression = columnstore_archive)

Table with columnstore_archive compression option

 

 



Here we can see size reduces from 8848 KB to 2704 KB and then 1232 KB.

Inline indexes

SQL 2014 has many silent features one of them is inline indexes.
Whats inline indexes?
Inline indexes are which can be created while creating table.
We can specify index in Create Table statement itself.

Here is an example

CREATE TABLE test_table

  (

     id   INT,

     data VARCHAR(max),

       INDEX idx1 (id)

   )

Limitations
1.We can create filtered index
2.We can not create columnstore index.

Good news is that both this issues resolved in SQL Server 2014.

Optimized select into in SQL Server 2014

Lets have quick glance of this feature.
As we all know select .... into clause and how to use it.
Now in SQL 2014 its more optimized and will operate in parallel also for better performance.


Lets see with example

I have one table test_table with 200 Million records in SQL Server 2014 and 2012 both.

We will run Select .... into query and check execution plan



SQL Server 2012

SELECT *

INTO   new_table

FROM   xyz

SQL Server 2014

SELECT *

INTO   new_table

FROM   xyz

As we can see in execution plan optimizer is using parallelism operator to dump data.

Managed lock priority -SQL 2014

Managed lock priority 

Its available in SQL 2014 and used for online operation s
Mainly used for switching partition and rebuilding indexes online.

Till now its issue to switch partition while tables are in use.
Its because that requires schema modification lock SCH-M lock.
As its not possible to get this lock until any operation is running on table and hence switching partition is a nightmare for any developers.
This request for SCH-M lock was creating a chain of locks and hence affecting performance of OLTP operations.


Now in 2014 we have option that we can define priority for switching or online reindexing with other operations.
Lets see how to use this feature

If we see at syntax of alter table or alter index we will find this option


WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ], ABORT_AFTER_WAIT =
{ NONE | SELF | BLOCKERS } )

MAX_DURATION is time for which we want to wait before completing the operation

ABORT_AFTER_WAIT :- What action we want to take after waiting time completes
NONE :- Will take no action and continue waiting
SELF :- It will kill self session
BLOCKERS:- It will kill session which are creating blocking and will do switching or
 reindexing.

But this killing session has one drawback it will lead transaction to rollback and if we dont know which operations are in rollback it may impact OLTP operations.
So we need to be careful while doing this and should be done when there is low activity or switching/reindexing has higher priority compare to other tasks.

Lets create a table and check this behavior

CREATE PARTITION FUNCTION part_test_func( datetime)

AS RANGE LEFT FOR VALUES( '2014-09-10' , '2014-09-20' , '2014-09-30');

CREATE PARTITION SCHEME part_test_scheme

AS PARTITION part_test_func TO( [primary],[primary],[primary],[primary]);

CREATE TABLE part_test( id int IDENTITY( 1 , 1) ,

                        data varchar( 100) ,

                        date datetime)

ON part_test_scheme( date);

CREATE TABLE part_test1( id int IDENTITY( 1 , 1) ,

                        data varchar( 100) ,

                        date datetime)

ON part_test_scheme( date);

INSERT INTO  part_test

VALUES( 'a' ,

        '2014-09-05'

      );

INSERT INTO part_test

VALUES( 'a' ,

        '2014-09-15'

      );

INSERT INTO part_test

VALUES( 'a' ,

        '2014-09-25'

      );

INSERT INTO part_test

VALUES( 'a' ,

        '2014-10-05'

      );

Now in other window I will run this query 

BEGIN TRAN

SELECT *

FROM   part_test WITH(updlock)

Now we will go for partition switching in first window 

We will run this query

ALTER TABLE part_test switch PARTITION 1 TO part_test1 PARTITION 1

WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES,

ABORT_AFTER_WAIT = BLOCKERS))

It will wait for 1 minute and then kill the blocking and statement and complete 

switching.

But we also need to know which session were killed. 

We can find this details in errorlog

Online reindexing of partition.

Till now SQL 2012 its not possible to reindex a partition online.
We had two option

1. Either reindex entire table online
2. Reindex a single partition offline.

Books online for SQL Server 2014

Books online for SQL 2014 is now available
Lets see how to install it.

First go to help -> Manage Help Setting

How to manage identity column in In-Memory tables

As there are certain limitation which I also mentioned in my previous articles.
One of them is identity is not supported.
So when there is identity column is used in table we have to change code and use SEQUENCE instead.

Lets see an example

Transact SQL options not supported on In-Memory Tables

In-Memory tables are great enhancement but also there is certain limitation also while using it.
Below is Transact SQL Constructs which are not supported in In-Memoery Tables compare to disk based tables.

Delayed Durability -SQL Server 2014

One more silent but very useful feature of SQL 2014 is delayed durability.
In memory tables has also durability option but we are talking about normal tables and will see how to use it.

First we have to set database option delayed_durability.
It has 3 options DISABLED/FORCED/ALLOWED


ALTER DATABASE TEST
SET DELAYED_DURABILITY  =DISABLED/FORCED/ALLOWED


In general this option is disabled

DISABLED :- Delayed durability is disabled and all transactions are fully durable.
ALLOWED:- Each transactions durability is determined at transaction level
by setting option Delayed_Durability = ON/OFF
FORCED:-Every transaction is delayed durable.

To enable from SSMS

Lets see the example to understand it better

CREATE TABLE T

(ID INT,

DATA VARCHAR(100))

 

Now we will insert data into table
First we will insert data by setting disabled.

DECLARE @DATE1 DATETIME

DECLARE @DATE2 DATETIME

ALTER DATABASE TEST

SET DELAYED_DURABILITY  =DISABLED

TRUNCATE TABLE T

   SELECT * INTO #before_disabled FROM sys.dm_io_virtual_file_stats(DB_ID('Test'), NULL);

SELECT @DATE1 =  GETDATE()

SET NOCOUNT ON

DECLARE @I INT =1

WHILE @I <= 100000

       BEGIN

              INSERT INTO T VALUES (@I, 'DATA'+ CONVERT(VARCHAR(10),@I))

              SET @I = @I+1

       END

SET NOCOUNT OFF

SELECT * INTO #after_disabled FROM sys.dm_io_virtual_file_stats(DB_ID('Test'), NULL);

SELECT @DATE2 =  GETDATE()

SELECT DATEDIFF (SS, @DATE1,@DATE2)

SELECT 

                B.DATABASE_ID, B.[FILE_ID],

                           CASE WHEN A.FILE_ID = 1 THEN 'LOG' ELSE 'DATA' END AS FILE_TYPE,

                A.NUM_OF_READS - B.NUM_OF_READS AS NUM_OF_READS,

                A.NUM_OF_BYTES_READ - B.NUM_OF_BYTES_READ AS NUM_OF_BYTES_READ,

                A.NUM_OF_WRITES - B.NUM_OF_WRITES AS NUM_OF_WRITES,

                (A.NUM_OF_BYTES_WRITTEN - B.NUM_OF_BYTES_WRITTEN)/(1024*1024) AS NUM_OF_BYTES_WRITTEN_MB

            FROM

               #BEFORE_DISABLED AS B

               INNER JOIN #AFTER_DISABLED A ON B.DATABASE_ID = A.DATABASE_ID AND B.[FILE_ID] = A.[FILE_ID]

GO

DECLARE @DATE1 DATETIME

DECLARE @DATE2 DATETIME

SELECT @DATE1 =  GETDATE()

ALTER DATABASE TEST

SET DELAYED_DURABILITY  =FORCED

TRUNCATE TABLE T

SELECT * INTO #before_enabled FROM sys.dm_io_virtual_file_stats(DB_ID('Test'), NULL);

SET NOCOUNT ON

DECLARE @I INT =1

WHILE @I <= 100000

       BEGIN

              INSERT INTO T VALUES (@I, 'DATA'+ CONVERT(VARCHAR(10),@I))

              SET @I = @I+1

       END

SET NOCOUNT OFF

SELECT * INTO #after_enabled FROM sys.dm_io_virtual_file_stats(DB_ID('Test'), NULL);

SELECT @DATE2 =  GETDATE()

SELECT DATEDIFF (SS, @DATE1,@DATE2)

SELECT 

                B.DATABASE_ID, B.[FILE_ID],

                           CASE WHEN A.FILE_ID = 1 THEN 'LOG' ELSE 'DATA' END AS FILE_TYPE,

                A.NUM_OF_READS - B.NUM_OF_READS AS NUM_OF_READS,

                A.NUM_OF_BYTES_READ - B.NUM_OF_BYTES_READ AS NUM_OF_BYTES_READ,

                A.NUM_OF_WRITES - B.NUM_OF_WRITES AS NUM_OF_WRITES,

                (A.NUM_OF_BYTES_WRITTEN - B.NUM_OF_BYTES_WRITTEN)/(1024*1024) AS NUM_OF_BYTES_WRITTEN_MB

            FROM

               #BEFORE_ENABLED AS B

               INNER JOIN #AFTER_ENABLED A ON B.DATABASE_ID = A.DATABASE_ID AND B.[FILE_ID] = A.[FILE_ID]

Here we can see when we are using delayed_durability = on the insert query completes within 4 seconds compare to 22 seconds with delayed_druability = off.

First query takes 100008 writes and 59 MB data was written

After enabling delayed durability it took only 565 writes and 32 MB data was written.

AS we can see the performance is amazing and it can be idea to solve many io related issue where log file and locking is  most contending,

Also lets see how much IO was required for each transaction .




Its because its less likely to contend for log IO. Data committed from buffer in large amount reduces contention much more than writing
individual entry for log.
Here  risk is that if system crashes due to any reason before data is written from buffer to disk, we will loss data.

Important points  to remember.

1. Transactions are asynchronous.
2. Transactions becomes durable when log entries flushed to Disk.
3. It writes transaction log in batches instead individual writes for each operation .
4. Reducing waits in the system
5. Reduces I/O requirements
6. Log is written to disk when buffer fills or durable transaction commits in same DB or running system proc SP_FLUSH_LOG.