Процедура медленно читается из SqlDataReader

Для тех, кому некогда читать, сразу перейду к сути: дело таки оказалось в хранимой процедуре, а не в C# коде...



Столкнулись с интересной проблемой. Как впоследствии оказалось, она была вызвана сразу несколькими неудачными совпадениями.

Есть сложный и важный для бизнеса Windows service, который ночью обрабатывает 50 миллионов записей из базы данных SQL Server. При этом результаты промежуточных вычислений сохраняются во временные таблицы. Не те, которые в TempDB, а обычные таблицы. Просто используются как временные.

Я этот процесс слегка поменял. И вот хранимая процедура, которая делает join временных таблиц, вдруг начинает бешенно тормозит. Причем это случается уже на боевом сервере, и откатываться назад поздно. Ну, хорошо - она возвращает около 300 тысяч записей, что немало... Но ведь тестировали много раз на реальных данных, в разных окружениях (Development, UAT, Stress). И всё летало, занимало секунды, а теперь часы.

Возможно, какие-то установки Production-сервера отличаются от тестовых?  Хотя стараемся, чтобы всё было похоже. Или из-за различий в объеме оперативной памяти планы запросов меняются?

К счастью, хранимая процедура представляет собой один большой запрос. Копирую его, запускаю отдельно... летает! А вот в виде процедуры тормозит. DBA посмотрел. Говорит, что, поскольку процедура компилируется, план запроса может быть не совсем актуальный. Поковырялся, добавил индексов, и процедура тоже стала летать.

"А включаешь - не работает". Запускаю свой сервис, который вызывает ту же процедуру - бешенные тормоза. Ну, тут, конечно, у всех появляется мысль, что этот как-то связано с C#-кодом. Надо заметить, мы практически не используем ORM - в основном всё делает через SqlDataReader, чтобы побыстрее. Поэтому сказать, что, например, Linq-to-SQL сгенерировал тупой запрос, нельзя. Да и какой тут запрос, если я просто вызываю процедуру?

Может, это как-то связанно с нагрузкой? Например, соединения с базой данных недостаточно быстро возвращаются в пул? Много раз проверяю свой код. Да вроде всё нормально написано, ресурсы освобождаются. Причем в других местах использую такой же код, и работает нормально.

Может, процедура возвращает поля с какими-нибудь устаревшими типами данных вроде text? И это как-то замедляет работу? Да нет, только обычные float, char, varchar...

Я чуть-чуть успокоился, когда выяснилось, что и до моих изменений на боевом сервере процесс занимал много часов. Т.е. проблема старая. Просто её никто не замечал. Ну, подумаешь, вычисления стали занимать полдня? Если за сутки всё успело посчитаться, то система вроде как работает нормально. Но сейчас, к сожалению, из-за изменений алгоритма нужно пересчитать данные задним числом, за целую неделю, а не за день, и на это просто не хватит времени...

Может, всё-таки запуск запроса из SQL Management Studio - это не то же самое, что запуск из Windows service? Какие-нибудь параметры соединения не такие, что ли? Тут DBA говорит: "Давай-ка я сам напишу простой C#-код, который вызывает процедуру и считывает все из SqlDataReader". Пишет. И у него всё летает! Тут начинаются шутки, что может его перевести из админов в разработчики, раз у него так здорово получается?

Мучительно сравниваю его код со своим. Да вроде никаких серьезных отличий...

Тут пришла идея хака: вместо того, чтобы вызывать процедуру, временно вставить в C# код непосредственно код SQL-запроса. Хотя DBA это не приветствует, но надо срочно что-то решать... Делаю - летает!

Итак, прокатило, непосредственная проблема решена. Но надо ж теперь разобраться, в чем была причина?

Во-первых, оказалось, что нашему DBA просто повезло. Его C#-код работал точно так же, как мой. Просто он его запустил непосредственно после того, от откомпилировал процедуру. На том и порешили: к определению процедуру добавили WITH RECOMPILE. Всё равно она запускается только несколько раз в день. Стала быстро работать каждый раз.

Но всё равно непонятно: что же заставляет SQL Server использовать неправильный план запроса? Неужели таки глюк в самом SQL Server (у нас 2008 R2)? Современные сервера баз данных очень-очень надежны, но всё же изредка бывает что-то такое? Была мысль, что надо процедуру грохнуть и создать заново. Или даже создать под другим именем. Может, где-то в недрах SQL Server напротив этой процедуры записан какой-то мусор?

Но оказалось, что, перефразируя Остапа Бендера, надо верить базам данных. Все дело было во временных таблицах и в старом, казалось бы, безобидном глюке в нашей системе. Перед запуском процедуры эти большие таблицы полностью очищаются. Потом заполняются свежими исходными данными. Запускается процедура. Ну, это как бы нормально - статистика должна автоматически пересчитаться.

Но процедура первый раз запускается с неправильными параметрами и ничего не возвращает. Мы об этой ошибке знаем, и вроде как ничего страшного в ней нет. Но оказалось, что именно вот этот первый запуск сбивает SQL Server с толку: он замечает, что ничего из временных таблиц не вытащить не удалось, и перестраивает план запроса в расчете на то, что так будет всегда. Если бы этот неправильный вызов был не первым, а вторым в списке после очистки временных таблиц, то всё было бы нормально...


Lessons learned:

Даже мелкие и, казалось бы, безобидные ошибки лучше по возможности исправлять. Иногда может вылезти в совершенно неожиданном месте...

То, что система сейчас "вроде как нормально работает", не всегда означает, что можно просто так внести в неё изменения. Может, она работает как раз на пределе возможностей.

Ну, и ещё раз убедился: если кажется, что нашел ошибку в SQL Server или Oracle, то на 99% это на самом деле "проблема в ручках".