As shown in the previous post that Artificial Intelligence has become or if not convinced yet then let’s say that is becoming an integral part of our life. This is not just limited to the devices but the tools we are using are also actively incorporating AI into their functionalities.
SQL Server laid down the foundation of its prominent AI feature about five years back. Yes, Microsoft made Automatic Tuning feature of SQL Server globally available in 2016 which is by default enabled in Azure SQL Databases and is AI-driven. Read our earlier post to learn Automatic Tuning in detail which also briefed about AI usage and its role in automatic performance improvement.
In this post, let us extend our discussion with much more emphasis on Artificial Intelligence exclusively –
The basic principle of artificial intelligence driven by machine learning is to make machines learn on themselves based on their experience just like humans learn i.e.
- It continuously monitor the database workload pattern and identifies the opportunities to improve database performance
- Once the confidence is built that a certain tuning would improve the performance, it applied it automatically
- However, if a tuning applied previously gets regressed quickly or with span of time that it is not giving performance benefits any more, it corrects this as well by reverting the tuning action
- Tuning recommendations are applied only when database is not highly active
- All tuning actions are non-blocking and the database can be used like before or even during the tuning action as well
Over indexing is a wide spread problem that exists on many databases, if not all. There could be many reasons for it like developers not having enough experience and maturity to take it seriously or sometimes, many developers working on the same object/database without having time to analyze or see what happened previously but this automatic tuning comes as a rescue to them. However, it needs considerable time and testing. In some situations, automatic tuning shines in handling such issues on its own.
More time, this automatic tuning is enabled on a database, more statistics, SQL Server or say AI running behind this has to learn from it, so true benefits of it starts coming from when you enable it at least for couple of months for a moderate database though this varies from database to database depending on how active it is or how regularly different query patterns are executed.
So, this is how automatic tuning learn on its own by leveraging AI capabilities without any manual intervention.
This might have sound little theoretical but the whole purpose of this post was to make you aware or emphasize that SQL Server itself is also using AI. In fact, Artificial Intelligence itself is also a conceptual study.
Many Thanks for reading!