Making sure that your SQL database is running like a well oiled machine can mean the difference between user frustration and improved productivity. For that reason alone it makes sense to optimize performance as much as possible, even if you are now relying on Microsoft to provide the infrastructure on which the database is hosted.
Here are few pieces of advice that SQL administrators can use to turbo-charge their implementation of Azure for this purpose.
What Features Are Missing?It is worth noting before you get started that Azure SQL is a little different to what you may be used to in terms of the adjustments you can make and the optimizations that are possible.
Since you are effectively working with a virtualized server instance, rather than a specific, singular set of server hardware to which you have sole access, you cannot alter things like the amount of memory which is available to handle operations.
With that out of the way, it is time to start examining the aspects over which you do have some level of control within the Azure ecosystem.
Using the Performance Dashboard
Your first port of call when aiming to enhance the way your database operates should be the Performance Dashboard that is now part of the official Azure portal.
This can not only let you get a quick overview of how things stand on a second-to-second basis, but also allow you to pinpoint specific problems by highlighting which queries have been monopolizing the largest proportion of your server resources in the past day.
The dashboard also lets you enable or disable auto-tuning capabilities that are baked into Azure SQL as standard, as well as keep tables on any tuning activities that you have scheduled in.
More about SQL server performance tuning read here - https://www.sentryone.com/sql-server/sql-server-performance-tuning
Checking Recommendations
An important part of the Performance Dashboard is the Database Advisor, which is another useful feature of Azure SQL that automatically generates recommendations for how you can tune performance and make the server more efficient and effective.
For example, it may recommend that you create an index if it feels one is needed, or indeed delete an index if a duplicate is detected. Each recommendation is ranked according to the amount of impact that it is likely to have on performance, allowing you to prioritize your decision-making accordingly.
Of course you do not need to rely solely on these self-generated pointers, especially if you feel like you have enough experience and intuition to make tuning choices based on the available data, but the dashboard and its sub-sections can certainly help do away with a lot of head-scratching and guesswork if you are a relative newcomer to Azure SQL.
Query Optimization
Fine tuning your database will require some tinkering with queries themselves, and once again the native dashboard makes this possible.
As mentioned earlier, you can see which queries are hogging the lion’s share of your CPU’s cycles. Even if these queries are not especially sub-optimal in their own right, the fact that they are so commonly used means that even small enhancements can deliver big performance gains on a cumulative basis.
You can see how long queries took to be executed, as well as how many times they were executed in a given time period.
Since downtime can be costly and even minor inefficiencies in database operations are an unwanted expense for any business, taking the time to make the most out of the tools that Azure SQL bestows upon you to tune performance makes sense. SentryOne can help with this, and if you're in healthcare cloud partners like Cloudticity can also help you optimize your Azure architecture for performance.
About the Author:
Kevin Kline serves as Principal Program Manager at SentryOne. He is a founder and former president of PASS and the author of popular IT books like SQL in a Nutshell. Kevin is a renowned database expert, software industry veteran, Microsoft SQL Server MVP, and long-time blogger at SentryOne. As a noted leader in the SQL Server community, Kevin blogs about Microsoft Data Platform features and best practices, SQL Server trends, and professional development for data professionals.
