Как разобраться с кейсом: производительность SQL Server после INDEX REORGANIZE?

После INDEX REORGANIZE на большой базе в AlwaysOn AG падение производительности может быть не из-за “плохой реорганизации” как таковой, а из-за побочных эффектов: вырос поток логов, вторичная реплика начала отставать, планы запросов остались старыми, статистика не обновилась полноценно, а часть индексов после reorganize всё равно осталась не в лучшем состоянии. Важно не чистить всё подряд, а сначала понять, где именно деградация.

Первое — проверьте состояние AG и redo queue на вторичной реплике:

SELECT
  DB_NAME(database_id) AS db_name,
  log_send_queue_size,
  redo_queue_size,
  redo_rate,
  synchronization_state_desc
FROM sys.dm_hadr_database_replica_states;

Если redo queue большая, вторичная реплика просто не успевает применить изменения. В этот момент чтение со secondary может стать заметно медленнее. Тогда нужно временно снизить нагрузку, дождаться выравнивания очереди, проверить диск/CPU на secondary и скорость redo.

Второе — reorganize не обновляет статистику так, как это делает rebuild. После массовой реорганизации я бы точечно обновил статистику по таблицам, где просели запросы:

UPDATE STATISTICS dbo.TableName WITH FULLSCAN;
-- или хотя бы
EXEC sp_updatestats;

Но sp_updatestats не всегда достаточно для критичных больших таблиц. Лучше взять 5-10 самых медленных запросов из Query Store и посмотреть, какие таблицы и планы изменились. Если Query Store включён, сравните планы “до” и “после”, при необходимости временно зафиксируйте хороший план.

Третье — проверьте waits. Если появились HADR_SYNC_COMMIT, WRITELOG, PAGEIOLATCH, CXPACKET/CXCONSUMER, причина будет разная. Например, WRITELOG указывает на журнал, а не на индексы.

Что делать практически: дождаться очистки redo queue, обновить статистику на проблемных таблицах, проверить Query Store, не очищать процедурный кеш глобально без причины, а точечно перекомпилировать проблемные процедуры. На будущее для больших БД лучше использовать Ola Hallengren scripts или аналогичную стратегию: rebuild/reorganize по порогам, лимиты по времени, обновление статистики отдельно и запуск в окно, где secondary успевает переварить лог.