Разгадка магических тормозов медленного выполнения запроса

Недавно я написал заметку, в которой описал магическое выполнение запроса, которое поставило меня в ступор http://www.flenov.info/blog/show/Magicheskaya-problema-proizvoditelynosti. В этой заметке я не раскрою все тайны тормозов, потому что я так и не могу понять, почему тогда простое добавление перехода на новую строку меняло план выполнения, а реальное изменение запроса типа добавления and 1=1 или другие модификации оставляли запрос медленным. Даже OPTION (recompile) не влияла. Именно символ новой строки менял план выполнения. Скажу только, что на следующий день этот трюк не работал и запрос оставался медленным даже после добавления новой строки.

Итак, краткая история. Если просто выполнять запрос в SQL Server Management Studio, то он выполняется быстро:

declare @OffSet int = 0
declare @PageSize int = 50
declare @appServerTimeStamp datetime = '2018-05-04 07:14:08.193'
declare @CurrentSessionUserId int =53792

SELECT 
...
...
	ОгромнаяТаблица1
	Join ОкромнаяТаблица2 on …. and userID = @CurrentSessionUserId
...
...
...
...
 
OFFSET @OffSet ROWS

Но .NET запускает параметризированные запросы через хранимую процедуру sp_executesql:

exec sp_executesql N'
SELECT 
...
...
	ОгромнаяТаблица1
	Join ОкромнаяТаблица2 on …. and userID = @CurrentSessionUserId
...
...
...
...
 
OFFSET @OffSet ROWS
FETCH NEXT @PageSize ROWS ONLY  OPTION(RECOMPILE)',
  N'@OffSet int,
  @PageSize int,
  @appServerTimeStamp 
  @CurrentSessionUserId int',
  @OffSet=0,@PageSize=50,
  @appServerTimeStamp='2018-05-04 07:14:08.193',
  @CurrentSessionUserId=53792'

Я весь запрос не могу приводить, и он реально займет наверно 5 листов в Word, но где-то в середине есть связь двух огромных таблиц (точнее это даже две вьюшки), которые фильтруются по ID пользователя. В зависимости от ID пользователя результат этой связки может возвращать 10 записей, а может 1,000. И когда мы выполняем процедуру sp_executesql, то SQL сервер не знает, какой пользователь будет в переменной и сколько он записей вернет, поэтому выбирает самый ужасный план выполнения и сохраняет его.

По какой-то случайности при наличии в конце пустой строки сервер выбирал другой план выполнения, более эффективный, но это уже больше случайность. Для меня немного странно такое поведение, ведь когда мы выполняем тот же запрос без sp_executesql, то тут тоже есть параметры и тоже нужно компилировать запрос и сохранять его в кэше, но этого видимо не происходит. Единственное объяснение - SQL Server видит эту переменную прямо в тексте SQL запроса и оценивает по ее значению. В случае с хранимой процедурой он не обращает внимание на переменную, потому что она не в тексте. Это единственное объяснение, которое я могу дать. 

Запрос был оптимизирован введением локальной переменной:

exec sp_executesql N'
declare @useidlocal int = @CurrentSessionUserId

SELECT 
...
...
	ОгромнаяТаблица1
	Join ОкромнаяТаблица2 on …. and userID = @useidlocal
...
...
...
...
 
OFFSET @OffSet ROWS
FETCH NEXT @PageSize ROWS ONLY  OPTION(RECOMPILE)',
  N'@OffSet int,
  @PageSize int,
  @appServerTimeStamp 
  @CurrentSessionUserId int',
  @OffSet=0,@PageSize=50,
  @appServerTimeStamp='2018-05-04 07:14:08.193',
  @CurrentSessionUserId=53792'

Я два дня гуглил, видел большое количество статей в стиле «какого черта sp_executesql медленнее» и видел много рекомендаций, как решить проблему, но ни одна из них не подходила. Только введением локальной переменной, которая используется в фильтрации огромных таблиц запрос заработал стабильно быстро. Видимо сервер по-разному обрабатывает переменные, которые поступают извне и из текста SQL. Вот это для меня не объяснимо.

---

Понравилось? Кликни Лайк, чтобы я знал, какой контент более интересен читателям. Заметку пока еще никто не лайкал и ты можешь быть первым

---