Friday, March 30, 2018

Azure SQL Database – Automatic Index Tuning


Introduction: -

We live in an era of “Automation”.  Planes run on auto pilot mode, cars will soon be auto driven, so why not have auto indexed databases? This article will discuss the automatic index tuning feature available in Azure SQL Database.

Problem Statement:

Application development is moving at a rapid pace and its very common for application teams to develop a template database design and deliver it to multiple customers.  It is even more common in cloud where solutions are delivered as “software as a service” and it is way too challenging for application development team to analyze the workload of individual database, design specific indexes for them and tune them.

Automatic tuning in Azure SQL Database

Azure SQL Database, the cloud Platform as a service (PAAS) offering of SQL Server has attempted to address the above problem by introducing “Automatic tuning”. As of date, “automatic tuning” has the following features

·         Automatic plan correction – Plan regressions or sudden change in query plan resulting in performance degradation are tracked and last good plan available is forced. Automatic plan correction is already well explained over here

·         Automatic Index Tuning:  Automatic index tuning feature of Azure SQL Database will study the workload executed against the database, identify useful indexes and create them automatically. Automatic index tuning will also drop indexes that are unused over 93 days or any duplicate indexes present.

How to enable automatic index tuning

Once logged in to Azure Portal (portal.azure.com) one can enable the same by navigating to SQL Databases-> Click on the database -> Automatic tuning


·         Force plan -> Enables Automatic Plan Correction
        ·         Create / Drop Index -> enables automatic index tuning

Features can be enabled at server level and can be inherited at the database level as well.

TSQL Script for enabling automatic index tuning provided below
ALTER DATABASE DBName
          SET AUTOMATIC_TUNING (CREATE_INDEX = ON, 
      DROP_INDEX = ON)

Example:

To test how Auto Indexing works, following things were performed
·         A sample database with 5 tables with no indexes was created
·         10 to 15 select queries against the tables were executed repeatedly few thousand times over 3 to 4 hours
·         After one day, automatic indexing found the recommendations and rolled them out when there was less or no workload on the database.  Auto Indexing requires minimum one day of monitoring to identify recommendations
 This can be verified on Azure Portal by clicking on the “Database name” -> “Performance Recommendation”


The same can also be verified via the DMV sys.dm_db_tuning_recommendations, documented clearly over here
Key pointers of Automatic Index Tuning

·         Indexing recommendations are obtained using advanced artificial intelligence rules applied on the monitored workload. The recommendations are expected to be lot more accurate than earlier missing index recommendations from SSMS
·         The best part of the feature is it is non-blocking during the rollout of new indexes. 
·         Index changes wouldn’t be rolled out and will be postponed, if DTU usage was above 80% anytime in the last 20 minutes
·         Automatically created indexes don’t enforce any dependency on the columns. When the column with automatically created index is dropped, index is dropped as well
·         Auto created indexes can be tracked via sys.indexes – auto_created column
Learn – Adapt - Verify

One of the key features of Automatic Index tuning is the ability to monitor the impact of the changes done via automatic tuning. Azure SQL Database closely monitors the automatically created or dropped indexes and verifies if it has achieved the desired performance improvement. If it doesn’t then the change is automatically reverted to prevent any negative impact to the database. The reverted changes can be tracked from sys.dm_db_tuning_recommendations DMV as well.


Suggested enhancements



·         Automatic indexing is available only as an Azure SQL Database feature and is not available on SQL Server on premise. If would be wonderful if the feature could be extended to on premise SQL Server.
·         Automatic Index tuning should be extended to fixing fragmentation issues of the indexes as well.
Conclusion
 Automatic Index Tuning does simplify one of the major tasks of a database administrator and a developer. Automatic Index Tuning may not be 100% perfect to get the choices correct all the time but it is certainly a step in the right direction. Expect Microsoft to release more of such features in the days to come not just in indexing but also all areas of database maintenance

44 comments:

Erfan said...

I'm very much inspired when I've visited your blog. Your blog is really informative. Hope you will continue with the new article.

http://tutorialabc.com

Erfan said...

I'm very much inspired when I've visited your blog. Your blog is really informative. Hope you will continue with the new article.

tutorialabc.com

ww said...

I love this blog . This is one of the best blog i ever seen. It's all about what i'm searching for. I love to read this blog again and again . Every time i enter this blog i get something new. This blog inspire me to write new blog. I write a blog name http://tutorialabc.com. It's about sql,c#,net etc

Unknown said...

Very informative, Keep Sharing Azure Online Training

Susmitha Bommepalli said...

Very nice information. I would like to appreciate you. Keep it up!Best Data Science Online Training Institute In Hyderabad | Online Data Science Training
Data Science Online Training Institute In Hyderabad
Data science online training in hyderabad
Best data science training in hyderabad



supreet said...


hi, nice information is given in this blog. Thanks for sharing this type of information, it is so useful for me. nice work keep it up. best sap simple finance online training institute in hyderabad

Priya said...

Thanks for sharing this useful article. Are you searching the correct partner to check Database Backup and Restore services? Try this company Database Backup and Restore services in usa

kristinahojholt said...

I’m not that much of a internet reader to be honest but your blogs really nice, keep it up! I'll go ahead and bookmark your website to come back in the future. Cheers epicor baq

Preeti Pundir said...

You shared very interesting article and i really appreciate it. Keep going!!
I am a data analyst and providing data analytics course in saket

kirankumar said...
This comment has been removed by the author.
kirankumar said...

Good information I loved it
Sanjary Kids is one of the best play school and preschool in Hyderabad,India. The motto of the Sanjary kids is to provide good atmosphere to the kids.Sanjary kids provides programs like Play group,Nursery,Junior KG,Serior KG,and provides Teacher Training Program.We have the both indoor and outdoor activities for your children.We build a strong value foundation for your child on Psychology and Personality development.
Preschool in hyderabad

kirankumar said...

Good blog information by the author
Sanjary Academy provides excellent training for Piping design course. Best Piping Design Training Institute in Hyderabad, Telangana. We have offer professional Engineering Course like Piping Design Course,QA / QC Course,document Controller course,pressure Vessel Design Course, Welding Inspector Course, Quality Management Course, #Safety officer course.
Piping Design Course in India­

kirankumar said...

Good blog information of the author provided

Pressure Vessel Design Course is one of the courses offered by Sanjary Academy in Hyderabad. We have offer professional Engineering Course like Piping Design Course,QA / QC Course,document Controller course,pressure Vessel Design Course,Welding Inspector Course, Quality Management Course, #Safety officer course.
Document Controller course
Pressure Vessel Design Course
Welding Inspector Course
Safety officer course
Quality Management Course
Quality Management Course in India

Dixit Mishra said...

Thanks for sharing such knowledge. Learners can also learn
SQL Server Database Training in Gurgaon

Bhanu Sree said...

Good information!!!
Azure DevOps online training
Microsoft Azure DevOps Online Training

Chandra Sekhar Reddy said...

Nice Article
Sanjary kids is the best playschool, preschool in Hyderabad, India. Start your play school,preschool in Hyderabad with sanjary kids. Sanjary kids provides programs like Play group,Nursery,Junior KG,Serior KG,and Teacher Training Program.
play school in hyderabad, India
Preschool in hyderabad, India
Preschool teacher training course in hyderabad, India
pre and primary teacher training course in hyderabad,India
early childhood teacher training course in hyderabad, India

qa qc course said...

Nice Information
Best QA / QC Course in India, Hyderabad. sanjaryacademy is a well-known institute. We have offer professional Engineering Course like Piping Design Course, QA / QC Course,document Controller course,pressure Vessel Design Course, Welding Inspector Course, Quality Management Course, #Safety officer course.
QA / QC Course
QA / QC Course in india
QA / QC Course in hyderabad

TNK Design Desk said...

Very useful and informative blog. Thank you so much for these kinds of informative blogs.
who provides seo services ande-commerce development services.
best website design services in gurgaon
best web design company in gurgaon
best website design in gurgaon
website design services in gurgaon
website design service in gurgaon
best website designing company in gurgaon
website designing services in gurgaon
web design company in gurgaon
best website designing company in india
top website designing company in india
best web design company in gurgaon
best web designing services in gurgaon
best web design services in gurgaon
website designing in gurgaon
website designing company in gurgaon
website design in gurgaon
graphic designing company in gurgaon
website company in gurgaon
website design company in gurgaon
web design services in gurgaon
best website design company in gurgaon
website company in gurgaon
Website design Company in gurgaon
best website designing services in gurgaon
best web design in gurgaon
website designing company in gurgaon
website development company in gurgaon
web development company in gurgaon
website design company

Oceanapart Technologies said...

I literally enjoyed to read your blog post. thanks for sharing such a informative content.
Oceanapart technologies - databackup & transfer

DevOps said...

Thanks for sharing this blog.This article gives lot of information.
Azure Development Online Training

Bhanu Sree said...
This comment has been removed by the author.
Bhanu Sree said...


This was truly awesome blog!!
SQL Azure Online Training
Azure SQL Training
SQL Azure Training

Bhanu Sree said...
This comment has been removed by the author.
Best waterproofing services in hyderabad said...

Thanks that was really a wonderful content.
We are the best waterproofing services in Hyderabad.We are providing all kinds of leakage services which includes bathroom,roof,wash area,water tank,wall cracks,kitchen leakage services in Hyderabad. With trust and honest, we solve the issue as quick as possible.We serve you better compared to others.
Best waterproofing services in hyderabad
bathroom leakage services in hyderabad
roof leakage services in hyderabad
water tank leakage services in hyderabad
kitchen leakage services in hyderabad
Hyderabad waterproofing services

sara jenifer said...

I like this topic.This site has lots of advantage.I found many interesting things from this site. It helps me in many ways.Thanks for posting this again.
BCOM 1st, 2nd & Final Year Year TimeTable 2020

Anonymous said...

Really nice blog, very infromative. You can refer more topics related to SQL like SQL Commands and Advanced SQL from here for future articles
thanks !

Anonymous said...

Excellent post
Thanks for sharing
R programming Training Course In Delhi

artificial intelligence course in delhi

Graphic Designing Training in Delhi

data analytics courses in delhi

SASVBA

GMP

For more information

Anonymous said...

THE EXCELLENT POST
THANK YOU SHARING


Data Analytics institute in Delhi
Java Android Training in Delhi
Mern Stack Training in Delhi/NCR
Advance Excel Training in Delhi
SASVBA
GMB

FOR MORE INFO:

Anonymous said...

Thanks for the Valuable information.Really useful information. Thank you so much for sharing. It will help everyone.

Data analytics course in Delhi

FOR MORE INFO:

Parul Pathak said...

This site helps to clear your all query.mgsu ba 1st year result mgsu bsc 3rd year result This is really worth reading. nice informative article.

Brisk Logic said...

great info

Brisk Logic said...

Nice ,

Unknown said...


Azure devops backup

Digital Minded said...

DevOps is a new trend in software development over the past few years. Although the term is relatively new, it is actually a combination of many practices that have been in use for years. DevOps allows software to be released quickly and efficiently, while still maintaining high levels of security. Azure devops backup

Cyanous said...

Very useful information.Thankyou so much for this wonderful blog…Great work keep going. Looking for the best database services in Hyderabad hire Cyanous software solutions now.
Best Database services in Hyderabad
Best software & web development company in Hyderabad

Unknown said...

Nice Post
https://tajendrasengar.blogspot.com/2011/09/list-of-post-gadget-for-blogger-using.html?showComment=1628682995141#c3300166967764846695

Unknown said...

amazing
SMO Services in Delhi

Automotive Locksmiths Melbourne said...

Thanks for sharing such a good information
Contact us:
Locksmith Melbourne,
Melbourne, VIC-3000,
Australia.
Phone: 03 9464 1230
eMail: info@locksmithmelbourne.net
Web: https://locksmithmelbourne.net/

Niharika Sree said...


Very nice article,Thank you for sharing!!
msbi online training | msbi training online

DB Exam Study said...

This is great stuff!! need to share lots of articles for the reader who like your blog and thanks for sharing your ideas and tips
1Z0-750: Oracle Application Express 18: Developing Web Applications

evoseedbox said...

I am glade that you have shared such a helpful expected article, this will be very important for those who are looking for this type of blog.WEB Designing Services in chandigarh

Anonymous said...

Very rapidly this web page will be famous amid all blogging and site-building users, due to its pleasant articles or reviews Spot on with this write-up, I truly believe that this site needs a great deal more attention. I’ll probably be back again to read more, thanks for the info!

BCom 1st Year Admit Card 2022
BCom 2nd Year Admit Card 2022
BCom 3rd Year Admit Card 2022

seo work said...


خرید و فروش آهن آلات

خرید و فروش انواع آهن الات شامل لوله ، نبشی ، ناودانی ، ورق ، پروفیل ، تیر آهن ، استیل ، فولادهای آلیاژی ، میلگردهای صنعتی توسط آهن استقلال

خرید و فروش آهن آلات

worli day pannel chart said...

Thank you for taking the time to publish this information very usefully!

janta morning pannel chart
central mumbai pannel chart