At Sequallity, the SQL Health Check Azure is relatively new, however we have been delivering SQL Server Health Checks for years. If you need an on-premises or virtual SQL Health Check then please click here.
There are two complimentary ways to analyse and health check an Azure SQL Solution. The Azure SQL Health Check can be run on an Azure SQL Database, an Azure SQL Managed Instance or a SQL Virtual Machine, the latter is almost exactly like an on-premises SQL Server health check.
Sometimes, clients are not comfortable with granting us access to their Microsoft Azure Portal but if we have been granted reader access only we can check the following important SQL Azure metrics built into the Azure portal including…
- DTU Usage
- CPU Usage
- IO Usage
- Database Advisor Recommendations
- Query Performance Insight
Principally, we are looking at how stressed the Azure SQL is and if it’s coping with your throughput. Where is the problem, the hardware or the SQL Queries? With this information, we are hovering at the machine level before we drill-down into the SQL Server queries.
If we are only allowed access to the Azure SQL via SSMS then we can still look at DTUs. There are some very specific DMVs that reveal Azure only counters.
Once the hardware has been investigated, it’s time to move onto the SQL instance itself.
Once in the Azure SQL instance, the order of the day is to check out these bad boys…
- Query Store
- Extended Events
Incidentally, we cover Query Store, DMVs and Extended Events in our Sequallity Training Courses.
What we’re looking for here are the SQL queries that have the most impact on the system. If the system is CPU-bound, we will be looking at the queries with the most CPU. If the Azure system is disk-bound, we would investigate the SQL queries with the most IO. We’d cover all of these: CPU, IO and Durations as well as other within this section.
Maintenance is critical to SQL Servers and there may be problems in this area around indexes and statistics, particularly as there can be no SQL Server Agent in order to run the automatic maintenance schedules.