Artificial Intelligence tunes Azure SQL Databases

Automatic tuning To stay competitive, today’s businesses need to make sure that they focus on their core competencies that directly deliver customer value while relying on their cloud provider to offer an affordable, reliable, and easy to use computing infrastructure that scales with their needs. In the world of cloud services, where many aspects of running the application stack are delegated to the cloud platform, having artificial intelligence to manage resources is a necessity when dealing with 100,000’s of resources. Azure SQL Database is continuously innovating by building artificial intelligence into the database engine to improve performance, reduce resource usage, and simplify management. The most prominent use of artificial intelligence is the automatic tuning feature that has been globally available since January 2016. Automatic tuning uses artificial intelligence to continuously monitor database workload patterns and recognize opportunities to improve the database performance. Once confidence is built that a certain tuning action would improve the performance of a database, the Azure SQL Database service automatically does the tuning action in a safe and managed fashion. The service monitors each tuning action and the benefits to performance are reported to the database owners. In the infrequent case of a performance regression, the service quickly reverts the tuning action. Click here to read more about automatic tuning. In this blog, I want to share a few examples of how Azure SQL Database customers have benefited from the automatic tuning feature. Tailored indexes for each out of 28K databases SnelStart is a company from Netherlands that uses Azure and Azure SQL Database to run their software as a service. Over the last few years, SnelStart has worked closely with the SQL Server product team to leverage the Azure SQL Database platform to improve performance and reduce DevOps costs. In 2017, SnelStart received the Microsoft Country Partner Netherlands award, proving their heavy investment in Azure and collaboration with Microsoft. SnelStart provides an invoicing and bookkeeping application to small and medium-sized businesses. By moving from desktop software to a hybrid software-as-a-service offering built on Azure, SnelStart has drastically decreased time to market, increased feature velocity and met new demands from their customers. By using the Azure SQL Database platform, SnelStart became a SaaS provider without incurring the major IT overhead that an infrastructure-as-a-service solution requires. SnelStart uses a database per tenant architecture. A new database is provided for each business administration and each database starts off with the same schema. However, each of these thousands of customers have specific scenarios and specific queries. Before automatic tuning, it was infeasible to tune every database to its specific usage scenario. The result was over indexing from trying to optimize for every usage scenario in one fixed schema. Individual databases did not get the attention they needed to be tuned and this resulted in less than optimum performance for each database workload. Now, using automatic tuning, all of this is history. SnelStart now has about 28,000 databases and automatic tuning takes care of them all. Automatic tuning focuses on each database individually, monitors its workload pattern, and applies tuning recommendations to each individual database based on its unique workload. These recommendations are applied safely by choosing the time when database is not highly active. All automatic tuning actions are non-blocking and the database can be fully used before, during, and after each tuning action. For two months, SnelStart gradually enabled automatic tuning for their databases. During that period, automatic tuning executed 262 tuning operations on 210 databases, resulting in improved performance on 346 unique queries across these databases. The following chart shows the roll out of automatic tuning across the SnelStart database fleet. By enabling automatic tuning on their databases, SnelStart got a virtual employee that focused on optimizing database performance. In case of SnelStart, this virtual employee did a great job and optimized an average of ~3.5 databases per day. SnelStart saved a lot of time and was able to focus on improvements in their core value prop instead of on database performance. “Using automated index tuning, we can further maximize the performance of our solution for every individual customer.” – Henry Been, Software Architect at SnelStart. Managed index clean-up AIMS360 is a cloud-based service provider for fashion businesses that empowers fashion labels to manage and grow their business by giving their customers more control of and visibility into their business. Additionally, AIMS360 gives back the time to their customers to focus on fashion instead of processes around their business. AIMS360 has been in this business for over thirty years and working with their software is taught in fashion-related schools throughout the United States. Each fashion business that buys the AIMS360 service, gets its own database. AIMS360 has thousands of databases. The database schema for each database is identical and has evolved over time as new features and capabilities were added into their product. However, trying to optimize the performance of each workflow in their application left their databases with duplicated indexes. SQL Server allows duplicated indexes and once they exist for every related update, duplicated indexes need to be updated – resulting in unneeded use of database resources. Over indexing is a wide spread problem that exists on large numbers of databases. The cause is different people working on the same database without the time to analyze and/or track what happened previously on the database. Looking at the automatic tuning internal statistics, our team was surprised to see that there is double the amount of drop duplicate index recommendations compared to the create index recommendations. AIMS360 enabled automatic tuning across all their databases to simply take care of this duplicate index problem. Since enabling automatic tuning, the SQL Database service has executed 3345 tuning actions on 1410 unique databases and improving 1730 unique queries across these databases. By choosing the right time to drop duplicated indexes, automatic tuning got the problem safely out of the way. In background, over a couple of days, automatic tuning dropped all duplicated indexes. Automatic tuning takes care of not putting a lot of pressure on databases or elastic pools. When multiple tuning actions need to be executed on a single database or within a single elastic pool, these actions are queued and executed with safe parallelism. “Using the automatic tuning feature, our team was able to quickly and efficiently fine tune our databases. Since we have over 1400 databases, traditional methods of tuning would be very labor intensive. However, with automatic tuning we were able to analyze 1000’s of queries and get them tuned instantly. “ – Shahin Kohan, President of AIMS360 Reducing resource usage for thousands of applications Microsoft IT applications use Azure SQL Database heavily for thousands of applications. These applications support various internal applications at Microsoft. The footprint of Microsoft IT in Azure SQL Database is in the thousands of databases. These workloads are diverse – spanning from light, sporadic usage to enterprise grade workloads using resources in higher premium tiers. This variety of applications is not easy to keep an eye on. Microsoft is enabling automatic tuning on all internal workloads, including Microsoft IT, to reduce the DevOps cost and improve the performance across applications that are relying on Azure SQL Database. These same problems are present in any enterprise IT department around the world and all of them have a set of common goals: reduce the total cost of ownership, reduce DevOps cost, and improve performance. Automatic tuning, by continuously monitoring and tuning all the databases in parallel, is constantly making progress towards these goals. Microsoft IT started using automatic tuning as soon as it became available for preview, but the stronger engagement to enable it for all databases started in Q2 2017. Gradually rolling out the automatic tuning for different groups within Microsoft IT has enabled us to carefully measure the benefits achieved by each group. Special success was achieved within Microsoft IT Finance group. In the following chart, you can see the number of databases tuned each day that belong to the Microsoft IT Finance group. Spike that happened on 3rd of May is caused by enabling automatic tuning on all the databases that belong to this group. After every tuning action, Azure SQL Database measures the performance improvement by comparing the resource usage of queries before and after the tuning action. Verification lasts until statistically significant sample has been collected so improvements in performance can be accurately measured. During this verification frequency of queries that have been improved is measured as well. Using this information, we can calculate the amount of CPU hours that have been saved due to tuning actions. The preceding chart shows that databases that belong to Microsoft IT Finance group now use ~250H less CPU than before enabling automatic tuning. In addition to improving the performance by reducing the duration of the queries, this reduction of resource usage directly translates to the reduction of price – Microsoft IT Finance can now decrease the pricing tier of certain databases while keeping the improved performance. You can find all the details regarding this case in this video. Summary Azure SQL Database customers are already heavily relying on automatic tuning to achieve optimal performance. In these customer stories, you see how different applications benefit by using automatic tuning – from optimizing similar workloads for SaaS applications to optimizing thousands of different applications for enterprises. Additionally, automatic tuning helps you to finally get rid of all those unused and duplicated indexes without an effort! Enable automatic tuning on your database and let the platform do the work for you – Click here to read more about how to enable automatic tuning.
Quelle: Azure