liveBook · Manning

Autotuning

As explained in Microsoft Azure in Action

Automatic tuning is a database optimization capability that analyzes workload behavior and recommends or applies configuration changes to improve performance.

A database can run well most days and still struggle when shopping traffic peaks. Automatic tuning helps by examining the workload and identifying performance changes worth making. In Azure SQL Database, it can recommend index changes and, depending on the options enabled, act on them automatically. section 10.4.1 section 10.3.3

Azure SQL Database offers three separately controlled options:

  • Force Plan uses the last known efficient query plan when a performance decline is detected. section 10.4.1
  • Create Index adds indexes that the workload indicates could help queries run faster. section 10.4.1
  • Drop Index removes redundant or duplicate indexes that consume storage and may slow updates. section 10.4.1

Finding useful indexes

Suppose an ordering database already has primary-key indexes on its tables. Database Advisor might also recommend indexes on OrderItems.OrderID, OrderItems.BookID, or Orders.OrderDate for the queries the workload actually runs. In the Azure SQL Database Automatic Tuning menu, Force Plan, Create Index, and Drop Index can each be enabled or disabled; leaving them in inherit mode is recommended. section 10.4.1

Azure Database for PostgreSQL also offers automatic performance tuning, but that does not rule out manual tuning for optimal performance. Treat automation as help with performance decisions, not a promise that every query will be fast. section 10.3.3

How Microsoft Azure in Action covers it

How deep it goes
Explains three tuning options and where to control them, without detailing the algorithms behind their decisions.
Examples
Uses an ordering database to suggest indexes on order-item identifiers and order dates.
What it assumes
Introduces primary-key indexes and additional query indexes through database tables, without assuming knowledge of tuning algorithms.
What you can do after
Operate/configure it through the Azure SQL Database Automatic Tuning menu by choosing settings for Force Plan, Create Index, and Drop Index.
How it connects
Connects automatic tuning to indexes, query plans, storage usage, and update performance.
Cover of Microsoft Azure in Action
liveBookmanning.com
Read Microsoft Azure in Action on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Microsoft Azure in Action. The section links point to the parts of the book it draws on.