Доброго времени суток!
Далек от 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. Почистил процедурный кэш, но не помогло. Команды:


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

P.S. Еще пару моментов:
1. SQL сервер запущен на локальной виртуалке. ОЗУ ОС = 64 GB вижу по RDP. Это ведь память вируталки?
2. Настроена репликация. Она продолжает нормально работать, данные на реплике обновляются.

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

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

Рекомендации? Может, статистику по всей базе грохнуть и создать заново?

UPD
Нашел таблицы, по которым статистики не было, вообще!
Запустил EXEC sp_updatestats по всей базе без удаления имеющейся статистики.

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

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

SomewhereSomehow ,
гляну уровни совместимости, спасибо!

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

P.S. Когда писал исходный пост, не заметил, что реиндексация базы а плане обслуживания не выполнялась несколько дней. Т.е. выполнение падало на реиндексации. Займемся в ближайшее время этим вопросом (с правами что-то, думаю). А пока раз в сутки запускаю руками реиндексацию. И это помогает. Описанные сбои прекратились. Понаблюдаем еще...