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
CREATE NONCLUSTERED COLUMNSTORE INDEX account_NCCI ON t_account (accountkey, accountdescription, unitsold)
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
What happens if we go to a service tier lower than S3? For instance Basic?
select * from sys.database_service_objectives
SELECT [SalesOrderID] ,[RevisionNumber] ,[OrderDate] ,[DueDate] ,[ShipDate] FROM [SalesLT].[SalesOrderHeader] GO
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.
Great article, very interesting.
LikeLike
Thanks for the details. Your blog helped me today.
LikeLike