Azure Synapse Analytics is an infinite analytics service that unites data integration, enterprise data warehousing, and big data analytics. Using either server-less or dedicated resources at scale, you have the freedom to query data your way. Here we will be comparing the performance at different scales.
Azure Synapse Performance Comparison
In order to compare the performance of different data volume tables with different scale, we have below set of tables/views:
[dbo].[Sales_Order_2Years]
[dbo].[Sales_Order_4Years]
[dbo].[Sales_Order_6Years]
[dbo].[Sales_Order_8Years]
[dbo].[Sales_Order_10Years]
Process of Scaling the Azure Dedicated SQL Pool
Default Azure Dedicated SQL pool is set on DW100c specification. To change the scaling on the higher specification, we can click on the scale button as shown in the below screenshot of the Azure Dedicated Pool window of Azure Portal.
In the above screenshot, we have changed the scale to DW200c, in the highlighted section it is showing all the pricing details. Once, change clicked on the save button on the same window to set the specification to DW200c.
SQL used in the process of performance analysis
1. Select count(*) from Sales_Order_2Years;
2. Select DISTINCT Part_Code from Sales_Order_2Years;
3. Select sum(cast(Month_Primary_Amount as float)) from Sales_Order_2Years;
4. Select sum(cast(Month_Primary_Amount as float)) ,Part_Code from Sales_Order_2Years group by Part_Code ;
5. Select sum(cast(Month_Primary_Amount as float)),Part_Code ,Organization_Name from Sales_Order_2Years group by Part_Code, Organization_Name;
6. Select sum(cast(Month_Primary_Amount as float)),Part_Code FROM Sales_Order_2Years group by Part_Code order by Part_Code DESC ;
7. Select sum(cast(Month_Primary_Amount as float)),sum(cast(Primary_Quantity as float )),Part_Code,Organization_Name From Sales_Order_2Years group by Part_Code,Organization_Name order by Part_Code desc;
Similarly, executed queries with other tables having different volumes.
Performance Comparison on Difference DP Specifications
Here following are the comparison among the different DP Specifications with different data sets of Volume in millions.
Conclusion: The above comparison clearly shows that when there is a large volume of data (in Millions) and the specification is higher on the dedicated Azure Pool, the performance increases. However, it doesn't get better on low data volume whether the specification is higher or lower.
Scale Up Dedicated SQL Pool using Pipeline
Note: To allow the Synapse workspace to call the REST API, we need to give the Synapse workspace access to the SQL Pool. In the Access control (IAM) of the SQL Pool, assign the contributor role to your Synapse Workspace.
Now, to scale up the SQL Pool using pipeline, we need to create a pipeline and set the parameters as below:
Above are the generic Parameters used within the Pipeline.
Subscription Id: Subscription Id of Synapse Workspace
ResourceGroupName: Name of the ResourceGroup of the used Synapse Workspace
Region: Location of the Synapse Workspace
ServerName: Synapse Workspace Name
DatabaseName: Name of the dedicated SQL Pool
Command: Resume
performance level: To level which we need to ScaleUp
Then in the pipeline, we need a Web Activity which will get the status of the SQL Pool whether it is Pause or Resume, an IF Condition which will check if the status is Resume or not, and again a Web Activity which will then Scale Up the SQL Pool.
Here is the setting of check status web activity:
Here is the URL value: @concat('https://management.azure.com/subscriptions/',pipeline().parameters.SubscriptionId,'/resourceGroups/',pipeline().parameters.ResourceGroupName,'/providers/Microsoft.Synapse/workspaces/',pipeline().parameters.ServerName,'/sqlPools/',pipeline().parameters.DatabaseName,'?api-version=2019-06-01-preview')
Method = GET
Resource =https://management.azure.com/
Now in If Condition, we will check if the status is not Resume and if it is not i.e. condition is true then in its Activity we will resume the status by assigning a Web Activity for it.
Here the expression in IF Condition is :
@and(equals(activity('Check Azure Synapse Analytics Status').output.properties.status,'Paused'), equals(pipeline().parameters.Command,'Resume'))
To add Web Activity we need to click on the pencil icon besides True as below and drag a Web Activity to that window from the left pane:
In Settings we need to give the below information:
URL Value: @concat('https://management.azure.com/subscriptions/',pipeline().parameters.SubscriptionId,'/resourceGroups/',pipeline().parameters.ResourceGroupName,'/providers/Microsoft.Synapse/workspaces/',pipeline().parameters.ServerName,'/sqlPools/',pipeline().parameters.DatabaseName,'/',pipeline().parameters.Command,'?api-version=2019-06-01-preview')
Method: Post
Body: {}
Resource =https://management.azure.com/
Once the status is set to Resume(or if it is confirmed that it is already resumed) the last Web Activity will Scale up the SQL Pool as per the given parameter.
URL Value: @concat('https://management.azure.com/subscriptions/',pipeline().parameters.SubscriptionId,'/resourceGroups/',pipeline().parameters.ResourceGroupName,'/providers/Microsoft.Synapse/workspaces/',pipeline().parameters.ServerName,'/sqlPools/',pipeline().parameters.DatabaseName,'/','?api-version=2019-06-01-preview')
Method: Patch
Header: Name = Content-Type Value= application/json
Body: {"sku": {"name": '@{pipeline().parameters.PerformanceLevel}'}}
Resource =https://management.azure.com/
You can schedule the pipeline before running ETLs in order to load the data and increase the performance by scaling up the SQL Pool and then in the same way you can create a pipeline to scale it down as before and schedule it after the load is done.
Azure Synapse offers an enterprise-grade analytical engine for datasets of any size. It offers a flexible pricing model to enable businesses to start small and scale up based on their need. If you would like to explore Azure Synapse for your business, Accelerant offers a free demo environment customized to your data needs. Reach us at info@accelerant.com for more information.
Author: Ritu Negi
Technical Lead, Accelerant