🌏 閱讀中文版本
Database migration or upgrades in cloud environments (such as migrating from SQL Server to Azure SQL Database) often result in query performance degradation. This article shares practical optimization workflows after Azure SQL migration, including:
- One-click statistics and index updates
- Querying Top SQL from the past 3 days
- Generating index adjustment recommendations
- Auto-tuning configuration and considerations
🧠 Why Re-optimize After Migration?
- Query plans are recompiled and may differ from the original environment
- Statistics may not be updated in real-time
- Azure I/O caching and resource scheduling logic differs
- Existing indexes may be unused or ineffective
🔄 Step 1: One-Click Statistics and Index Update
The first step to avoid performance issues is updating statistics and rebuilding indexes:
-- Update all statistics
EXEC sp_updatestats;
-- Rebuild all indexes (ONLINE mode recommended)
EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REBUILD WITH (ONLINE = ON)';
📊 Step 2: Analyze Top SQL from Past 3 Days
✅ Enable Query Store (if not already enabled)
ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE = ON;
✅ Query top 10 SQL statements by CPU time (in seconds)
SELECT TOP 10
qt.query_sql_text AS SQLStatement,
qs.query_id AS QueryID,
SUM(rs.count_executions) AS TotalExecutions,
ROUND(AVG(rs.avg_duration) / 1000.0, 3) AS AvgDuration_Sec,
ROUND(AVG(rs.avg_cpu_time) / 1000.0, 3) AS AvgCPUTime_Sec
FROM
sys.query_store_query_text qt
JOIN
sys.query_store_query qs ON qt.query_text_id = qs.query_text_id
JOIN
sys.query_store_plan p ON qs.query_id = p.query_id
JOIN
sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
GROUP BY
qt.query_sql_text, qs.query_id
ORDER BY
AvgCPUTime_Sec DESC;
🧠 Step 3: Review Usage and Generate Index Adjustment List
The following query lists all indexes with their usage counts and last query times:
SELECT
OBJECT_NAME(s.object_id) AS TableName,
i.name AS IndexName,
s.index_id,
s.user_seeks,
s.user_scans,
s.user_lookups,
s.user_updates,
s.last_user_seek,
s.last_user_scan
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i
ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
ORDER BY s.user_seeks + s.user_scans DESC;
Metric Interpretation:
user_seeks: Number of index seek operationsuser_scans: Number of full table scansuser_lookups: Number of bookmark lookups (higher is worse)user_updates: Cost of maintaining the index
🛠 Step 4: Create Appropriate Indexes for Queries
Example query:
SELECT [u].[Time], [u].[UserId], [u].[ProductId]
FROM [UserTakeProductRecords] AS [u]
WHERE ([u].[Time] >= @__todayStartTime_0) AND ([u].[ParentId] = 0)
Recommended index:
CREATE NONCLUSTERED INDEX IX_UserTakeProductRecords_ParentId_Time
ON dbo.UserTakeProductRecords (ParentId, Time)
INCLUDE (UserId, ProductId);
Explanation:
Use equality condition ParentId first, then range condition Time, and add INCLUDE to avoid bookmark lookups.
🤖 Step 5: Enable Azure SQL Auto-Tuning
- Force Plan (FORCE PLAN): Recommended to enable ✅
- Create Index (CREATE INDEX): If you can control naming and column order, handle manually ⚠️
- Drop Index (DROP INDEX): Recommend engineer review before execution ⚠️
Azure SQL index creation/deletion does not cause downtime but may have brief locking.
📌 Summary: Azure SQL Performance Tuning Workflow
- Execute statistics and index rebuilds after migration
- Enable Query Store and monitor Top SQL
- Evaluate hot and cold index usage
- Create new indexes for critical queries (with INCLUDE)
- Configure auto-tuning but keep manual review space
Related Articles
- Azure Elastic Pool vs AWS Aurora Complete Comparison: Architecture, Performance, Cost, and Selection Guide
- SQL Server Full-Database Text Search: Complete Implementation Guide and Performance Optimization
- IaaS, PaaS, and SaaS Concepts and Examples in Azure / AWS
- Azure / AWS 中的 IaaS、PaaS 與 SaaS 概念與應用範例
- Azure DMZ Implementation Guide: Complete Handbook for Security Professionals