This sample provides a demo to enable you to explore the Azure SQL Automatic Tuning and its capabilities. You can also use it to demo this service to your teams and organization.
Automatic tuning is a fully managed, smart performance service that leverages inherent intelligence to consistently observe and enhance the performance of queries running on your databases. It achieves this by adapting to fluctuating workloads and implementing tuning suggestions in real time. Benefiting from AI, automatic tuning continuously enhances its actions by learning from a collective pool of knowledge gathered from all databases hosted on Azure. The performance of a database improves progressively the more it operates under automatic tuning.
The core principles of Azure SQL automatic tuning are similar to those found in the SQL Server automatic tuning feature within the database engine. For more detailed information on the underlying intelligent mechanisms, please take a look at SQL Server automatic tuning.
If you want to learn more about Azure SQL Automatic Tuning, visit this link.
- Automated performance tuning of databases: You can enable the automatic tuning settings to apply the tuning recommendations automatically, so it does not require any manual action.
- Automated verification of performance gains: Once the tuning recommendations are applied, the service monitors the performance of your database to ensure there were improvements in its performance.
- Automated rollback and self-correction: If no performance improvement is verified, the service reverts the change to the original state to avoid impacting your database's performance.
- Tuning history: All actions performed by the service or anyone interacting with the service are logged into the tuning history so anytime you can verify the actions performed against your database.
- Tuning action Transact-SQL (T-SQL) scripts for manual deployments: All performance recommendations allow you to download the T-SQL script and apply them manually in your preferred way.
- Scale-out capability on hundreds of thousands of databases: By leveraging the automatic tuning service, you can easily scale the number of databases you can monitor and enhance the performance.
The idea of this demo is to create an Azure SQL Database using the sample database AdventureWorksLT. Once you have the database, you can install the Azure Data Studio and connect to your created database. Once you can connect to your database, you can run the provided queries in this repository and analyze the query execution plan to assess if the queries that you are going to use and run on your database require optimizations and database tuning. Then, you must decide which way to run your queries: on Azure using Azure Functions or Automation or locally using SQLQueryStress or VS Code. I
To run and test this demo, you must configure and meet the following prerequisites.
- Azure account: If you're new to Azure, get an Azure account for free, and you'll get some free Azure credits to use with this demo.
- Azure account permissions: You must ensure you have enough permissions at the subscription level to deploy the resources in this demo and modify them accordingly.
- Your Azure account needs Microsoft.Resources/deployments/write permissions on the subscription level.
- Azure Functions extension for VS Code (in case you are using VS Code. Alternatively, you can use Visual Studio and install the extension on the Visual Studio). - This is only required if you are running your SQL queries using Azure Functions
- SQLQueryStress - https://apps.microsoft.com/detail/9N46QJ5SBGKB (in case you want to run your queries locally and avoid additional costs to run your queries using Azure Functions or Azure Automation, you can install and use the app SQLQueryStress on your local environment, configure the database settings and run the desired queries.
- Python 3.9
Pricing varies per region and usage, so it isn't possible to predict exact costs for your usage. However, you can try the Azure pricing calculator for the resources below.
- Run the deployment to create your Azure SQL database
Warning This button will only create the Azure SQL database using the sample dataset AdventureWorksLT. Verify the costs of using this service before creating it.
Click the Deploy to Azure button to deploy the Azure resources for the demo.
-
Configure Azure SQL Server Firewall rules You need to configure the firewall rules to whitelist at least your IP address so you can access the database. If you are unfamiliar with this configuration, please refer to this link.
-
Connect to your Azure SQL database using the Azure Portal Once you configure the firewall rules, you can use the Azure Portal to connect to your Azure SQL Database as per the image below.
-
Enable Automatic Tuning on Azure SQL Database You have different ways to enable automatic tuning on your Azure SQL database. Please refer to the link provided above to enable the automatic tuning. By default, automatic tuning is only enabled to force the last good plan to ensure your queries run on your database using the best query plan.
-
Run your queries against your database
-
Run queries using Azure Functions If you decide to use an Azure Function to run the queries to your databases for 24+ hours, then you need to perform two steps:
-
Create an Azure Function NOTE: Please ensure that you select the runtime stack as Python and version 3.9.
-
Configure the Azure Function Settings You need to clone this repository to your local machine and then modify the function's settings to use your database's configuration before you deploy it. If you want to be sure the configuration changes you made are correct, you can run the function locally by following the guide link provided in the next step. Before running the function, you need to ensure you select your python interpreter available in your machine, as per image below.
-
Deploy the Azure Function's source code available in this repo to your newly created function
-
-
Run queries using SQLQueryStress In case you want to run your queries from your local environment, an easy way is to use the SQLQueryStress app, by doing the following steps:
-
Configure the app as per image below. Don't forget to copy the connection details from the Azure SQL database you created. Note I am setting a big number for the number of iterations so it can stay running for a couple of days before finalizing all iterations. Also, you can set a delay between queries to wait after completing the current query, before starting the new query. In terms of configuring the database settings, you can copy all details from the Azure Portal, by accessing your database's connection strings setting.
-
Run the queries available in this repo and wait for the results. You have different queries you can run to test out different scenarios, but I am going to give you this example so you can get inspired to write your own queries and try out the service. Note: Keep in mind that the queries you run should be queries that require some optimization/tuning in your database, so the service is able to identify the performance recommendations for those queries.
Copy content from the SQLQueries.sql file and paste it in SQLQueryStress app and run it. Note: In case you want to run multiple queries at same time, open separate instances of the SQLQueryStress app and configure the queries accordingly.
In case you are not sure if your query is unoptimized, you can run the query using the _Azure Data Studio _ and enable the query execution plan so you can analyze the plan and the results presented in the plan, as per image below.
-
-
Verify results After running your queries for at least 24 hours continuously, you should see performance recommendations for your database. You need to run your unoptimized queries for a significant time so the AI used by the service is able to learn from it and provide recommendations to improve the performance of your queries and database.
If you set your automatic tuning settings to ON for the create index and drop index, then you should see after a while logs on the tuning history showing the actions performed by the service to your database. Those actions will include applying and verifying the recommendations on your database.
You are welcome to contribute to this repository. If you find any issues or have suggestions/improvements to provide, please create a Pull request and provide all relevant information. Additionally, you can connect with me on LinkedIn and report any issues or discuss any suggestions and ideas.
