One of the challenges associated with running your workloads in Azure SQL Database is the limited level of management oversight (when compared with databases hosted on full-fledged SQL Server instances) over its resources. While you can easily scale it vertically by changing the associated service tier and performance level, you do not have, for example, the option of running SQL Server Profiler or Index Tuning Wizard, commonly used to evaluate, troubleshoot, and optimize database performance. Fortunately, there is an alternative approach that leverages the functionality incorporated into the recently introduced Azure SQL Database Advisor component of Azure SQL Database. In this article, we will present its basic characteristics.
The primary purpose of the Azure SQL Database Advisor is to provide and (optionally) implement recommendations geared towards remediating any potential database performance related issues. This tool was initially introduced as the Azure SQL Database Index Advisor; however, due to expansion of the scope of its original capabilities, it was subsequently rebranded to its current name. It operates by collecting telemetry data made available by the Azure SQL Database, analyzing it with assistance from the Azure Machine Learning, providing results of this analysis, and if permitted by the customer, implementing actions that take into account these results. While the collected data is available for both V11 and V12 Azure SQL database deployments, implementing recommendations is available exclusively for the latter.
Recommendations may include the following actions:
- creating indexes – offers the ability to improve performance by adding extra non-clustered indexes. Note that while clustered indexes are no longer required in Azure SQL Database V12 (that was a requirement in order to add data to tables in earlier versions), you should implement them first before creating non-clustered indexes.
- dropping indexes – detects and eliminates redundant indexes.
- parameterizing queries – applies forced parameterization in order to allow for reusing compiled (and cached) query execution plans. As a result, any literal value included in T-SQL statements executed against Azure SQL database is converted automatically into a parameter during query compilation. This recommendation is based on monitoring query execution plans and the corresponding queries.
- fixing schema issues – identifies such schema related inconsistencies as invalid object names.
Recommendations are based on the usage data collected over an extended period of time (at least one week) and require a relatively consistent usage pattern in order to be meaningful. In addition, their implementation is not immediate either, since it involves establishing the baseline against which the subsequent changes are evaluated, as well as collecting data following these changes that is necessary to determine whether the outcome was successful (i.e. the changes resulted in an improved performance).
Recommendations are displayed directly in the Azure portal in the Recommendations blade, accessible via the PERFORMANCE section of the Settings blade of the Azure SQL database, which performance characteristics you want to review. The blade lists current recommendations, including the action to carry out (such as CREATE INDEX), along with its more detailed description (such as the name of the table and indexed columns) and the expected impact (such as high, medium, or low). In addition, the blade displays Tuning history, which allows you to track previously implemented recommendations, including, for each, the action that was carried out, more detailed description, resulting status, and the completion time. Selecting individual entries in this list also reveals the Revert button in the toolbar, which you can use to roll back the corresponding changes. From within the same interface, you also have the ability to review recommendations you decided to forgo by clicking the View discarded button in the toolbar.
Another of the toolbar icons labeled Automate facilitates automating implementation of Create index and Drop index recommendations (the latter is in preview at the time of writing of this article). Alternatively, you have the option of manually running the recommended T-SQL script against the current database. You can view and copy the script to be executed by clicking on the View script icon in the Details blade displayed once you select the corresponding recommendation listed on the Recommendations blade.
It is important to point out that automatically implemented recommendations are evaluated by the SQL Database Advisor to determine whether they actually yield the expected performance benefits. If this is not the case, the change is automatically rolled back. However, considering that index creation could be potentially a resource intensive process, you might want to apply such change manually. If that is the approach you take, then you should also keep in mind that the evaluation of the performance benefits resulting from implementing a new index will not automatically be carried out by the Azure SQL Database Advisor. Effectively, in such cases, it is your responsibility to verify the resulting performance. One way to accomplish such objective with Azure SQL Database is to leverage the Queries functionality in the Azure portal.
You will find the Queries entry within the same PERFORMANCE section of the Settings blade of any Azure SQL database hosted on a V12 server instance. This entry provides access to the Query Performance Insight blade, where you will find a chart and the corresponding listing of top queries by the CPU consumption. By modifying the options available in its Settings blade, you can customize the displayed statistics. In particular, it is possible to modify the time period (to an arbitrary value within the range determined by the Query Store), to change the maximum number of queries to display (between 3 to 10), and alter the aggregation type (set to sum, average, or maximum). Each query included in the chart at the top of the page is also displayed in the table underneath, which lists such details as the query CPU utilization, its duration and total count of executions.
Clicking on any of the entries in the QUERY ID column will open the Query details blade, including the actual Transact-SQL script. In addition, you will find there the chart illustrating the CPU consumption for this particular query during last 24 hours (which you can customize to match your preferences) along with the table listing the CPU percentage values for each of the hourly intervals within that period.
If you prefer to script the process of reviewing and implementing Azure SQL Database Index Advisor, there is a set of Azure PowerShell cmdlets (including Get-AzureRmSqlDatabaseIndexRecommendations, Start-AzureRmSqlDatabaseExecuteIndexRecommendation, and Stop-AzureRmSqlDatabaseExecuteIndexRecommendation that provide this functionality. For more information regarding this topic, refer to the Azure SQL Database Cmdlets article of the MSDN library.
This concludes our introduction to Azure SQL Database Advisor. In our upcoming articles, we will take a more detailed look into managing performance of Azure SQL Database.