We want to deploy our data warehouse that uses SQL Server 2022 Clustered ColumnStore Indexes on a VM with Sql Server 2022 Standard and we don't know which Azure instance type (series) is better for our requirements where we have : Jobs that run every 1 minute to add data. Reports running on Clustered Column Index tables for 15 users for 8-12 hours per day with computations. Our annual size in database is about 20GB (clustered column index tables) Since this is our first project with this technology it is unknown for us which instance type (series) to use Any suggestion would be highly appreciated. Thanks in advance

@pantonis , Thanks for contacting Microsoft Q&A platform. Based on your specifications, I recommend taking the following factors into account when choosing the Azure instance type for your deployment. 1. Virtual Machine Series: · For database workloads, consider using the Azure Virtual Machines (VM) D-series or E-series. These series are optimized for applications that demand higher compute power and memory. · D-series VMs provide a balance of CPU, memory, and disk resources and are suitable for a variety of workloads. · E-series VMs are memory-optimized and can be a good choice if your workload requires a significant amount of RAM. 2. Virtual Machine Size: · Depending on your specific needs and budget constraints, you can start with a medium-sized VM and scale up or down based on performance monitoring. · For the D-series, you might consider starting with a VM like Standard_D4s_v3 or Standard_D8s_v3, and for the E-series, consider Standard_E4s_v3 or Standard_E8s_v3. Adjust the size based on performance requirements. 3. Storage Configuration: · Use Premium SSDs for better performance, as they provide low-latency and high-throughput storage. · Consider setting up your SQL Server data and log files on separate disks to optimize I/O. Hope this helps.

Hi I think its a good idea to start with a VM size that you believe meets your requirements. For example, an "Esv3" or "Dsv3" series VM with an appropriate number of CPU cores and memory. Monitor the VM's performance metrics during your load tests and report computations. If needed, adjust the VM size or consider horizontal scaling with multiple VM instances. Also go through these documentations for more info https://learn.microsoft.com/en-us/azure/virtual-machine-scale-sets/virtual-machine-scale-sets-autoscale-overview Azure Virtual Machines documentation https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/ways-to-connect-to-sql?view=azuresql If this helps kindly accept the answer thanks much.

You can scale up a VM on anytime, so start with an approbiate one an see, how it's going. Note: The SQL Server Standard Edition supports ColumnStore, but in a very limited way, see Editions and supported features of SQL Server 2022

SQL Server 2022 Standard Edition on Azure VM

pantonis 30

We want to deploy our data warehouse that uses SQL Server 2022 Clustered ColumnStore Indexes on a VM with Sql Server 2022 Standard and we don't know which Azure instance type (series) is better for our requirements where we have :

Jobs that run every 1 minute to add data.
Reports running on Clustered Column Index tables for 15 users for 8-12 hours per day with computations.

Our annual size in database is about 20GB (clustered column index tables)

Since this is our first project with this technology it is unknown for us which instance type (series) to use

Any suggestion would be highly appreciated.

Thanks in advance

v-vvellanki-MSFT 4,920 Reputation points Microsoft Vendor

2023-12-14T05:18:29.1866667+00:00

Hi @pantonis ,

Just checking in to see if the below answer provided by Azar helped.
If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
v-vvellanki-MSFT 4,920 Reputation points Microsoft Vendor

2023-12-15T04:18:35.2333333+00:00

Hi @pantonis,

Following up to see if the provided answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Accepted answer

v-vvellanki-MSFT 4,920 Reputation points Microsoft Vendor

2023-12-19T04:21:45.5133333+00:00

@pantonis ,
Thanks for contacting Microsoft Q&A platform.
Based on your specifications, I recommend taking the following factors into account when choosing the Azure instance type for your deployment.

1.      Virtual Machine Series:

·       For database workloads, consider using the Azure Virtual Machines (VM) D-series or E-series. These series are optimized for applications that demand higher compute power and memory.

·       D-series VMs provide a balance of CPU, memory, and disk resources and are suitable for a variety of workloads.

·       E-series VMs are memory-optimized and can be a good choice if your workload requires a significant amount of RAM.

2.      Virtual Machine Size:

·       Depending on your specific needs and budget constraints, you can start with a medium-sized VM and scale up or down based on performance monitoring.

·       For the D-series, you might consider starting with a VM like Standard_D4s_v3 or Standard_D8s_v3, and for the E-series, consider Standard_E4s_v3 or Standard_E8s_v3. Adjust the size based on performance requirements.

3.      Storage Configuration:

·       Use Premium SSDs for better performance, as they provide low-latency and high-throughput storage.

·       Consider setting up your SQL Server data and log files on separate disks to optimize I/O.

Hope this helps.
Please sign in to rate this answer.

1 person found this answer helpful.
pantonis 30 Reputation points

2023-12-19T06:58:36.3933333+00:00

One last question the VMs that you suggested don't include Temporary storage and from what I read temporary storage is used by SQL Server tempdb. If they don't have temp storage does this mean we wont be able to use tempdb?

v-vvellanki-MSFT 4,920 Reputation points Microsoft Vendor

2023-12-19T07:11:18.72+00:00

Temporary storage in Azure Virtual Machines typically refers to the local temporary drive (D: drive on Windows VMs). The size and characteristics of this temporary drive can vary based on the VM series and size. Some VM sizes come with a local temporary drive, while others do not.

For SQL Server deployments, it's a common practice to use Azure managed disks for the database files, including tempdb, rather than relying on the local temporary drive. Managed disks are durable, high-performance, and provide better reliability compared to the local temporary drive, which can be volatile and is not intended for long-term storage.

Hope this helps you.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

2 additional answers

Azar 25,375 Reputation points MVP

2023-12-13T19:01:17.5966667+00:00

Hi

I think its a good idea to start with a VM size that you believe meets your requirements. For example, an "Esv3" or "Dsv3" series VM with an appropriate number of CPU cores and memory.

Monitor the VM's performance metrics during your load tests and report computations.

If needed, adjust the VM size or consider horizontal scaling with multiple VM instances.

Also go through these documentations for more info

https://learn.microsoft.com/en-us/azure/virtual-machine-scale-sets/virtual-machine-scale-sets-autoscale-overview

Azure Virtual Machines documentation

https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/ways-to-connect-to-sql?view=azuresql

If this helps kindly accept the answer thanks much.
Please sign in to rate this answer.
pantonis 30 Reputation points

2023-12-13T19:17:17.0133333+00:00

Thanks for your reply. I understand that I have to find the optimal number of cpus and memory. But to better understand your answer "Esv3" and "Dsv3" are better suited for SQL Server 2022 with clustered columnstore index?

Azar 25,375 Reputation points MVP

2023-12-13T19:24:32.29+00:00

I would say yes pantonis

pantonis 30 Reputation points

2023-12-14T05:55:10.72+00:00

What about F8 VM (8CPUs 16GB RAM)?

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Erland Sommarskog 115.3K Reputation points MVP

2023-12-14T19:54:13.56+00:00

But to better understand your answer "Esv3" and "Dsv3" are better suited for SQL Server 2022 with clustered columnstore index?

I don't want to make any suggestions about VM sizes and models, because the number of options available makes my head spin. However, I like to point out that columnstore as such does not call for a specific model of the VM. The answer would be the same for your workload, if the data would be stored in a traditional row-based table.

Azar 25,375 Reputation points MVP

2023-12-14T21:53:22.6633333+00:00

Hehe yes,I agree with you on this @Erland Sommarskog

pantonis guess this is helpful to you, and don't forget to accept the answer thanks much

pantonis 30 Reputation points

2023-12-18T20:03:25.2166667+00:00

Thanks for your reply. I wasn't talking for number of cpus and memory but rather to the quality of cpu. From what I understand and please correct me if Im wrong, Clustered Columnstore indexes use lots of CPU and I/O In order to achieve maximum performance, it measn you need a good cpu and disk. That is why I was asking which series is optimal. Thanks again

pantonis 30 Reputation points

2023-12-19T06:11:27.9766667+00:00

Thank you very much. That was very very helpful

v-vvellanki-MSFT 4,920 Reputation points Microsoft Vendor

2023-12-19T06:17:00.6866667+00:00

@pantonis ,
Thanks for the feedback provided. I have converted the comment to an answer. Can you please accept the answer. So that will visible to the users.

pantonis 30 Reputation points

2023-12-19T06:35:28.2966667+00:00

I have marked it as answer. One last question the VMs that you suggested don't include Temporary storage and from what I read temporary storage is used by SQL Server tempdb. If they don't have temp storage does this mean we wont be able to use tempdb?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Olaf Helper 45,541 Reputation points

2023-12-19T06:53:55.65+00:00

You can scale up a VM on anytime, so start with an approbiate one an see, how it's going.

Note: The SQL Server Standard Edition supports ColumnStore, but in a very limited way, see

Editions and supported features of SQL Server 2022
Please sign in to rate this answer.
pantonis 30 Reputation points

2023-12-19T07:05:42.6166667+00:00

Wow. I just noticed this. Thanks for the comment. It seems Columnstore on Standard wont work at all the way it works in Enterprise. Am I right?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

SQL Server 2022 Standard Edition on Azure VM

2 additional answers

Your answer