SqlDependency: размножение дикобразов

Как размножаются дикобразы?

Очень, очень осторожно либо безрезультатно – что-то дикобразов нигде не видно.

(С) “Афера Томаса Крауна”

В связи с переходом на новую работу, на мне “подвисла” одна задача, которую я не успел решить в старом проекте. Связана она была, как нетрудно догадаться из темы, с SqlDependency. Дело получается относительно давнее (вот уже больше двух месяцев как я в этом проекте не участвую), но поскольку недавно стало известно, что проблема решена, я  ставлю “зарубку на память”.

Вкратце предыстория такова: в какой-то момент времени было принято решение использовать SqlDependency для очистки кэша настроек приложения – сценарий достаточно стандартный. Как всегда нестандартными (на первый взгляд) были проблемы, возникшие позже. Настройки хранятся в специальной БД (ConfigDB), к которой обращаются приложения (ASP.NET App, несколько экземпляров потенциально на разных серверах) и [вспомогательные] “сервисы” в виде служб и консольных приложений, запускаемых по расписанию.

В какой-то момент с ConfigDB начинаются проблемы:

• невозможно сохранить измененные настройки (timeout)
• измененные настройки не подхватываются клиентами
• SqlServer начинает ощутимо тормозить (хотя поначалу это не связывается с ConfigDB)
• журнал ошибок SqlServer-а забивается сообщениями с “жалобами” на query norifications (“The query notifications dialog conversation handle ‘{xxxxxx….}’ closed due to the following error …”, with error code 8490 or 8470)

Проблема живет только на продакшине и воспроизвести ее в тестовом окружении не удается. Попытки хоть как-то локализовать проблему дают следующий результат:

• создание и использование новой ConfigDB помогает, правда ненадолго – через пару дней/неделю все возвращается на круги своя
• восстановление ConfigDB из резервной копии занимает очень много времени – база объемом 500 Мб (из которых 400 – это журнал транзакций) при восстановлении на двухьядерной машине с 2 Гб оперативной памяти намертво подвешивает ее (процесс не завершился за 1.5 часа)
• выясняется, что в ConfigDB очень много открытых подписок (query notification) и их количество растет

Проблему в итоге удалось устранить путем:

1. Вдумчивого чтения и переработки кода, который работает с объектами SqlDependency.
2. Закрытия всех открытых подписок (об этом ниже).

По ходу работы пару раз слышались голоса “SqlDependency – это зло”, “и зачем вы вообще это используете”, “да никогда и ни в жизни не пользовать ЭТО”, “лучше сделать все ‘руками’”. В итоге скептики были посрамлены, механизм работает как и ожидалось, проблему устранили. Но должен признать, что работать с SqlDependency необходимо очень осторожно и внимательно.

Итак, о чем нужно помнить при использовании этой технологии:

• Строго следите за тем, чтобы на каждый вызов SqlDependency.Start приходился вызов SqlDependency.Stop – об этом явно написано в MSDN, но на моих глазах (в 2х случаях из 2х) об этом “забывали”, а потом долго искали решение возникших проблем. Хорошим решением будет обернуть всю работу с SqlDependency в некий класс (отвечающий за кеширование/инвалидацию), реализаций IDisposable, и помещение вызовов Start и Stop в конструктор и Dispose соответственно.
• В обработчике события SqlDependency.OnChange не ленитесь анализировать причину вызова обработчика (SqlNotificationEventArgs.Info). Игнорирование этого простого совета может привести (и рано или поздно приводит) к возникновению эффекта, похожего на "положительную обратную связь”: когда на сервере что-то пошло не так, вызвался обработчик, мы тут же создали новую подписку, добавив тем самым проблем серверу, который тут же снова вызовет наш обработчик.
• Не забывайте о протоколировании – наличие информации о том, когда и с какими параметрами вызывается обработчик события OnChange здорово упрощает диагностику.
• Проводите мониторинг SqlServer-а. Отслеживание количества активных подписок (select count(*) from sys.dm_qn_subscriptions) позволит вам на самых ранних этапах обнаружить “утечку” и устранить ее пока это не переросло в БОЛЬШУЮ проблему. Обычно именно мониторингом все склонны пренебрегать.
• Считайте количество подписок, которые будут регистрироваться на сервере в ходе нормального (штатного) функционирования вашего приложения. Десятки подписок – не проблема. При сотнях стоит уже быть ооочень осторожным. Если же расчетное количество подписок исчисляется тысячами, то следует подумать о изменении архитектуры с целью уменьшения этого числа.
• Не самой лучше идеей будет [активно] использовать этот механизм в базе данных с высокой нагрузкой.
• Если что-то пошло не так, помним о возможности “убить” подписку: KILL QUERY NOTIFICATION SUBSCRIPTION <subscription id/ALL>.

UPDATE: как было совершенно справедливо замечено Airex-ом, при использовании SqlDependency в контексте ASP.NET, наилучшим местом для вызова SqlDependency.Stop является Application_End (в Global.asax). И не стоит надеяться на деструктор того класса, в который вы “запрячете” всю работу с SqlDependency – в этой связи немного обновил (уточнил) первый пункт выше.

HTH,

AlexS