Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Сервер выделяет процессам слишком мало памяти  [new]
gpav
Member

Откуда:
Сообщений: 18
Доброго времени суток!
Далек от DBA, но приходится разбираться. Поиском пока не осилил.

Итак, переехали недавно с сервера SQL 2005 на 2016 Standard Edition (64-bit) на Windows Server 2016. Есть несколько тяжелых периодически выполняемых запросов (со времен 2005), которые нормально работали до и после переезда. Через пару месяцев после переезда запросы стали неожиданно зависать до бесконечности. Т.е. выполняются по нескольку часов без ошибок, пока их не убиваем. Раньше в пределах минуты или нескольких минут выполнялись. Легкие запросы продолжают нормально работать. Некоторое время помогала перезагрузка сервера. Сейчас - нет.

Пытаясь понять причину зависаний, пока проверил/сделал следующее (2016-й SQL):
1. SQL Server отжирает немного ресурсов у ОС. Task Manager показывает использование памяти 39% и проца 17% всеми процессами ОС суммарно.
2. Параметры управления памятью SQL Server:
min server memory = 0 MB
max server memory = 52000 MB
Это нормально, насколько понимаю, т.к. ОЗУ ОС = 64 GB.
Есть еще пара второстепенных (думаю) параметров:
память для создания индекса = 0
минимальный объем памяти для запроса = 1024 KB
3. Когда все висит, смотрю Активитити монитор и sys.dm_exec_sessions. И тут видно, что то сервер выделяет на запросы очень мало памяти, а именно:
max(memory_usage) = 4 (32 KB?)
sum(memory_usage) = 1073 (8584 KB?)
При этом доступной памяти у SQL Server ~22 GB (dm_os_process_memory.physical_memory_in_use_kb/1024 = ~22000), т.е. гораздо больше, чем съедают процессы.
4. Почистил процедурный кэш, но не помогло. Команды:
DBCC FREEPROCCACHE
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE

Где еще искать проблему? Или что пробовать делать (авось, поможет)?

P.S. Еще пару моментов:
1. SQL сервер запущен на локальной виртуалке. ОЗУ ОС = 64 GB вижу по RDP. Это ведь память вируталки?
2. Настроена репликация. Она продолжает нормально работать, данные на реплике обновляются.
1 фев 18, 18:15    [21158586]     Ответить | Цитировать Сообщить модератору
 Re: Сервер выделяет процессам слишком мало памяти  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Все таки стоит посмотреть на планы выполнения проблемых запросов. Обновить статистику в таблицах, если это требуется. Помочь оптимизатору получить верную кардинальность и соотвественно точнее сделать грант памяти для того или иного запроса
1 фев 18, 18:20    [21158601]     Ответить | Цитировать Сообщить модератору
 Re: Сервер выделяет процессам слишком мало памяти  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36970
Вы бы лучше написали, когда вы последний раз статистику обновляли.
1 фев 18, 18:22    [21158606]     Ответить | Цитировать Сообщить модератору
 Re: Сервер выделяет процессам слишком мало памяти  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Возможна ситуация когда SQL Server сильно недооуенил кол-во строк и в зависимости от удачи. Запрос скатится в NestedLoops и будет убиваться проц либо пойдут спилы в tempdb. Опять же лучше всего посмотреть на планы выполнения, чтобы понять проблему
1 фев 18, 18:23    [21158608]     Ответить | Цитировать Сообщить модератору
 Re: Сервер выделяет процессам слишком мало памяти  [new]
gpav
Member

Откуда:
Сообщений: 18
AlanDenton,
Гавриленко Сергей Алексеевич,

Явно обновление статистики никто не делает. Глянул сейчас в dm_db_stats_properties статистику по самым крупным таблицам с множеством индексов. По всем обновление статистики было сегодня, не более 5 часов назад.

Запросы кривые, конечно. Переписываем их потихоньку. С индексами далеко не все в порядке.

Рекомендации? Может, статистику по всей базе грохнуть и создать заново?
1 фев 18, 19:42    [21158803]     Ответить | Цитировать Сообщить модератору
 Re: Сервер выделяет процессам слишком мало памяти  [new]
Добрый Э - Эх
Guest
gpav,

как именно переезжали? может в процессе переезда "потерялись" планы обслуживания базы (дефрагментация индексов, пересчет статистики, перенос неактуальных данных в архивные таблицы)?
Попробуйте принудительно пересобрать статистику (хотя бы по таблицам из проблемных запросов) с опцией FULLSCAN. Возможно, автоматический сбор статистики делает недостаточно аккуратный и точный расчет...
1 фев 18, 19:52    [21158828]     Ответить | Цитировать Сообщить модератору
 Re: Сервер выделяет процессам слишком мало памяти  [new]
gpav
Member

Откуда:
Сообщений: 18
UPD
Нашел таблицы, по которым статистики не было, вообще!
Запустил EXEC sp_updatestats по всей базе без удаления имеющейся статистики.
1 фев 18, 19:54    [21158832]     Ответить | Цитировать Сообщить модератору
 Re: Сервер выделяет процессам слишком мало памяти  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
gpav,

Уровень совместимости БД поставьте минимальный 100-110 (не помню, какой там минимальный в 2016), либо включите опцию LEGACY_CARDINALITY_ESTIMATION. Этим вы исключите проблемы производительности из-за нового механизма оценки. Далее, включаете Query Store, копите статистику, потом переходите на последний уровень совместимости. После этого, смотрите регрессировавшие планы и решаете, как действовать дальше.

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

Но мне почему-то кажется, что проблема у вас в том, что я обозначил первым пунктом. Если нет - нужно обсуждать предметно и подробно.
1 фев 18, 20:01    [21158843]     Ответить | Цитировать Сообщить модератору
 Re: Сервер выделяет процессам слишком мало памяти  [new]
gpav
Member

Откуда:
Сообщений: 18
EXEC sp_updatestats (без аргументов) отработала за 8 минут. Про таблицы без статистики в предыдущем посте я наврал. Это были таблицы, в которых потерлась старая статистика в процессе пересборки сейчас. Увы, ничего не изменилось, - запросы висят.

Добрый Э - Эх,
До переезда из обслуживания было только реиндексирование всех таблиц раз в сутки джобом. После переезда продолжаем делать реиндексацию в том же режиме. Но последняя реиндексация, сегодня ночью, не прошла. А проблемы начались вчера днем. Когда была последняя успешная реиндексация, не известно (трутся логи давностью более суток). Перестроить индексы первым делом?

SomewhereSomehow,
гляну уровни совместимости, спасибо!
1 фев 18, 20:30    [21158902]     Ответить | Цитировать Сообщить модератору
 Re: Сервер выделяет процессам слишком мало памяти  [new]
nvv
Member

Откуда:
Сообщений: 54
gpav,
из практики - минимальную память нужно обязательно ограничивать. SQL сервер не должен конкурировать с другими приложениями за ресурсы. Иначе лихо теряется буфер, который заполняется с большими тормозами. И уж точно не стоит на 2016 чистить кэши, даже кэши планов. В тяжелом продакшене минут 15-20 все будут жестко тупить. Чистятся отдельные планы.
Проверяйте время жизни страниц в буфере. При нехватке памяти - сделайте файл расширенного буфера на ССД. 2016 версия это позволяет.
Вы ничего не написали о размере базы. Может у вас терабайт >>? Тогда нужно думать хотя бы о 256Гб оперативы для СУБД. Видал я базы 600 Гб с 0-8Гб памяти ((
Что является узким местом по железу в момент торможения запросов? Может физические чтения есть? Может время доступа большое?
Или все же планы шалят?
Все нужно смотреть. Гадать не стоит.
3 фев 18, 20:02    [21163287]     Ответить | Цитировать Сообщить модератору
 Re: Сервер выделяет процессам слишком мало памяти  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
nvv
gpav,
из практики - минимальную память нужно обязательно ограничивать.
Лучше не трогать стандартные настройки, можно сделать серверу хуже. Память можно трогать, только если на сервере кроме сиквела ещё что то установлено.
3 фев 18, 22:36    [21163518]     Ответить | Цитировать Сообщить модератору
 Re: Сервер выделяет процессам слишком мало памяти  [new]
Диклевич Александр
Member

Откуда:
Сообщений: 610
gpav,

наблюдал подобное при переезде 2012 -> 2016.
В проблемных запросах случаем не используются CTE?
4 фев 18, 05:47    [21163862]     Ответить | Цитировать Сообщить модератору
 Re: Сервер выделяет процессам слишком мало памяти  [new]
nvv
Member

Откуда:
Сообщений: 54
alexeyvg,
Неееенене!!!! Это всегда приводит к проблемам. На любых тренингах, вебинарах, выступлениях по хайлоаду даётся вполне четкая по этим параметрам рекомендация.
Нижнюю желательно ограничивать и делать равной верхней. Верхняя рассчитывается: отдается часть памяти системе + 1/16 от занимаемой sql. (Говорю про памяти, информация с вебинаров, выступлений).
Другим приложениям на этой машине делать нечего, это плохая практика.
Если другие приложения все же есть - нужно подбирать верхнюю планку так чтобы конкуренции никогда не было. Начинается конкуренция - начинаются тормоза. И что интересно не в СУБД, а в приложениях и системе.
4 фев 18, 08:20    [21163887]     Ответить | Цитировать Сообщить модератору
 Re: Сервер выделяет процессам слишком мало памяти  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
nvv
Нижнюю желательно ограничивать и делать равной верхней

непонятно только, почему же сделали 2 границы
и про каждую еще написали,
когда именно ее надо менять.

надо оставить одну настройку и вперед!
вы уж не забудьте MS-овцам сообщить,
как они лоханулись, сделав все же 2 настройки
5 фев 18, 12:09    [21166395]     Ответить | Цитировать Сообщить модератору
 Re: Сервер выделяет процессам слишком мало памяти  [new]
gpav
Member

Откуда:
Сообщений: 18
Диклевич Александр
gpav,
наблюдал подобное при переезде 2012 -> 2016.
В проблемных запросах случаем не используются CTE?

Активно используются, причем в критически важном тормозном запросе!
Речь про SP, в которой табличная переменная заполняется из CTE. Число записей единовременно в переменной не более 5000. При этом кол-во полей приличное - от 120 до 130. Но сильно тяжелых полей нет - максимально varchar(1024). Запрос зависал намертво при заполнении табличной переменной (на 2005 сервере годами проблем не было в этом месте). Месяц назад (на 2016) в качестве эксперимента заменил табличную переменную на постоянную таблицу (которая чистится на старте SP). Это помогло на пару недель. Потом опять началось описанное в исходном посте. Теперь табличных переменных нет совсем в SP. Но CTE остались.

P.S. Когда писал исходный пост, не заметил, что реиндексация базы а плане обслуживания не выполнялась несколько дней. Т.е. выполнение падало на реиндексации. Займемся в ближайшее время этим вопросом (с правами что-то, думаю). А пока раз в сутки запускаю руками реиндексацию. И это помогает. Описанные сбои прекратились. Понаблюдаем еще...
5 фев 18, 20:28    [21168038]     Ответить | Цитировать Сообщить модератору
 Re: Сервер выделяет процессам слишком мало памяти  [new]
nvv
Member

Откуда:
Сообщений: 54
Yasha123,
вроде уважаемый человек на форуме, а такой неадекватный сарказм. Пересказал, то что слышал на выступлениях практиков крупных проектов и от MS-овцев в том числе. Вынужден поверить столь положительному опыту.
Ключевой момент конкуренция за ресурсы. Если что-то отжимает память у СУБД - это уже нехорошо.
А вообще идея интересная. Поставил один параметр и все, чего заморачиваться )))

Какое ваше мнение по поводу вилки памяти? Что нам дает, что у нас вилка будет не 150-200, а 50-200? Или тем более 0-200Гб ?
5 фев 18, 21:43    [21168193]     Ответить | Цитировать Сообщить модератору
 Re: Сервер выделяет процессам слишком мало памяти  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7757
автор
раз в сутки запускаю руками реиндексацию


Тем самым вы удаляете кешированные планы запросов.
6 фев 18, 12:01    [21169425]     Ответить | Цитировать Сообщить модератору
 Re: Сервер выделяет процессам слишком мало памяти  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7757
У Вас плохая архитектура и плохие запросы, как следствие.
6 фев 18, 12:02    [21169431]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить