Azure SQL Database and Columnstore Indexes

I have come to understand the importance of using columnstore indexes when my queries are aggregating and scanning across many millions of rows.

Since I mostly work with Azure SQL Database I was pretty exciting to hear about columnstore support in the standard tier, more specifically S3 and above. Why S3? Well it is all about the memory required for it to perform well and using S3 and above works best.

Single Database

So what happens if you try and create a columnstore index on a non – S3 database?

You will get – Msg 40536, Level 16, State 32, Line 21 ‘COLUMNSTORE’ is not supported in this service tier of the database. See Books Online for more details on feature support in different service tiers of Windows Azure SQL Database.

Ok, what if we try and create one on something higher than S3? Maybe P1?

select * from sys.database_service_objectives

Capturep1

CREATE NONCLUSTERED COLUMNSTORE INDEX account_NCCI
ON t_account (accountkey, accountdescription, unitsold)

NCI_cs

Works just as expected.

Checking The Execution Plan

So now we have the right service tier and it is utilising a columnstore nonclustered index (basic queries for demo purposes).

SELECT [SalesOrderID]
,[RevisionNumber]
,[OrderDate]
,[DueDate]
,[ShipDate]
FROM [SalesLT].[SalesOrderHeader]

GO

CSscan

What happens if we go to a service tier lower than S3? For instance Basic?

select * from sys.database_service_objectives

BasicCS

SELECT [SalesOrderID]
      ,[RevisionNumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
 FROM [SalesLT].[SalesOrderHeader]
GO

nocol

It will not be chosen when the database executes the query. It becomes dormant.

Elastic Pools

What about if you are using elastic pools? I have a database called AWSDB which is part of an elastic pool. As you can see it is a standard elastic pool.

select * from sys.database_service_objectives

standPool

I run the code to create the nonclustered columnstore index but I get: Msg 40536, Level 16, State 32, Line 21 ‘COLUMNSTORE’ is not supported in this service tier of the database. See Books Online for more details on feature support in different service tiers of Windows Azure SQL Database.

Digging into the portal I notice that this is a 50 eDTU pool.

50edtus

The minimum requirement is 100 eDTU pool.

100edtus

so I scale up and confirm that the index has been created.

So the key learning(s) from this post?

  • For single Azure SQL Databases you need a minimum of S3 to create columnstore indexes.
  • Your query will no longer use the columnstore index if you scale lower than S3.
  • For elastic pools, your pool must be using 100 eDTUs or higher to create columnstore indexes.
Advertisements

One thought on “Azure SQL Database and Columnstore Indexes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s