Create alerts for your Synapse Dedicated SQL Pool
In this article I will discuss how to configure alerts for you Azure Synapse dedicated SQL pool and provide recommended alerts to get you started. Enabling alerts allows you to detect workload issues sooner allowing you to take action earlier to minimize end-user impact.Azure metrics Dashboard for Azure Synapse Analytics - Part 1
This article is part 1 of a 3 part monitoring series to meet the increasing needs of customers to proactively monitor the Synapse pool resource consumption, workload patterns and other key performance metrics. In this post, we will cover the dashboards that are available today for us on the Azure portal. These don’t require heavy customization and are very easy to set up. To create your dashboard, navigate to the Azure Portal Home page -> Synapse Pool resource blade -> Monitoring -> Metrics Although numerous metrics are available for building dashboards, this segment will cover the 4 most important ones for monitoring your DataWarehouse in this article. We will be using these metrics in the upcoming posts in the series as well. 1. Resource Utilization - CPU, DWU, IO percentages From the drop down shown above choose max CPU percentage, add max DWU percentage metric and max Data IO Percentage as shown below. Click on the pencil symbol and edit the name of the chart to your preference. Please note that DWU Percentage and CPU percentage overlap and you may see only one of them. The DWU percentage is usually either the CPU or IO percentage, whichever is higher. Now save the chart to a dashboard by clicking the 'pin to dashboard' option on the top righthand corner. You will be asked to choose between pinning it to an existing dashboard vs a new one as shown below. Once the chart is saved/pinned to the dashboard, follow the same process to create the remaining charts as well, as shown below. 2. Active and Queued queries - Concurrency details Following the same process as above, create another chart on the same dashboard blade by adding active queries and queued queries aggregating on 'Sum' 3. Workload Group Allocation - Resource classes and their percentage allocation details For this chart, select workload group allocation by system percent aggregating on 'Max' and split by 'Workload group'. Please note that there is a limit on the number of workload groups you can monitor. 4. Tempdb Utilization - tempdb usage across all the nodes Add the below mentioned metric to your chart aggregating on 'Max'. It is important to note that the chart below is the minimum, average or maximum value over a 5 minute window of the average tempdb utilization across all the nodes. In general, tempdb is located on each of the nodes, however, Azure metrics do not show the individual node level tempdb utilization as of yet. This has been brought to the attention of the development teams. Once all the 4 charts are pinned to the dashboard, resize the charts so that they all fit on one screen like below. Now that you have the important dashboards setup, you can build additional custom dashboards to get into more granular details about what queries/workloads are affecting your resources. This is not done by graphical user interface entirely and the second part of this post will provide you the step by step process for setting up the same.
Azure Synapse Variable DWU Level Usage - Cost Optimization using Reserved Instance Pricing
If you are running a production workload on Azure Synapse which you don’t plan to sunset in near time most likely Synapse Reserved Instance Pricing would make sense for you even if you are running at variable DWU Levels.Synapse Connectivity Series Part #2 - Inbound Synapse Private Endpoints
This blog article will feature Synapse Private Endpoint. The foundation of this article was based on a previous post - Azure SQL DB Private Link / Private Endpoint - Connectivity Troubleshooting) which I will go more in depth with Synapse specific features.Automatic pause all Synapse Pools and keeping your subscription costs under control
As Synapse engineer or Synapse Support Engineer you may need to start and test some Pools, and you want this to be the most cost efficient possible. Leaving some Synapse with a lot of DWU left turned on during the weekend because you forget to pause the DW after you shutdown your computers is not a good approach and we can quickly resolve this by using Powershell + Automation accounts.Save money and increase performance with intelligent cache for Apache Spark in Azure Synapse
Data professionals can now save money and increase the overall performance of repeat queries in their Apache Spark in Azure Synapse workloads using the new intelligent cache, now in public preview. This feature lowers the total cost of ownership by improving performance up to 65% on subsequent reads of files stored in the available cache for Parquet files and 50% for CSV files.Read Only Permissions in Synapse SQL
The Object level / Schema level permissions can be provided in Azure Synapse Analytics using SQL statements. There are be several ways that this can be achieved with different SQL syntax. EXEC sp_addrolemember 'db_datareader' , 'UserName' The Syntax “sp_addrolemember” Adds a database user, database role, Windows login, or Windows group to a database role in the current database. The Syntax “db_datareader” Members of the db_datareader fixed database role can read all data from all user tables. GRANT Database Principal Permissions also another way that can be achieved this task. This can be extended to table / schema level permissions. GRANT SELECT ON DATABASE::[SQL pool Name] TO [UserName] -------------------------------------------------------------------------------------------------------------- For Testing purposes, we have created 10 tables in the Data warehouse as follows – -------------------------------------------------------------------------------------------------------------- Example #1 : Providing “Select” permission on Data warehouse to specific user in Azure Synapse Analytics using “sp_addrolemember” Connect to Data warehouse using a user with ALTER USER permissions & use below syntax to provide select permissions to the SQL pool – EXEC sp_addrolemember 'db_datareader' , 'UserName' -------------------------------------------------------------------------------------------------------------- The role permission level can be check after providing the access to the specific user using “sp_addrolemember” as follows - SELECT DP1.name AS DatabaseRoleName, isnull (DP2.name, 'No members') AS DatabaseUserName FROM sys.database_role_members AS DRM RIGHT OUTER JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id LEFT OUTER JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id WHERE DP1.name ='db_datareader' -------------------------------------------------------------------------------------------------------------- The role permission level verifications as follows – -------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------- Example #2: Providing “Select” permission on Data warehouse to specific user in Azure Synapse Analytics using “GRANT DATABASE PRINCIPAL” permissions Connect to SQL pool using a user with ALTER USER permissions & use below syntax to provide select permissions to the SQL pool – GRANT SELECT ON DATABASE::[SQL pool Name] TO [UserName] -------------------------------------------------------------------------------------------------------------- The permission level can be check after providing the access to the specific user using “GRANT SELECT ON DATABASE” as follows - select princ.name, princ.type_desc, perm.permission_name, perm.state_desc, perm.class_desc, object_name(perm.major_id) from sys.database_principals princ left join sys.database_permissions perm on perm.grantee_principal_id = princ.principal_id where princ.name = '[USERNAME IN DW]' -------------------------------------------------------------------------------------------------------------- The GRANT DATABASE PRINCIPAL permission level verifications as follows – -------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------- NOTE: To remove the permissions or user roles, below syntax can be used To drop a user from user role – “sp_droprolemember” To drop a database principal permission – “REVOKE ON DATABASE PERMISSION” -------------------------------------------------------------------------------------------------------------- NOTE : When “Select” permissions are provided, the database objects will be visible to the user who has permissions and to hide the objects and provide access to specific objects, this needs to be extended to object level / schema level permissions Example #3: Note: There are multiple tables created with different schemas in the test environment - Providing “Select” permission to a SQL Schema on Data warehouse to specific user in Azure Synapse Analytics using “GRANT DATABASE PRINCIPAL” permissions Connect to Data warehouse using a user with ALTER USER permissions & use below syntax to provide select permissions to the SQL Schema & verify – GRANT SELECT ON SCHEMA::[SCHEMA NAME] TO [UserName] -------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------- Verifications using SQL server Object Explorer once the user is connected. The user will be able to see the objects under the schema (that permission provided) -------------------------------------------------------------------------------------------------------------- Example #4: Note: There are multiple tables created with different schemas in the test environment - Providing “Select” permission to a SQL Object on Data warehouse to specific user in Azure Synapse Analytics using “GRANT DATABASE PRINCIPAL” permissions Connect to Data warehouse using a user with ALTER USER permissions & use below syntax to provide select permissions to the SQL Object & verify – GRANT SELECT ON OBJECT::SCHEMANAME.TABLENAME TO [UserName] -------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------- Verifications using SQL server Object Explorer once the user is connected. The user will be able to see the objects under the schema (that permission provided)
