Automatically Indexing Millions of Databases in Microsoft Azure SQL Database

16 November,2021 by Tom Collins

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  

 

 

Auto_index_architecture

 

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


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Automatically Indexing Millions of Databases in Microsoft Azure SQL Database


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer