Database Performance Tips from Azure That You Are Probably Overlooking

Do you know you can leverage Azure portal to get tips on how to maximize the performance of your Azure Database?

Credit — https://www.facultyfocus.com/articles/online-education/online-course-delivery-and-instruction/transitioning-to-distance-learning-three-tips-for-teachers/

At organizations that contain more than one data engineer or multiple data engineering teams, SQL tables get created at a breakneck speed. This is understandable and often one of the reasons why it gets difficult to ensure that the tables were designed correctly.

What I mean by that?

For example, in the eye of multiple project deadlines, how can we ensure whether the distribution methods (replicate, round robin or hash) chosen for a plethora of tables were correct or not and can a better method provide a higher performance during querying?

This article brings to focus tips that Azure provides to you within its portal to help alleviate the performance bottlenecks.

👉 Tip 1: Check Recommendations on Your Data Warehouse’s Azure Page

In Azure portal, simply type the name of your Azure Data warehouse in the search bar to arrive at your Data warehouse section.

As I can see from the above screenshot, I have some recommendations from Azure about my SQL tables.

When I click any of the recommendations, the page will refresh to show me additional details about its recommendations.

Click the performance option (as highlighted) to get all the performance related suggestions. Here I have 3 that are automatically arranged in the order of their impact from High to Low.

1. Create statistics on table columns
2. Convert tables to replicated tables
3. Table has < 60 million rows but is in a Clustered Columnstore Index design

As I click each of these recommendations, Azure provides me with the table name that recommendation applies to. For ex: I clicked the convert tables to replicated tables and Azure showed 2 tables that will benefit from a replicated distribution.

The Data warehouse, subscription and table names information is omitted.

I can also read the Details provided against the tables to suggest why a replicated distribution would work for them.

👉 Tip 2: Use Azure Advisor To Get Recommendations Across The Board

The second tip I have for you is to use Azure Advisor. Log into your Azure portal and search for Advisor.

When you arrive at this section, you should be able to see recommendations grouped for different categories — Cost, Security, Reliability, Operational Excellence and Performance.

When I click Performance option, I can now see that the recommendations apply not just on one of my dedicated SQL pool but on other data warehouses too.

At this step, you might ask-

Well what’s the difference between this page and what we saw in the first tip ?

The difference is that in Tip 1, we exclusively navigated to the section of our Dedicated SQL pool so all the recommendations we saw were for that Dedicated SQL Pool tables only.

Here, we see performance recommendations being provided for multiple data warehouses (assuming you have the right RBAC role for those) and thus we can assess the design strategy of our tables that are present in different data warehouses.

👉 Tip 3: Automate (if needed) Azure Performance Recommendations

If you do not need the hassle of checking the recommendations every now and then but prefer an automated way where Azure automatically applies its recommendations to the tables, choose Automate option.

Where To Find This Option?

Navigate to the Overview section of your chosen Dedicated SQL Pool. Click the server name from this section.

You should arrive at the SQL server section in Azure. From the left pane, click Automatic tuning setting.

Here, you can enable the automatic tuning options.

⚠️ Please note that since we are on the server page, any enabled automatic tuning option will cascade down to all the databases / data warehouses hosted on that server.

So, what do those tuning options even mean?

1. Force Plan — When we turn on automatic tuning, Azure will continuously monitor queries executed on a database and automatically improve their performance. In this option, Azure will check if the execution plan used by a query is slower than the last plan. If so, Azure will force the last known performant execution plan on the query to enable faster execution.
2. Create Index — Since Azure monitors query execution, it will decide if the queries can be improved using an index on the table.
3. Drop Index — Conversely to our #2, Azure checks if the tables have any unused indexes which will then be dropped. Please note that if there are indexes on Primary key or with Unique constraints, those indexes will never be dropped.

So, as we can see, Azure continuously monitors the queries and the tables to provide its list of recommendations for performance improvement. I hope this article was able to provide you with some insights on how to manage your Database’s performance.