question

Deepak2610-6711 avatar image
0 Votes"
Deepak2610-6711 asked ·

SQL Server ColumnStore indexes

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.

20475-image.png


azure-sql-databaseazure-database-mariadb
image.png (7.3 KiB)
· 1
10 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Deepak2610-6711, Thank you for the detailed inputs. Could you please provide below information to understand the issue better?

  1. Is the difference in execution time is related to just the tables that have more number of columns (like 100 columns your mentioned above), or even with tables with less number of columns?

  2. At an average, how much is the execution time difference for both the DBs? Could you provide few stats with queries if possible?

  3. Which tools were you using to execute the queries?





0 Votes 0 ·

1 Answer

Mike-Ubezzi-MSFT avatar image
0 Votes"
Mike-Ubezzi-MSFT answered ·

@Deepak2610-6711 - 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.

10 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.