Automatically Indexing Millions of Databases in Microsoft Azure SQL Database

Recently read an article paper describing the process of developing a system to Automatically Indexing Millions of Databases in Microsoft Azure SQL Database.

Index Tuning is difficult , tuning indexes at large scale is even more challenging. 

The notes below are some key points , but for more in-depth click through to the article 

Challenges 

-Index Tuning is both schema and data dependent  i.e different workloads have different requirements 

-Massive variation across a large pool of databases

-How to scale the auto-indexing to all SQL databases while maintaining statutory requirements  over 140 countries

-How to automate the critical inputs for tuning 

-How to quickly roll-back if problem arises 

-Ensure auto-indexing with performance impact 

Architecture 

-Control Plane - brain of the system. Coordinating a suite of microservices 

-Rely on the SQL Server Query Optimizer and Query Store  

Learnings

 -using techniques such as resumable index create can allow better management for log truncation or pause ig high contention 

-Using operations whithout blocking queries. Overcoming The SQL Servers lock scheduler FIFO by using managed lock priorities to avoid blocking current user transactions 

To read the full article Automatically Indexing Millions of Databases in Microsoft Azure SQL Database

Read more on Index Tuning 

Top 5 SQL Server DMV for Index Analysis

How to use Database Tuning Advisor via command line

How to find Longest running queries in SQL Server

SQL Server Performance Killers