Home / Databases / SQL Server

DatabasesChecklistIntermediate

After the SQL Server migration: validation, compatibility level and performance tuning

What to check in the first days after moving SQL Server to Azure or AWS. Covers data validation, statistics and index maintenance, raising compatibility level safely with Query Store, common performance gaps in the cloud, and monitoring set-up.

Deepak Bhardwaj3 min read

Applies toAzure SQL Database / MI, SQL Server on Azure VMs, Amazon RDS for SQL Server, SQL Server on EC2

Data and functionality

  • Row counts and checksums on critical tables match (see the cutover runbook).
  • Logins map to users (no orphaned users): EXEC sp_change_users_login 'Report' (older) or compare SIDs in sys.database_principals and sys.server_principals.
  • Jobs, alerts and operators present and scheduled (SQL Agent, Elastic Jobs, or RDS-specific jobs).
  • Linked servers, DB Mail, CLR and certificates work.
  • App and integration tests passed; month-end and batch jobs tested once.

Keep plans stable first

  • Leave the compatibility level as it was on the source at first.
  • Turn on Query Store (on by default in Azure SQL) in read-write mode.
  • Update statistics after the migration:
EXEC sp_updatestats;   -- or Ola Hallengren IndexOptimize with @UpdateStatistics = 'ALL'

Raise the compatibility level safely

  1. Capture a baseline in Query Store under the old level for a few days.
  2. Change the level: ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 160;
  3. Watch Regressed Queries in Query Store; force the old plan where needed:
EXEC sp_query_store_force_plan @query_id = 4123, @plan_id = 88;
  1. Or use automatic plan correction (Azure SQL / SQL Server 2017+):
ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

Common cloud performance gaps

Symptom Likely cause Fix
Slow writes, WRITELOG waits Log rate limit of the tier / disk throughput Bigger tier, Business Critical, faster disk (gp3/io2, Premium SSD v2)
PAGEIOLATCH waits IO latency higher than on-premises SAN More memory (buffer pool), more IOPS, better indexes
Chatty apps slow Network latency app ↔ DB (app still on-premises) Move the app too, batch calls, or put them in the same region/zone
tempdb contention Smaller tempdb on PaaS tier Larger tier; reduce temp table use
CPU high after cutover Plan changes, missing stats Update stats, Query Store plan forcing 
SELECT TOP 10 wait_type, wait_time_ms / 1000 AS wait_s
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%' AND wait_type NOT IN ('BROKER_TASK_STOP','XE_TIMER_EVENT','SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
ORDER BY wait_time_ms DESC;

Operations

  • Monitoring: Azure SQL (database watcher, Azure Monitor) / RDS (CloudWatch Database Insights) with alerts.
  • Backups: retention and PITR set; a restore tested.
  • HA/DR as designed (zone redundancy, failover group, Multi-AZ) and tested.
  • Cost: right-size after 2–4 weeks of real usage; reservations once stable.

Tip

Most "the cloud is slower" complaints in the first week are network latency between an app that stayed on-premises and the database that moved. Measure the round trip before tuning SQL.

Related fixes