SQL Server ColumnStore indexes

Question

Recently I did a POC with column store databases. I used Maria DB ColumnStore database storage engine and SQL server's column indexed on Azure. I had around 38 GB's of sample data to query upon. I wrote close to 45 test cases (with queries). Some of the tables had more than 100 columns.

I was surprised to see that Maria Db's execution time for queries was far better than SQL Server columnstore index. Is there any reason behind it? I was expecting better results from SQL.

Answer 1

@Deepak2610 - These comparisons are generally difficult to track down without a more specific baseline of deployment details for the specific service the test is being run. Additionally, although Azure SQL Database and Azure SQL Database for MariaDB are both deployed via a cluster/node architecture, your best best for an equal comparison is to deploy SQL Server (Linux image) to an Azure VM that is sized accordingly. Please see: Get started with SQL Server VMs.

If you would like to dig into the performance issues you are experiencing with the Azure SQL Database instance, please see: Troubleshoot Azure SQL Database and Azure SQL Managed Instance performance issues with Intelligent Insights where there is a recommended troubleshooting flow you can reference to efficiently investigate and identify anomalies or patterns that may give better insights to your workload behavior.

There are performance recommendations for Azure Database for MariaDB as well.

If you have specific questions, please comment and I hope this information addresses most, if not all of your inquiry.