Sql server не освобождает память
каков хороший способ проверить, сколько (фактической) памяти в настоящее время используется против того, сколько SQL Server выделяется для себя?
я прибегал к memory_utilization_percentage но это, похоже, не меняется после запуска следующего, чтобы освободить память.
решение-удалить максимальная память сервера для SQL Server и увеличить его снова, чтобы заставить SQL Server освободить неиспользуемые, но выделенная память. Однако проблема с этим подходом заключается в том, что мы не можем быть уверены, насколько уменьшить максимальная память сервера, следовательно, рискуют убить SQL Server. Вот почему важно понять, сколько SQL Server "фактически" использует, прежде чем уменьшать значение для максимальная память сервера.
SQL Server всегда предполагает, что это основное запущенное приложение. Он не предназначен для совместного использования ресурсов. Он всегда будет занимать всю доступную память, и он будет выпускать ее только для операционной системы, если вы не дросселируете "max server memory".
по дизайну Sql Server не играет хорошо с другими.
в этой статье sqlskills рекомендует базовый уровень для дросселирования с последующим мониторингом и повышением дроссельной заслонки как нужны:
У меня нет решения для того, чтобы освободить выделенную память. Однако для наших целей мы смогли выяснить, как позволить активно-активным кластерам безопасно работать. Мы решили установить минимальная память сервера до ~2 ГБ. Это полезно, потому что независимо от того, сколько max памяти экземпляр решает использовать, он никогда не будет запускать другие экземпляры из памяти. Опять же, это решает нашу цель, но все равно не отвечает на вопрос о том, сколько памяти фактически используется, как низко мы можем отбросить максимальную память сервера и т. д.
вы должны установить "Max Server memory" на некоторое значение между 1-2 ГБ. Этот диапазон безопасен в большинстве случаев. Это может занять некоторое время, чтобы освободить память после выполнения ниже:
Эта настройка позволяет очистить пул, скомпилировать память, все кэши, память среды clr и т. д.
минимальное значение для "max Server memory" - 128 МБ, но это не рекомендуется, так как SQL Server может не запускаться в определенных конфигурациях. Если это произойдет, используйте переключатель"- f", чтобы заставить SQL начать с минимальная конфигурация, затем измените значение на исходное.
этот пост решается по следующей ссылке, пожалуйста, проверьте формат:
Я не думаю, что SQL Server освобождает память, если операционная система активно не запрашивает ее. Если есть случай других процессов, требующих больше памяти, и если нет вообще, SQL Server освободит неиспользуемую память самостоятельно. А не пытаться промыть unusued памяти, я бы, наверное, пойти с ограничением по SQL позволило памяти.
SQL Server In-Memory OLTP использует больше памяти, чем SQL Server, и делает это по-другому. Возможно, что объем памяти, установленный и выделенный для Выполняющаяся в памяти OLTP , станет недостаточным для растущих потребностей. В таком случае может возникнуть нехватка памяти. В этом разделе описывается восстановление из ситуации с нехваткой памяти. В статье Наблюдение и устранение неисправностей при использовании памяти вы найдете рекомендации, которые помогут вам избежать многих ситуаций нехватки памяти.
Настройка максимального размера памяти для SQL Server
По умолчанию экземпляр SQL Server может со временем использовать большую часть памяти, доступной операционной системе Windows на сервере. После занятия памяти она не высвобождается, пока не будет обнаружена нехватка памяти. Такое поведение является нормальным и не свидетельствует об утечке памяти в процессе SQL Server. Чтобы ограничить объем памяти, который сервер SQL Server может использовать в своих целях, задайте параметр max server memory. Дополнительные сведения см. в статье Руководство по архитектуре управления памятью.
В SQL Server на Linux установить ограничение памяти можно с помощью средства mssql-conf и параметра memory.memorylimitmb.
PSSDIAG или SQL LogScout
Существует и другой, автоматический способ записи подобных точек данных — это такие инструменты, как PSSDIAG и SQL LogScout.
- Для PSSDIAG настройте сборщики данных Perfmon (Системный монитор) и Custom Diagnostics\SQL Memory Error (Настраиваемая диагностика\Ошибка памяти SQL).
- Для SQL LogScout настройте сбор данных по сценарию Memory (Память).
В следующих разделах приведены более подробные указания для каждого сценария (нехватка памяти по внешним или внутренним причинам).
Определение текущего объема памяти, используемого сервером SQL Server
Приведенный ниже запрос возвращает сведения о текущем использовании памяти сервером SQL Server.
Рекомендуемые действия
Если ошибка 701 возникает лишь иногда или длится короткое время, возможно, речь идет о краткосрочной проблеме с памятью, которая решается сама собой. В таких случаях предпринимать какие-либо действия может быть не нужно. Однако если эта ошибка возникает многократно, на разных подключениях и длится несколько секунд или дольше, для ее устранения действуйте предложенным ниже образом.
Далее перечислены основные шаги, которые помогут в устранении ошибок памяти.
устранить ошибки восстановления базы данных, возникающие из-за нехватки памяти
Сервер, на который восстанавливается база данных, должен иметь достаточно памяти для оптимизированных для памяти таблиц в резервной копии базы данных, в противном случае база данных не подключится к сети и будет помечена как подозрительная.
Если сервер имеет достаточный объем физической памяти, но по-прежнему возникает данная ошибка, возможно, что другие процессы используют слишком много памяти или операция восстановления не получает достаточный объем памяти из-за проблем с конфигурацией. При подобных проблемах воспользуйтесь следующими мерами, чтобы предоставить операции восстановления дополнительную память.
Временно закройте выполняющиеся приложения.
Закрыв одно или несколько выполняющихся приложений или остановив ненужные на данный момент службы, можно освободить используемую ими память для операции восстановления. Эти приложения можно будет перезапустить после успешного завершения восстановления.
Увеличьте значение MAX_MEMORY_PERCENT.
Если база данных, как и рекомендуется, привязана к пулу ресурсов, то память, доступная для операции восстановления, регулируется параметром MAX_MEMORY_PERCENT. Если значение слишком мало, восстановление завершится со сбоем. В этом фрагменте кода значение параметра MAX_MEMORY_PERCENT для пула ресурсов PoolHk увеличивается до 70 % от установленной памяти.
Если сервер выполняется на ВМ и не выделен, установите такое же значение MIN_MEMORY_PERCENT, как и MAX_MEMORY_PERCENT.
Дополнительные сведения см. в статье Рекомендации по использованию выполняющейся в памяти OLTP в среде виртуальных машин.
Дополнительные сведения о максимальных значениях параметра MAX_MEMORY_PERCENT см в разделе Процент памяти, доступной для оптимизированных для памяти таблиц и индексов.
Увеличьте значение max server memory.
Дополнительные сведения о настройке параметра Макс. памяти сервера см. в разделе Параметры конфигурации сервера "Память сервера".
Примените действие по исправлению
Для устранения проблемы с нехваткой памяти необходимо либо освободить имеющуюся память путем сокращения объема ее использования, либо выделить дополнительный объем памяти таблицам в памяти.
Освобождение имеющейся памяти
Удаление неважных строк оптимизированных для памяти таблиц и ожидание выполнения сборки мусора
Можно удалить неважные строки из оптимизированной для памяти таблицы. Сборщик мусора делает объем памяти, используемый этими строками, доступным. Компонент In-memory OLTP выполняет сбор ненужных строк агрессивно. Однако долго выполняющаяся транзакция может помешать сбору мусора. Например, если имеется транзакция, которая выполняется в течение 5 минут, все версии строк, созданные из-за операций обновления или удаления во время выполнения транзакции, не подпадают под сборку мусора.
Переместить одну или несколько строк в таблице на диске
В следующих статьях TechNet представлены рекомендации по перемещению строк из таблиц, оптимизированных для памяти, в таблицы на диске.
Увеличение объема доступной памяти
Увеличение значения MAX_MEMORY_PERCENT для пула ресурсов
Если именованный пул ресурсов для таблиц в памяти еще не создан, то его необходимо создать и привязать к нему базы данных Выполняющаяся в памяти OLTP . Инструкции по созданию пула ресурсов и привязки к нему баз данных см. в разделе Привязка базы данных с таблицами, оптимизированными для памяти, к пулу ресурсов Выполняющаяся в памяти OLTP .
Если база данных Выполняющаяся в памяти OLTP привязана к пулу ресурсов, то пользователь может увеличить процент памяти, доступной для пула. Инструкции по изменению значения MIN_MEMORY_PERCENT и MAX_MEMORY_PERCENT для пула ресурсов см. в подразделе Изменение параметров MIN_MEMORY_PERCENT и MAX_MEMORY_PERCENT для существующего пула .
Увеличьте значение MAX_MEMORY_PERCENT.
В этом фрагменте кода значение параметра MAX_MEMORY_PERCENT для пула ресурсов PoolHk увеличивается до 70 % от установленной памяти.
Если сервер выполняется на ВМ и не выделен, установите такое же значение MIN_MEMORY_PERCENT, как и MAX_MEMORY_PERCENT.
Дополнительные сведения см. в статье Рекомендации по использованию выполняющейся в памяти OLTP в среде виртуальных машин.
Дополнительные сведения о максимальных значениях параметра MAX_MEMORY_PERCENT см в разделе Процент памяти, доступной для оптимизированных для памяти таблиц и индексов.
Установка дополнительной памяти
В конечном счете наилучшим решением является установка дополнительной памяти. Если выбран этот вариант, то необходимо учитывать, что, скорее всего, также можно будет увеличить значение MAX_MEMORY_PERCENT (см. подраздел Изменение параметров MIN_MEMORY_PERCENT и MAX_MEMORY_PERCENT для существующего пула), так как SQL Server вряд ли будет нужно больше памяти, а это позволит выделить большую часть или даже всю установленную новую память пулу ресурсов.
Если сервер выполняется на ВМ и не выделен, установите такое же значение MIN_MEMORY_PERCENT, как и MAX_MEMORY_PERCENT.
Дополнительные сведения см. в статье Рекомендации по использованию выполняющейся в памяти OLTP в среде виртуальных машин.
устранить влияния нехватки свободной памяти на рабочую нагрузку
Очевидно, проще всего вообще избегать ситуаций, связанных с нехваткой памяти. Помочь в этом может хорошее планирование и отслеживание. Однако даже самое хорошее планирование не гарантирует стабильной работы, и возникновение нехватки памяти всегда возможно. Для устранения этой ситуации необходимо выполнить два действия.
Внутреннее использование памяти ядром SQL Server: диагностика и решения
Начните сбор данных счетчиков Системного монитора SQL Server — Диспетчер буферов и SQL Server — Диспетчер памяти.
Запросите динамическое административное представление клерков памяти SQL Server несколько раз, чтобы узнать, где происходит наибольшее потребление памяти в ядре:
Если вы четко увидите клерк памяти, ответственный за избыточное ее потребление, сосредоточьтесь на особенностях потребления этого компонента. Вот несколько примеров:
- Если память потребляется клерком MEMORYCLERK_SQLQERESERVATIONS, определите, каким запросам выделяется большой временно предоставляемый буфер памяти, а затем оптимизируйте эти запросы с помощью индексов, перепишите их (например, удалите ORDER by) или используйте указания запроса.
- Если кэшируется большое число нерегламентированных планов запросов, то клерк памяти CACHESTORE_SQLCP будет использовать большой ее объем. Определите непараметризованные запросы, у которых планы не могут использоваться многократно, и параметризуйте эти запросы, преобразовав их в хранимые процедуры либо воспользовавшись sp_executesql или принудительной параметризацией.
- Если хранилище кэша планов объектов CACHESTORE_OBJCP потребляет много памяти, определите, какие хранимые процедуры, функции или триггеры используют большой объем памяти и, если возможно, спроектируйте приложение иным образом. Такое обычно происходит при больших объемах баз данных или схем с сотнями процедур в каждой.
- Если клерк памяти OBJECTSTORE_LOCK_MANAGER показывает большие объемы ее выделения, определите, какие запросы применяют множество блокировок, и оптимизируйте эти запросы с помощью индексов. Сократите транзакции, которые создают длительные блокировки на определенных уровнях изоляции, а также проверьте, не включено ли укрупнение блокировки.
Предварительное выделение памяти
В виртуальной среде важными факторами для памяти являются более высокая производительность и расширенная поддержка. Необходимо иметь возможность как быстро выделять память виртуальным машинам в зависимости от их требований (пиковые и низкие нагрузки), так и исключить бесполезные траты памяти. Компонент Hyper-V Dynamic Memory делает выделение памяти между виртуальными машинами, выполняемыми на узле, и управление ею более гибким.
Некоторые рекомендации по виртуализации и управлению SQL Server необходимо скорректировать при виртуализации базы данных с таблицами, оптимизированными для памяти. При отсутствии оптимизированных для памяти таблиц есть две рекомендации.
- При использовании параметра "Мин. памяти сервера" рекомендуется назначать только необходимое количество памяти, чтобы осталось достаточно памяти для других процессов (во избежание вытеснения).
- Не назначайте слишком высокого значения предварительного выделения памяти. В противном случае другие процессы могут не получить достаточной памяти к тому времени, когда она им потребуется, а это приведет к подкачке памяти.
Если следовать указанным выше рекомендациям, когда в базе данных есть оптимизированные для памяти таблицы, может выясниться, что попытка восстановить базу данных приводит к остановке базы данных в состоянии "Ожидание восстановления", даже если доступно достаточно памяти для восстановления базы данных. Причина в том, что при запуске выполняющаяся в памяти OLTP передает данные в память активнее, чем механизм выделения динамической памяти выделяет память базе данных.
Рекомендации по использованию выполняющейся в памяти OLTP в среде виртуальных машин
Виртуализация серверов позволяет не только снизить расходы на приобретение и эксплуатацию, но и добиться большей эффективности ИТ-процессов благодаря оптимизации подготовки, обслуживания, доступности и операций резервного копирования или восстановления приложений. В результате недавних успехов в развитии технологий стало проще консолидировать сложные рабочие нагрузки базы данных с применением виртуализации. В этой статье приведены рекомендации по использованию выполняющейся в памяти OLTP для SQL Server в виртуализированной среде.
Внутреннее использование памяти независимо от SQL Server
Нехватка памяти может быть вызвана факторами внутри процесса SQL Server. В процессе SQL Server могут использоваться компоненты, являющиеся "внешними" по отношению к ядру SQL Server. Это могут быть DLL-библиотеки, например связанные серверы, компоненты SQLCLR, расширенные процедуры (XP) и OLE Automation ( sp_OA* ). Сюда также относятся антивирусы и другие программы безопасности, которые внедряют DLL в процесс с целью мониторинга. При наличии проблем или плохой архитектуре эти компоненты могут вызвать существенное потребление памяти. К примеру, связанный сервер может кэшировать 20 миллионов строк данных, поступающих в память SQL Server из внешнего источника. Никакой клерк памяти в SQL Server не сообщит об использовании большого ее объема, хотя в процессе SQL Server происходит именно это. При росте объема памяти из-за DLL связанного сервера SQL Server начнет сокращать свое потребление памяти (см. выше) и ее станет недостаточно для компонентов внутри решения, из-за чего будут возникнут такие ошибки, как 701.
Темы данного раздела
Определение ожидаемого времени существования страницы
В приведенном ниже запросе с помощью представления sys.dm_os_performance_counters отслеживается текущее значение ожидаемого времени существования страницы для экземпляра SQL Server на уровне как всего диспетчера буферов, так и каждого узла NUMA.
Эта статья посвящена SQL Server. Сведения об устранении проблем нехватки памяти в Базе данных SQL Azure см. в статье Устранение ошибок нехватки памяти в Базе данных SQL Azure.
Наблюдение за памятью операционной системы
Для отслеживания нехватки памяти используйте приведенные ниже счетчики Windows. Значения многих счетчиков памяти операционной системы можно запрашивать с помощью динамических административных представлений sys.dm_os_process_memory и sys.dm_os_sys_memory.
Память: доступно байтов
Этот счетчик указывает на то, сколько байт памяти доступно на данный момент для использования процессами. Низкие значения счетчика Доступно байтов могут указывать на общую нехватку памяти операционной системы. Это значение можно запросить с помощью T-SQL из sys.dm_os_sys_memory.available_physical_memory_kb.
Память: ошибок страницы/с Этот счетчик показывает частоту ошибок страниц для всех процессов, включая системные. Низкий, но не нулевой уровень выгрузки на диск (и вызванные ею ошибки страниц) является нормальным, даже если у компьютера достаточно большое количество доступной памяти. Диспетчер виртуальной памяти (VMM) Microsoft Windows берет страницы из SQL Server и других процессов по мере того, как он урезает размеры рабочих множеств этих процессов. Деятельность VMM может привести к ошибкам страниц.
Процесс: ошибок страницы/с Этот счетчик показывает частоту ошибок страниц для определенного пользовательского процесса. С помощью счетчика Процесс: ошибок страниц/с можно определить, вызвана ли повышенная активность диска подкачкой, выполняемой сервером SQL Server. Чтобы определить, является ли SQL Server или другой процесс причиной излишней подкачки, наблюдайте за счетчиком Процесс: ошибок страниц/с для экземпляра процесса SQL Server.
Дополнительные сведения об устранении проблемы излишней подкачки см. в документации по операционной системе.
Использование памяти внешними компонентами или ОС
Какой-то внешний по отношению к процессу компонент может использовать большой объем памяти, из-за чего ее может не хватать для SQL Server. Необходимо выяснить, есть ли в системе другие потребляющие память приложения, которые вызывают ее нехватку. SQL Server — одно из немногих приложений, которое при запросе памяти со стороны ОС сокращает свое использование ресурсов. То есть, когда какое-то приложение или драйвер просит выделить себе память, ОС отправляет сигнал освободить память всем приложениям и SQL Server удовлетворяет этот запрос. Мало какие приложения действуют так же, поскольку они не предусматривают ответ на это уведомление. Таким образом, если SQL начинает сокращать свое использование памяти, его пул памяти уменьшается и нуждающиеся компоненты могут ее не получить. В результате начинает возникать ошибка 701 и другие ошибки, связанные с памятью. Дополнительные сведения см. в разделе Архитектура памяти SQL Server
Примеры
Внутреннее использование памяти независимо от SQL Server: диагностика и решения
Для диагностики потребления памяти модулями (DLL-библиотеками) внутри SQL Server используйте следующий подход.
Если SQL Server не* использует Блокировку страниц в памяти (API-интерфейс AWE), то большая часть памяти SQL Server отображается счетчиком Процесс — Байт исключительного пользования (экземпляр SQLServr ) в Системном мониторе. Общее использование памяти внутри SQL Server отражено счетчиком SQL Server — Диспетчер памяти — Общая память сервера (КБ) . Существенная разница между значениями счетчиков Процесс — Байт исключительного пользования и SQL Server — Диспетчер памяти — Общая память сервера (КБ) может с большой вероятностью возникать из-за DLL (связанного сервера, XP, SQLCLR и др.). Например, если счетчик Байт исключительного пользования показывает 300 ГБ, а Общая память сервера — 250 ГБ, то примерно 50 ГБ общей памяти процесса занято чем-то вне ядра SQL Server.
Если SQL Server использует блокировку страниц в памяти (API AWE), определить проблему будет сложнее, так как в Системном мониторе нет счетчиков для AWE, отслеживающих потребление памяти в отдельных процессах. Общее использование памяти внутри SQL Server отражено счетчиком SQL Server — Диспетчер памяти — Общая память сервера (КБ) . Значения счетчика Процесс — Байт исключительного пользования обычно в совокупности составляют от 300 МБ до 1–2 ГБ. Если счетчик Процесс — Байт исключительного пользования показывает существенно большее значение, то, скорее всего, эта разница возникает из-за DLL (связанного сервера, XP, SQLCLR и др.). Например, если счетчик Байт исключительного пользования показывает 4–5 ГБ и SQL Server использует блокировку страниц в памяти (AWE), тогда большая часть этих байт может использоваться чем-то вне ядра SQL Server. Такая методика является приблизительной.
Воспользуйтесь служебной программой tasklist для обнаружения DLL, загруженных в область SQL Server:
Вы также можете проверить загруженные модули (DLL) и посмотреть, есть ли там что-то непредусмотренное, с помощью следующего запроса:
Если вы подозреваете, что значительный объем памяти занят модулем связанного сервера, вы можете настроить для него внепроцессное выполнение, отключив параметр Допускать в ходе процесса. Дополнительные сведения см. в разделе Создание связанных серверов. Не все поставщики OLE DB для связанных серверов могут работать вне процесса. За более подробными сведениями обратитесь к производителю продукта.
В тех редких случаях, когда используются объекты OLE Automation ( sp_OA* ), вы можете настроить запуск объекта в процессе вне SQL Server, установив context = 4 (только для локального сервера OLE, т. е. EXE-файла). Дополнительные сведения: sp_OACreate.
Простые способы быстро освободить память
Чтобы освободить некоторый объем памяти для SQL Server, можно воспользоваться следующими приемами.
Проверьте следующие параметры конфигурации памяти SQL Server и попробуйте увеличить max server memory (максимальный объем памяти сервера), если это возможно:
max server memory
min server memory
Обратите внимание на нестандартные параметры. При необходимости измените их. Учтите, что требования к объему памяти возросли. Настройки по умолчанию приведены в статье Параметры конфигурации памяти сервера.
Если вы не настроили max server memory, особенно при использовании блокировки страниц в памяти, попробуйте задать конкретное значение, которое оставит некоторый объем памяти для ОС. См. параметр конфигурации сервера Блокировка страниц в памяти.
Проверьте рабочую нагрузку запросов (число параллельных сеансов и текущие выполняемые запросы) и посмотрите, имеются ли неприоритетные приложения, которые можно временно приостановить или перенести в другой экземпляр SQL Server.
Если вы запускаете SQL Server на виртуальной машине, убедитесь в отсутствии на ней избыточного выделения памяти. Некоторые принципы настройки памяти для виртуальных машин см. в статьях Virtualization – Overcommitting memory and how to detect it within the VM (Виртуализация — избыточное выделение памяти и обнаружение его на виртуальной машине) и Troubleshooting ESX/ESXi virtual machine performance issues (memory overcommitment) (Устранение неполадок с производительностью виртуальных машин ESX/ESXi (избыточное выделение памяти)).
Следующие команды DBCC позволяют освободить несколько кэшей памяти SQL Server.
Если вы используете Resource Governor, рекомендуем проверить параметры пула ресурсов или группы рабочей нагрузки и убедиться, что они не ограничивают память слишком сильно.
Если проблема сохраняется, потребуется дополнительный анализ и, возможно, увеличение серверных ресурсов (ОЗУ).
SQL Server сразу "отжимает" память системы столько, сколько ему необходимо (точнее сколько выделено в настройках).
Так что это вполне штатный режим работы, службу перезагружать не надо.
(2) А как мне узнать, например, что из 350 отжатых ГБ только например 50 содержит актуальные данные, а остальные 300 старые ненужные, которые он, я так понимаю, замещает на нужные при необходимости? И почему тогда явно замедляется работа 1С. Не хватает 350 ГБ выделенной памяти?
(0) Во-первый SQL Server память освобождает, но не сразу, а через некоторое время.
Во-вторых, нужно выяснить, из-за чего "1С начинает работать медленнее". Может быть, совсем не из-за нехватки памяти.
Сколько памяти возьмет 1С, если ей вдруг освободить? Не 350 Гб же.
(0) Нагрузку на диск смотрели до и после этих расчетов? Возможно, память SQL надо еще ограничить, т.к. её не остается на прочие нужды системы.
(8) (9)
Свободная память остается, за этим следим, даже rphost немного съедает. Основная нагрузка на CPU скуля и память, которую он съедает.
Для теста взял уменьшил лимит до 50 Гб и SQL потихоньку освободил память до 50 ГБ. Потом вернул назад 350 ГБ. Через час уже 80 Гб забрал, хотя расчеты крупные ночью происходят.
В общем делаю вывод, что не нужно пытаться освобождать эти 350 ГБ. Если только для проверки как быстро он назад их заберет и хоть какой-то проверки ее достаточности.
Если вы попробуете после своей фразы "SQL Server не освобождает занятую им память, хотя данные в ней уже давно не актуальны", ответить на вопрос "Что значит данные не актуальны", да ещё в свете SQL индексов, планов и статистики запросов,
то вот тут вы поймёте, что ответить на это очень сложно.
Так что оставьте SQL как есть.
Лучше поищите инфу на тему "Счётчики SQL сервера и их анализ", это будет полезнее.
вар1. смотреть РИБ, возможно и не нужно так много узлов на одной ЦБ. Попробовать её децентрализовать. создав промежуточные между ЦБ и ПБ.
вар2. Может слишком жадный запрос? убирать излишнюю детализацию, сколько раз видел - вытаскивают по 100 полей в отчет для какого нибудь аналитика, который именно сводно строит по 8 полям, а остальные 90 нужны для расшифровок.
вар3. Если такое количество ВТ нужно держать активными - может подумать чтобы вместо этих ВТ создать новые справочники/регистры?
Может у вас, из-за больших изменений после "алгоритма" планы запроса не актуальны. попробуйте после закрытия статистику обновить.
(0) А зачем вы вообще ожидаете что СГЛ вам чтото будет возращать? Ему выделили память, он ее в какой то момент всю занял. Дальше сам будет разбираться что и как лучше с ней делать.
Удаляет все неиспользуемые элементы из всех кэшей. Компонент Компонент SQL Server Database Engine заранее автоматически очищает неиспользуемые элементы кэша в фоновом режиме, освобождая память для текущих записей. Однако можно использовать эту команду, чтобы вручную удалить неиспользуемые записи из всех кэшей или из указанного кэша пула регулятора ресурсов.
(15) Скорее всего все проще. Во время тяжелого расчета вытесняются справочники и индексы из кэша, отсюда и "замедление".
то что я сказал в (13) - во первых для чего нужен такой тяжелый механизм расчета? обычно всегда можно сложный процесс разбить на более простые.
(25) Вот уж сказанул так сказанул.
Что-то не вижу по ссылке ничего про тупизну 1С-ников.
These wait types are almost 100% never a problem and so they are
-- filtered out to avoid them skewing the results. Click on the URL
-- for more information.
(39) So what do I do if one day one of these suddenly become a problem and take 20% of total time? I won't even see that. Instead, I'll see skewed results.
Tester, у нас были проблемы "внезапно начало тупить" или "конфликт блокировок на любом документе". Помогало уменьшить потом увеличить лимит памяти sql. Позже прописали в план обслуживания (раз в сутки, утром):
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'5000'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
Т.е. 2 задания, в одном (условно) 5000, во втором (через 5 минут) обратно на 358400.
Такой же эффект (для описанных проблем) дает очистка процедурного кеша сервера dbcc freeproccache (а лучше аналог DBCC FLUSHPROCINDB (DB_ID()) - только для одной базы).
Это конечно не правильное решение проблемы, но за не имением лучшего, обходимся тем что есть.
(46) изменение лимита памяти как раз и сбрасывает проц кэш. найти причину и устранить мозга конечно же нет
(4) страшное тут не то что скуль сожрал 400Г памяти, а в том что 95% ЦП.
по ходу ему её не хватает, бггг
привет погромистам местным передавай
(50) "95% ЦП. " - вот уж действительно, слона-то я и не приметил :)
(0) Степень параллелизма у вас какая? Поставьте 1.
(50) (51) И что тут страшного? Если все данные в кэше, запросы идут на ЦП.
И проблема, на которую жалуется ТС, начинается "после этого".
(52) "что тут страшного?" // Да это сисадминская примета: если в пике нагрузка на какой-нибудь ресурс сервера достигает 80% или выше, то пора увеличивать этот ресурс
А то когда в пике настанет *опа, тогда уже может поздняк оказаться увеличивать - и будут простои и все такое
(52) Чушь. Скорей всего нагрузка из-за постоянной перекомпиляции планов или латчей на временных таблицах. Чтобы не гадать, нужна диагностика.
Устранение ошибок выделения страниц, возникших из-за нехватки памяти при наличии достаточных ресурсов памяти
Для устранения этой ошибки необходимо включить регулятор ресурсов.
См. в разделе Включение регулятора ресурсов дополнительные сведения об ограничениях, а также рекомендации по включению регулятора ресурсов через обозреватель объектов, свойства регулятора ресурсов или Transact-SQL.
Внутреннее использование памяти компонентами SQL Server
К ошибке 701 также может приводить использование памяти внутренними компонентами ядра SQL Server. В SQL Server имеются сотни компонентов, которые отслеживают и которым выделяют память клерки памяти. Для устранения проблем необходимо определить, какие клерки памяти выделяют наибольший ее объем. Например, если вы обнаружите большой объем выделенной памяти у клерка OBJECTSTORE_LOCK_MANAGER, необходимо далее определить, почему диспетчер блокировки потребляет так много ресурсов. Возможно, какие-то запросы создают множество блокировок и их можно оптимизировать с помощью индексов, сокращения транзакций с длительными блокировками или отключив укрупнение блокировки. Каждый компонент и клерк памяти имеет свой уникальный способ доступа к памяти и ее использования. См. дополнительные сведения о типах клерков памяти и их описания.
Откройте выделенное административное соединение
SQL Server предоставляет выделенное административное соединение (DAC). С помощью выделенного административного соединения администратор может обращаться к запущенному экземпляру ядра СУБД SQL Server для устранения неполадок на сервере, даже если сервер не отвечает на другие клиентские соединения. DAC доступны в программе sqlcmd и в среде SQL Server Management Studio.
Рекомендации по использованию DAC в SSMS или sqlcmd см. в разделе Диагностическое подключение для администраторов баз данных.
Средства диагностики и сбор данных
Средства диагностики, позволяющие собирать данные для устранения неполадок, — это Системный монитор, sys.dm_os_memory_clerks и DBCC MEMORYSTATUS .
Настройте в Системном мониторе сбор данных с помощью следующих счетчиков:
- Память — Доступно МБ
- Процесс — Рабочий набор
- Процесс — Байт исключительного пользования
- SQL Server — Диспетчер памяти (все счетчики)
- SQL Server — Диспетчер буферов (все счетчики)
Собирайте периодические выходные данные следующего запроса на затронутом сервере SQL Server:
Изоляция памяти, используемой SQL Server
Для мониторинга использования памяти сервером SQL Server используйте приведенные ниже счетчики объектов SQL Server. Значения многих счетчиков объектов SQL Server можно запрашивать с помощью динамических административных представлений sys.dm_os_performance_counters и sys.dm_os_process_memory.
По умолчанию сервер SQL Server управляет требованиями к памяти динамически исходя из доступных ресурсов системы. Если SQL Server нужно больше памяти, он производит запрос к операционной системе, чтобы определить, доступна ли свободная физическая память, и использует ее. Если в операционной системе недостаточно свободной памяти, SQL Server будет освобождать память для операционной системы до тех пор, пока нехватка памяти не будет устранена или пока SQL Server не достигнет предела min server memory. Однако можно отказаться от динамического использования памяти, задав значения для параметров конфигурации сервера min server memory и max server memory. Дополнительные сведения см. в разделе Параметры памяти сервера.
Для мониторинга объема памяти, используемого SQL Server , наблюдайте за следующими счетчиками производительности.
SQL Server: Memory Manager: общая память сервера (КБ)
Этот счетчик показывает объем памяти операционной системы, выделенной в настоящее время серверу SQL Server диспетчером памяти SQL Server. Данное значение, как правило, увеличивается при повышении активности и растет после запуска SQL Server. Получить этот счетчик можно из столбца committed_kb динамического административного представления sys.dm_os_sys_info.
SQL Server: Memory Manager: память целевого сервера (КБ)
Этот счетчик показывает идеальный объем памяти для SQL Server в соответствии с рабочей нагрузкой за последнее время. Чтобы определить, выделен ли для сервера SQL Server оптимальный объем памяти, сравните это значение со счетчиком Общая память сервера по истечении некоторого периода работы со стандартной нагрузкой. Значения счетчиков Общая память сервера и Память целевого сервера должны быть примерно равны. Если значение Общая память сервера значительно меньше, чем значение Память целевого сервера, возможно, экземпляру SQL Server не хватает памяти. Через некоторое время после запуска SQL Server значение Общая память сервера возрастает. При этом значение Память целевого сервера должно быть больше, чем Общая память сервера. Получить этот счетчик можно из столбца committed_target_kb динамического административного представления sys.dm_os_sys_info. Дополнительные сведения и рекомендации по настройке памяти см. в статье Параметры конфигурации памяти сервера.
Процесс: рабочий набор
Этот счетчик показывает объем физической памяти, используемой процессом в настоящее время, согласно данным операционной системы. Обратите внимание на экземпляр этого счетчика для sqlservr.exe. Получить этот счетчик можно из столбца physical_memory_in_use_kb динамического административного представления sys.dm_os_process_memory.
Процесс: байт исключительного пользования
Этот счетчик показывает объем памяти операционной системы, запрошенный процессом для использования в собственных целях. Обратите внимание на экземпляр этого счетчика для sqlservr.exe. Так как этот счетчик учитывает все выделения памяти, запрошенные процессом sqlservr.exe, включая выходящие за пределы max server memory, его значение может превышать значение параметра max server memory.
SQL Server: Buffer Manager: страниц базы данных
Этот счетчик указывает число страниц с содержимым базы данных в буферном пуле. Память, не относящаяся к буферному пулу процесса SQL Server, не учитывается. Запросить этот счетчик можно из динамического административного представления sys.dm_os_performance_counters.
SQL Server: Buffer Manager: коэффициент попаданий в буферный кэш
Этот счетчик относится только к SQL Server. Желательно, чтобы коэффициент был не меньше 90. Значение выше 90 указывает на то, что более 90 процентов всех запрошенных данных были получены из кэша данных в памяти без считывания с диска. Дополнительные сведения о диспетчере буферов SQL Server см. в статье SQL Server, объект Buffer Manager. Запросить этот счетчик можно из динамического административного представления sys.dm_os_performance_counters.
SQL Server: Buffer Manager: ожидаемое время существования страницы
Этот счетчик измеряет, сколько секунд самая старая страница находится в буферном пуле. Для систем с архитектурой NUMA это среднее значение для всех узлов NUMA. Чем больше это значение, тем лучше. Его резкое падение указывает на постоянное обновление данных в буферном пуле, из-за которого рабочая нагрузка недостаточно эффективно использует данные, уже находящиеся в памяти. У каждого узла NUMA имеется собственный узел буферного пула. На серверах с несколькими узлами NUMA узнать ожидаемое время существования страницы для каждого узла буферного пула можно с помощью счетчика SQL Server: узел буфера: ожидаемое время существования страницы. Запросить этот счетчик можно из динамического административного представления sys.dm_os_performance_counters.
Пояснение
Ошибка 701 возникает, когда SQL Server не удается выделить достаточный объем памяти для выполнения запроса. Причиной нехватки памяти могут быть разные факторы, например параметры операционной системы, доступность физической памяти, использование памяти другими компонентами в SQL Server или ограничения памяти для текущей рабочей нагрузки. В большинстве случаев эта ошибка не возникает из-за сбоя транзакции. В целом, причины можно разделить на три группы.
Использование памяти внешними компонентами: диагностика и решения
Чтобы продиагностировать нехватку памяти в системе вне процесса SQL Server, используйте счетчики Системного монитора. Проверьте, имеются ли на текущем сервере потребляющие память приложения или службы помимо SQL Server, с помощью следующих счетчиков:
- Память — Доступно МБ
- Процесс — Рабочий набор
- Процесс — Байт исключительного пользования
Проверьте наличие ошибок, связанных с памятью (например, нехватку виртуальной памяти), в журнале системных событий.
Проверьте наличие ошибок памяти, связанных с приложениями, в журнале событий приложений.
Устраните все проблемы с кодом и конфигурацией для неприоритетных приложений и служб, чтобы уменьшить объем используемой ими памяти.
Если какие-то приложения помимо SQL Server потребляют ресурсы, попробуйте остановить эти приложения, изменить расписание их работы или запустить их на другом сервере. Это поможет снизить внешнюю нагрузку на память.
Решение
Чтобы смягчить этот эффект, заранее выделите достаточную память, чтобы восстановить или перезапустить базу данных, а не минимальное значение, в расчете на то, что динамическая память выделит дополнительную память при необходимости.
Проводите периодический мониторинг экземпляра SQL Server для подтверждения того, что память используется в допустимых пределах.
Определение текущего распределения памяти
Приведенные ниже запросы возвращают сведения о текущем распределении памяти.
Читайте также: