Производительность SQL Server снижается, когда текст запроса имеет определенное количество символов
Я настраиваю веб-сайт на новых серверах приложений и баз данных. Данные сервера:
- Стандарт Windows 2016
- SQL Server 2017 (окончательная первоначальная версия) — 14.0.1000.169 (X64)
- 16 ГБ ОЗУ на SQL и 8 ГБ на сервере приложений
- 4 виртуальных ЦП на SQL и 1 на сервере приложений
- ВМ, работающие на локальных серверах (т. е. не в облаке)
- Протокол TCP/IP настроен для SQL Server, именованные каналы отключены
Веб-страницы работают медленно, поэтому я попытался найти причину. В итоге я нашел и не могу объяснить, что если у меня есть простой запрос, например. (у этого запроса есть проблема, но я не запускаю его на веб-сайте):
SELECT 1
Запрос сам по себе не вызывает проблем, но если я дополню его пробелами, чтобы текст запроса составлял 676 символов или более (до 675 символов работает нормально), выполнение волшебным образом увеличится на 500 мс. Если я продолжаю добавлять пробелы примерно к 1500 символам, производительность в основном снижается со случайным повышением здесь и там. При добавлении дополнительных пробелов примерно к 2000 символов запрос снова становится стабильно быстрым.
С запуском запроса на самом SQL сервере проблем нет, только при удаленном запуске. Я пробовал простой сценарий PowerShell с использованием SqlCommand на сервере приложений и пробовал SQL Server Management Studio на другом компьютере, оба они медленные. SQL Profiler показывает, что запросы выполняются мгновенно, длительность равна 0 (процессор, операции чтения и записи также равны 0).
Вот несколько примеров выполнения, это взято из статистики клиента SQL Management Studio с более медленными экземплярами, где размер запроса превышает 675, и более быстрым экземпляром ниже 676.
Client processing time 0 0 0 15 0
Total execution time 15 62 531 546 531
Wait time on server replies 15 62 531 531 531
Возвращаясь к веб-сайту, я не запускаю «SELECT 1» и не дополняю запросы пробелами в коде веб-сайта. Фактические запросы, выполняемые с веб-сайта, в основном из Entity Framework, и из-за того, как эти запросы построены, столбцы, перечисленные в предложении select, некоторые запросы имеют соединения и предложения where, все это приводит к тому, что длина текста запроса достигает это волшебное ограничение в 676 символов, и запрос выполняется медленно. Вот актуальный запрос с сайта:
exec sp_executesql N'SELECT
[Extent1].[Id] AS [Id],
[Extent1].[ID] AS [Id1],
[Extent1].[Name] AS [Name],
[Extent1].[Code] AS [Code],
[Extent1].[DisplayOrder] AS [DisplayOrder],
[Extent1].[ScreenTypeId] AS [ScreenTypeId],
[Extent1].[Exclude] AS [Exclude],
[Extent1].[Author] AS [Author],
[Extent1].[Editor] AS [Editor],
[Extent1].[Created] AS [Created],
[Extent1].[Modified] AS [Modified],
[Extent2].[Id] AS [Id2],
[Extent2].[Name] AS [Name1],
[Extent2].[ScreenUrl] AS [ScreenUrl],
[Extent2].[Author] AS [Author1],
[Extent2].[Editor] AS [Editor1],
[Extent2].[Created] AS [Created1],
[Extent2].[Modified] AS [Modified1]
FROM [dbo].[ProfitCentre] AS [Extent1]
INNER JOIN [dbo].[ScreenType] AS [Extent2] ON [Extent1].[ScreenTypeId] = [Extent2].[Id]
WHERE [Extent1].[ScreenTypeId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=16
Почему запросы выполняются так долго при изменении длины текста запроса и как это исправить?
Похоже, ваш запрос чувствителен к анализу параметров. Когда вы меняете текст, добавляя пробелы, вы обходите кеш плана, и запрос компилируется для вашего конкретного набора параметров, поэтому он, вероятно, будет оптимальным для этого набора параметров.
Попробуйте запустить оператор с параметром перекомпиляции и посмотрите, наблюдаете ли вы поведение по-прежнему. Как правило, в фоновом режиме для запроса генерируется HASH, и если HASH нового запроса отличается от существующих, создается новый план, в противном случае, если уже существует HASH, используется существующий план. SELECT 1 OPTION(RECOMPILE)
Другой способ вместо option(recompile), который каждый раз перекомпилирует запрос, — использовать option(optimize for unknown). Некоторое объяснение здесь.
Я попытался очистить кеш плана, я попытался очистить кеш, запустить запрос самостоятельно и очистить кеш, а затем запустить с пробелами. То же странное поведение. Также пробовал с обоими вариантами, перекомпилировал и оптимизировал для неизвестного, по-прежнему работает медленно с пробелами и быстро без. Будет ли сниффинг параметров по-прежнему играть роль даже для чего-то простого, например «выбрать 1», поскольку параметров как таковых нет? Мы подняли характеристики сервера БД до смехотворного уровня, 128 ГБ ОЗУ и 32 виртуальных процессора, просто чтобы быть уверенным... нет, все еще занимает 500 мс.
Я взял случайный запрос (отличный от тех, что в OP), в Management Studio он быстрый, а из PowerShell - медленный, работающий с той же удаленной машины. У меня есть SQL Profiler, работающий с XML-событием Showplan, и я сравнил два плана, зависая над каждой операцией, я сравнил два плана, абсолютно одинаковые. Я добавил комментарий с несколькими сотнями символов (цифры 1..0 повторяются). Теперь это быстро в обоих. Я знаю, что это попахивает обнюхиванием параметров, но разве это не означает разные планы запросов?
Привет еще раз, я смотрю на Список версий SQL Server и вижу, что есть CU15 обновление. До первого CU или SP в версии всегда могут присутствовать какие-то странные причуды. Можно ли попробовать установить CU и повторить попытку?
Я установил все последние обновления Windows и CU15, проблема осталась.
@davidp_1978 Не забывайте, что если вы хотите обратиться к кому-то в комментариях, добавьте текст @ плюс имя пользователя (см., например, начало этого комментария). Вы пытались из SSMS включить фактический план выполнения и сравнить два (тот, где он быстрый, и тот, где он медленный)?
@ТТ. В XML-плане в медленном режиме CompileTime и CompileCPU равны 14 и 1, тогда как в быстром они оба равны 0. Все остальное, включая хэш запроса и хэш плана, одинаковы.
@ davidp_1978 Я понятия не имею, как работает EF, я все еще думаю здесь. Я бы попытался создать хранимую процедуру, которая возвращает точно такой же набор результатов с тем же параметром, что и в опубликованном вами запросе, но в хранимой процедуре добавьте option(optimize for unknown) к запросу. Затем в своем приложении вызовите хранимую процедуру, а не выполняйте запрос напрямую. Не знаю, возможно ли это вообще для вас?
@ТТ. Я создал хранимую процедуру, пробовал с «опцией» и без нее. Я не вызывал его из кода, а просто из Management Studio на другом компьютере. Аналогичное поведение в том, что он работает быстрее из-за меньшего количества символов, но добавление комментария замедляет его. Таким образом, один из способов повысить производительность — использовать хранимые процедуры, но только потому, что это уменьшает количество символов. Раздражает, но возможно, я оставлю это в крайнем случае. Я сделал запрос, чтобы другие посмотрели на сервер, ожидая их ответа...
Ответы 1
В данном случае проблема связана с тем, как виртуальные машины были настроены в среде хоста. Я не имею дело с инфраструктурой, поэтому я не уверен в конкретных деталях, но специалисты по инфраструктуре переместили виртуальные машины в соответствующий кластер, и это решило проблему производительности.
Рад, что вы получили ответ. Я собирался предположить, что это сетевой пакет/блок данных, связанный между клиентом и SQL Server. Более длинный текст может перевернуть его, поэтому для отправки запроса требуется 2 или более пакетов. Любые ошибки или проблемы в маршрутизации сделают его чувствительным к размеру пакета.
Замедление из-за отправки дополнительных пакетов имеет большой смысл! Что-то еще, что я мог бы попробовать, это пара запросов одинакового размера, возвращающих разные объемы данных, например. «Где Deleted = 1» по сравнению с «Где Deleted = 0», одинаковый размер запроса, но может возвращать совершенно разные результаты.
возможно, это связано с кешем планов. используйте
DBCC FREEPROCCACHE;для удаления всего плана и снова запустите запрос. для получения дополнительной информации см. docs.microsoft.com/en-us/sql/t-sql/database-console-commands/…