Post navigation

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?

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

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.

The minimum requirement is 100 eDTU pool.

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.