Анализируя состояние одного из серверов наткнулся на очень странную проблему- средняя стоимость запроса по счетчику оптимизатора составляла 135.956, что очень странно. Небольшой сервер, простые запросы- откуда? Пришлось перерыть кэш, поднять планы запросов. Было установлено, что в кэше хранились планы стоимостью 135418.
Что за чудеса?! Анализ планов показал, что косячит на таком вот нехитром запросе
DECLARE @new_oldest_id INT SET NOCOUNT ON SET ROWCOUNT 7 SELECT @new_oldest_id = instance_id FROM msdb.dbo.sysjobhistory ORDER BY instance_id SET ROWCOUNT 0 DELETE FROM msdb.dbo.sysjobhistory WHERE (instance_id <= @new_oldest_id)
В базе msdb, стало быть.
Эта таблица уже привлекала внимание, описание ситуации здесь http://www.sqlservergeeks.com/sql-server-job-performing-completion-actions-blocked/
Посмотрел в оболочке- ограничение 40 тыс.
SELECT COUNT(*) FROM msdb.dbo.sysjobhistory
40 тыс
А по плану запроса там..
Опттимизатор считает, что в таблице [msdb].[dbo].[sysjobhistory] 1288500000 записей
Прочекал базу- ошибок нет. Обновил статистику по базе, по таблице фулл- не помогло. Отребилдил кластерный индекс- все нормально, система поняла сколько записей в таблице и выстроила адекватные планы.
Статистика оптимизатора тут же стала налаживаться, кривая средней стоимости пошла вниз.
Выводы:
- Нужно следить за счетчиками оптимизатора, иногда там можно увидеть что-то интересное, диагностировать проблему на раннем этапе;
- Периодически нужно обслуживать базу, иначе ошибочные значения числа записей могут привести к использования совершенно неадекватных планов;
- msdb тоже база, ее тоже нужно контролировать, обслуживать и бакапить.
