Azure SQL Database自动调优：无需手动管理索引即可开发？

Great question—this is something a lot of us who’ve migrated legacy databases to Azure SQL have wrestled with, especially after seeing how quickly auto-tuning can fix obvious performance gaps like you did. Let’s break this down:

首先，自动调优确实能帮你省大量精力 #

Your experience is spot-on: when you migrate an under-indexed legacy database, auto-tuning jumps in fast to create missing indexes that directly reduce CPU/DTU usage, and it’ll also clean up unused or redundant indexes over time. For most day-to-day, routine query patterns, it’s incredibly reliable.

但你不能完全抛弃手动索引维护——这里有几个关键场景： #

• 特定业务逻辑的查询：Auto-tuning relies on actual query load to make recommendations. If you have infrequent but critical queries (like end-of-month reports, bulk data imports, or internal analytics), auto-tuning might never pick up on their needs. For these, you’ll want to manually create covering indexes or optimize existing ones to avoid expensive key lookups.
• 全局索引策略：Auto-tuning operates per-database and per-table, which means it won’t account for cross-table query patterns or your team’s naming/structure standards. If you need consistent index conventions (like including certain columns for auditing, or aligning indexes with sharding strategies), manual planning is still necessary.
• 特殊表类型：Auto-tuning has limited support for memory-optimized tables or columnstore indexes. If your workload uses these, you’ll need to manually design and maintain indexes tailored to those storage engines.

开发者的实际做法：混合模式是主流 #

Most teams I work with use a hybrid approach:

• Let auto-tuning handle the "grunt work"—fixing missing indexes, dropping unused ones, and adjusting existing indexes as workloads shift. This frees up time from tedious maintenance tasks.
• Use tools like Query Store to identify slow or resource-heavy queries that auto-tuning hasn’t addressed, then manually optimize those. For example, if a report query is still taking too long even after auto-tuning, you might create a covering index that includes all the columns the query needs, eliminating the need for key lookups.

关于SSMS索引编辑器的痛点：换个方式更高效 #

You’re right—SSMS’s built-in index editor is clunky and slow. Instead, use T-SQL scripts to create or modify indexes directly. It’s faster and more repeatable. For example, here’s a quick script to create a covering index:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_Include
ON Sales.Orders (CustomerID)
INCLUDE (OrderDate, TotalAmount);

You can also grab ready-to-run index recommendation scripts directly from the Query Store or Azure Portal’s auto-tuning section—no need to mess with the SSMS editor at all.

总结 #

Auto-tuning is a game-changer for reducing routine index maintenance and fixing obvious performance issues quickly. It won’t replace manual index work entirely, but it will let you focus your energy on the high-impact, business-specific queries that matter most. This balance is where you’ll get the best efficiency and performance.

内容的提问来源于stack exchange，提问作者Tom Gullen