Проблема с периодически долго выполняемыми запросами в MS SQL Server

Предисловие


Есть информационная система, которую я администрирую. Система состоит из следующих компонент:

1. База данных MS SQL Server
2. Серверное приложение
3. Клиентские приложения

Данные информационные системы установлены на нескольких объектах. Информационная система активно и круглосуточно используется одновременно от 2-х до 20-ти пользователей на каждом объекте. Поэтому нельзя выполнять регламентные работы все и сразу. Т е приходится дефрагментацию индексов размазывать на целый день, а не одним махом все нужные фрагментированные индексы дефрагментировать. Аналогично и с другими работами.

Автообновление статистики выставлено в свойствах самой базы данных. Также статистика обновляется по дефрагментированному индексу.

Проблема


Около года назад столкнулся со следующей проблемой:

Периодически все запросы выполнялись долго. Причем время торможений были случайными. Это происходило на каждом объекте в случайный день. Более того, когда стал анализировать как часто происходят торможения (профайлером), то удалось выяснить, что они происходят каждый день в случайное время. Просто пользователи не всегда обращают на это внимание, а воспринимают как единственную случайную задержку, и потом система опять быстро работает.

Путь к спасению


Были пересмотрены сами запросы, которые выполнялись долго. Но самое интересное, это то, что все запросы в случайное время выполнялись долго. Даже самые простые типа вытащить последнюю запись из таблицы в несколько тысяч строк.

Далее, были проведены следующие работы:

  1. Проанализированы журналы MS SQL Server и Windows Server – причину торможений не удалось найти
  2. Проанализированы индексы (фрагментация и т д) – добавлены недостающие и удалены неиспользуемые
  3. Проанализированы запросы – улучшены некоторые запросы
  4. Проанализированы задания в SQL Agent – не удалось задачи привязать к проблеме торможений
  5. Проанализированы задания в Планировщике заданий – не удалось задачи привязать к проблеме торможений
  6. Profiler тоже выдавал следствие, а не причину торможений.
  7. Проведена проверка на взаимоблокировки – не было выявлено долгих блокировок вообще

В итоге было потрачено более 3-х месяцев на безуспешные поиски причины периодических торможений. Однако, удалось выявить интересный факт – у всех запросов вырастал показатель ожидания Elapsed, а не сам показатель выполнения Worker. Что натолкнуло на то, что возможно что-то с дисками. Тоже их проверил — все нормально.

Решение


Удивительным было то, что случайным образом удалось установить, что когда запрос в приложении выполняется медленно, то в самом SSMS он выполняется быстро. Тогда для решения помогла следующая статья (она по крайней мере в последствии натолкнула на идею).

Из этой статьи процитирую следующий абзац:
На практике наиболее важной опцией SET является ARITHABORT, потому что значение по-умолчанию для этой опции отличается для приложений и для SQL Server Management Studio. Это объясняет, почему вы можете обнаружить медленный запрос в вашем приложении, и затем получить хорошую скорость, выполняя его в SSMS. Приложение использует план, который был построен для набора значений отличающегося от актуальных, правильных значений. Тогда как если вы запускаете запрос в SSMS, то вероятнее всего в кэше пока еще не имеется плана выполнения для ARITHABORT ON, и поэтому SQL Server построит план для ваших текущих значений.

Разница в выполнении была в параметре SET ARITHABORT. Для всех запросов, выполняемых в SSMS этот параметр включен, а для запросов из вне (из приложений) – выключен. И его нельзя включить даже простым запросом для приложений:

SET ARITHABORT ON;

После этого последовала безумная идея – в момент зависания очищать процедурный кэш: клик.

Для последующей ручной проверки перед запросом в SSMS необходимо писать:

SET ARITHABORT OFF;

Тогда запрос будет выполняться, как если бы он пришел из приложения. Когда запрос выполнялся долго, то я очищал процедурный кэш. И всегда это лечило. Т е до чистки процедурного кэша, запрос мог выполняться до 20-30 секунд, а после – 0 секунд.

После этого был поставлен еще один эксперимент – чистка всего процедурного кэша для всей базы данных каждый час через SQL Agent:

--очищаем кэш по id БД
DBCC FLUSHPROCINDB(@db_id);

После этого все запросы стали просто выполняться очень быстро (менее 0,05 сек.), были лишь единичные выбросы до 5-10 секунд выполнения, но пользователи уже зависаний не замечали. Более того, обновление статистики не улучшало результаты, поэтому я убрал обновление статистики.

После еще нескольких месяцев исследований удалось установить, что единичные зависания происходят, когда на самом сервере либо все съедает кэш, и свободной памяти ничего не остается или остается, но меньше 1 ГБ ОЗУ, либо служба MS SQL Server съедает всю выделенную ей оперативную память (через Диспетчер задач). Но второе происходило всего 2 раза за все исследование.

Дело в том, что в кэш записывается в буквальном смысле все, а вот освобождается кэш не всегда вовремя. Проблему с кэшем удалось решить с помощью программы EmptyStandbyList.exe.

Данное приложение настроил через Планировщик задач на выполнение 1 раз каждый час. После проделанных работ уже более полугода нет торможений по запросам на всех объектах.

Единственное, что осталось непонятным, так это редкие случаи, когда один запрос зависнет на 5-10 секунд 1 раз в месяц в случайный день и в случайное время. Всего было за полгода 4 таких случая и то на двух объектах, а не на всех. При этом служба MS SQL Server съедает на короткое время всю выделенную ей оперативную память.

Проделал шаги, описанные в статье, но данное решение не помогло.

В принципе можно было и не копать глубже, т к пользователи зависания не замечают и все работает, но если у кого есть какие-то соображения-поделитесь, буду признателен.

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

Источники


» РазДваТриЧетыреПятьШестьСемьВосемь
Поделиться публикацией

Комментарии 73

    +3
    Память экземпляру SQL пробовали «зажимать»?
      0
      Пробовал, но оптимальный вариант не искал-поверил на слово Майкрософту:
      https://blogs.technet.microsoft.com/sqlruteam/2014/02/09/173/
      Но думаю руки дорастут. Пока на всех серверах стоит от 3 до 4 ГБ ОЗУ под MS SQL Server, а на самих серверах ОЗУ от 6 до 16 ГБ.
      Ему хоть все 8 дай-все съест)
        0
        Мне тоже сразу пришло это в голову, сталкивался ещё лет 10ть назад, когда под 1С SQL 8.0 (или 8.1) выделили обычный стационарный компьютер.
          0
          На счет 1С. Мой друг, когда я с ним поделился этой инфой, использовал чистить процедурный кэш и кэш ОС. И тоже запросы перестали тупить. Сам он занимается администрированием и внедрением 1С с MS SQL Server
            0
            Да все 1С-ники (надеюсь) об этом знают как 2х2, у нас стандартные регламентные процедуры: переиндексация или дефрагментация, пересчет статистики и DBCC FREEPROCCACHE, раз в сутки обычно это делают.
              0
              На самом деле не все-не буду говорить, кто не знал)
              Но ведь учиться никогда не поздно)
          0
          Многие забывают про эту простую, но очень важную вещь.
            0
            Зажимать память SQL Server? Под SQL Server нужно выделять 90% физической памяти. На одном сервере нельзя совмещать SQL server с другим софтом
              0
              Ваши слова как бальзам на душу, но увы обычно там что-то еще-аля Касперский или доктор Веб и безопасников не переубедить.
              Кстати, а почему 90%? Откуда такой подсчет?
              Я ориентируюсь всегда вот поэтому рассчету
                0
                А почему именно 90%?
                Про «совмещать» никто не говорит, но самой ОС и туче её служб тоже нужно пространство для манёвра.
                  +1
                  Конечно же, значение max server memory SQL Server не обязательно должно равняться 90% от величины физической памяти, это не best practices. Значение 90% я обычно выставляю по умолчанию для новых серверов и уже в ходе эксплуатации это значение корректирую. Для каждого экземпляра SQL Server величина max server memory устанавливается индивидуально.
                    +1
                    https://www.brentozar.com/blitz/max-memory/
                    Отличная статья по теме.

                    Вообще всем крайне советую изучить этот сайт. Содержит уйму полезной инфы и скриптов
                      0
                      Выставил сегодня на одном сервере 8 гигов вместо 4 (всего 16 гигов оперативной памяти). После 4 гигов оперативной памяти, которую он преодолел за пару часов, запросы некоторые стали немного медленнее выполняться (ожидания стали больше времени выполнения). Вернул 4 ГБ макс для скуля-стало нормально. Вообще, странное поведение.
                        +1
                        Могу предположить, что у вас множество уникальных запросов (использование кеша нерентабельно). ИМХО, при увеличении общего объема памяти, увеличивается и объем кеша, что увеличивает время поиска планов выполнения.
                          0
                          Так и есть-если проанализировать запросы.
                          Запросы и хранимки все теже, просто для каждого набора параметров нецелесообразно использовать имеющиеся планы.
                            +1
                            А что если попробовать уменьшить максимальный размер системного кеша, через SetSystemFileCacheSize, а для MsSqlSrv снова поднять до 8ГБ?
                            Посмотреть текущее ограничения: http://stackoverflow.com/a/17875550/6143821
                            Вызвать SetSystemFileCacheSize: http://www.uwe-sieber.de/files/setsystemfilecachesize.zip
                            Пример, как установить ограничение в 1ГБ:
                            SetSystemFileCacheSize off 1024
                            


                              0
                              Пока такие эксперименты я ставить побоюсь в ближайшее время.
                              Сами можете поэкспериментировать и кинуть результаты?
                                +1
                                Забыл уточнить один момент, данный способ актуален для серверов до версии 2008 (включая R2). На новых вроде уже профиксили все.
                                Если найду машину с 2008 проверю и отпишусь.
                                  0
                                  У нас большинство на 2012 стоят. Есть два сервера на 2014 и один на 2005
            +3
            Дело в том, что в кэш записывается в буквальном смысле все, а вот освобождается кэш не всегда вовремя. Проблему с кэшем удалось решить с помощью программы EmptyStandbyList.exe.

            А с чего вы взяли, что это — проблема? SQL Server считает (вполне обоснованно), что доступ к данным в памяти быстрее, чем доступ к данным на диске, поэтому все «горячие» данные старается держать в памяти, используя для этого всю доступную ему память. Если памяти ему не хватает, он часть наиболее давно незапрашиваемых данных вытесняет из памяти и помещает туда свежезапрошенный набор. Частый вариант решения проблем с производительностью — это как раз добавление памяти на сервер, чтобы все «горячие» данные в ней помещались.

            Есть даже специальный счетчик для SQL Server'а — «SQLServer: Buffer Manager: Buffer cache hit ratio», показывающий, какой процент запросов может читать данные из кэша (чем выше — тем лучше).
              0
              Да это понятно. Просто экспериментами на нескольких объектах удалось повысить скорость именно запуская чистку кэша. Ведь утилита, которая описана в статье, не отнимает весь кэш, а только тот, что не используется. Т е после применения кэш уменьшается максимум на 70-80%, а иногда и вовсе на 50%. Проблема в том, что часто кэшируемые данные используются редко, а не как предполагается часто. Но конечно с кэшем вопрос открытый, т к 9 серверов у Заказчика и 3 рабочих компьютера-это еще не показатель, хотя и заставляет задуматься. У меня даже когда запускаешь 3 студии 2015 и кучу страниц в браузере без этой утилиты кэш растет как на дрожжах и съедает всю ОЗУ, но благодаря вызовам каждый час этой утилиты тормозов не происходит и ОЗУ всегда хватает ОС, т е нет коротких тормозов в системе и не нужно ее перезапускать.
              0
              Удивительным было то, что случайным образом удалось установить, что когда запрос в приложении выполняется медленно, то в самом SSMS он выполняется быстро.

              Это не очень удивительно, если читать документацию Microsoft SQL Server.
              В документации указано следующее:
              You should always set ARITHABORT to ON in your logon sessions. Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues.

              Кроме этого следующий абзац содержит предупреждение:
              The default ARITHABORT setting for SQL Server Management Studio is ON. Client applications setting ARITHABORT to OFF can receive different query plans making it difficult to troubleshoot poorly performing queries. That is, the same query can execute fast in management studio but slow in the application. When troubleshooting queries with Management Studio always match the client ARITHABORT setting.


              SET ARITHABORT
                0
                Признаюсь, что это узнал не сразу, а по мере появления проблемы.
                  0
                  Для того, чтобы установить для клиентского сессии ARITHABORT ON можно использовать скрипт
                  EXEC sp_configure 'user options', 64 ;  
                  GO  
                  RECONFIGURE ;  
                  GO  
                  

                  Configure the user options
                    0
                    Интересно. Проверяли работает ли как написано в документации?
                      +1
                      Да, проверял. Можете это сделать самостоятельно, выполнить код (ниже) до и после выполнения скрипта
                      SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
                      builder.DataSource = "localhost";
                      builder.IntegratedSecurity = true;
                      builder.InitialCatalog = "AdventureWorks2014";
                      builder.ApplicationName = "test";
                      
                      using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
                      using (SqlCommand command = new SqlCommand("select DB_NAME() + ':' + ' ARITHABORT ' + case when (@@OPTIONS & 64) = 64 then 'ON' else 'OFF' end", connection))
                      {
                          connection.Open();
                          command.CommandType = CommandType.Text;
                          object result = command.ExecuteScalar();
                          Console.WriteLine($"{result}");
                      }
                      
                        0
                        Большое спасибо)
                        Учту на будущее
                      +1
                      Вы так перезатрете остальные дефалтовые опции, т.к. тут битовая маска используется. Они, правда, по умолчанию в ноль выставлены, но если кто-то что-то уже менял — то этим скриптом вы изменения затрете. Надо бы написать универсально — получить значение, проверить значение этого бита, если выставлен в 0, выставить в 1 и записать.
                        0
                        Спасибо за уточнение. Нужно взять существующие опции из @@options
                          +1
                          Нет. Из sys.configurations — это аналог (для обратной совместимости) sp_configure.

                          Вот так получилось:

                          declare @value int;
                          
                          select	@value = cast(value as int)
                          from	sys.configurations 
                          where	name = 'user options';
                          
                          if (@value & 64 = 0)
                          	set @value += 64;
                          
                          exec sp_configure 'user options', @value
                          go
                          reconfigure
                          go
                            +2
                            точно. поторопился :( пока писал правильный скрипт…
                            declare @t table (name sysname, minimum int, maximum int, config_value int, run_value int);
                            insert into @t
                            exec sp_configure 'user options'
                            declare @config_value int
                            select @config_value = config_value from @t
                            set @config_value |= 64
                            select @config_value
                            exec sp_configure 'user options', @config_value
                            go
                            reconfigure
                            go
                            


                  0
                  Это называется «Магия» а не решение проблемы. Вы где-то полазили, что-то поменяли, это кажется помогло. В чём на самом деле была причина вы не разобрались и лечили симптомы.
                    0
                    Зачастую действительно приходится лечить симптомы, т к когда система интегрирована с разными другими системами, порой сложно каждый раз искать причину (параметризация запросов и прочее), поэтому выработал следующий алгоритм решения проблемы:
                    1) попытаться найти причину за разумное время, параллельно собирая симптомы и то, что может в будущем помочь: факты и их связь с тормозами
                    2) если не удается найти причину-пишется костыль, но п.1 продолжается
                    3) ищем параллельные (альтернативные) пути решения
                    Конечно, п.3 является своего рода тоже костылем, но когда у вас время ограничено и причин каждый раз может быть много, то иногда лучше решить костылем, а потом искать причину.
                    Если проблема в запросе-рано или поздно найдете причину и исправите запрос.
                    Однако, если причина вообще непонятна и этим занимались 3 специалиста достаточно долгое время, включая меня, то пришлось пойти именно такими шагами. Главное-пользователи довольны. Но да-причину установить не удалось. Зато удалось решить ее последствия. И опять же-все проверили, и память, и задачи агента и т д и т п. Просто заметил связь с тормозами и ОЗУ. Их было две:
                    1) когда кэш все съедал-это уже решено
                    2) когда скуль все съедал, иногда тормоза были, но необязательно. Т е если скуль съедает все-это еще не повод, что запросы будут медленными. Правда конечно непонятно, почему он нередко съедает всю выделенную ему память. В ИС нет сложных запросов, которые требуют столько памяти. И пользователей не более 20-ти одновременно работающих с системой.
                      0
                      Кстати, вот на счет кэша могу сказать-что поставьте себе эту утилиту каждый час и посмотрите на разницу-система меньше тупить будет. Так что возможно-это и была причина, но не единственная.
                    0
                    У меня продовый MS SQL Server перезагружается в ночь с субботы на воскресенье. Идеологически это не круто, зато очень помогает против вот такого вот.
                      0
                      Если система работает постоянно и ее постоянно используют, то перезапускать невозможно. Плюс ко всему система отвечает за безопасность движения судов-ее нельзя перезапускать. А если попробовать чистить процедурный кэш и кэш ОС? Попробуйте-может поможет. В любом случае поделитесь результатами-будет думаю всем интересно.
                        0
                        Вы хотите сказать что она у вас не кластеризована — сервер единственный? И при этом она отвечает за безопасность?
                          0
                          Кластеризованы, но не все. Есть резервные сервера-по крайней мере 1, макс-2.
                            +1
                            Ну вот кластеризованные можно перезагружать. А если критически важный сервер не кластеризован — то медленная работа, пожалуй, это не главный повод для беспокойства.
                            0
                            У заказчика бывает денег нет на два сервера)
                              +1
                              Как это знакомо, так же обращался на данную статью (которая указана первой в разделе решение), но мне помогло создание новых индексов и изменение некоторых запросов.
                                0
                                Да, индексы перелапатили-я еще об этом напишу. И это в разы улучшело положение, но опять же все запросы в случайное время просто в разы медленнее выполнялись, причем именно по ожиданию Elapsed.
                                Запросы тяжелые ловим-правим по мере их появления.
                        +1
                        >Автообновление статистики выставлено в свойствах самой базы данных
                        Синхронное или асинхронное?
                        Статистику ожиданий не пробовали смотреть?
                        Обновления ставили (SP, CU)?
                          0
                          Асинхронное стоит у двоих серверов-там 2014 версия, а у остальных (2005-2012)-синхронная. Ожидания были в разы больше выполнения до того, как стал процедурный кэш чистить. Стоят последние обновления, кроме 2014-там SP1.
                            0
                            Например вызов хранимых процедур занимал от 0 до 0,5 сек. И примерно Elapsed=Worker.
                            Когда шли тормоза, то Elapsed доходил до 10-30 сек., а Worker до 1 сек. Т е выполнение вырастало макс в 2 раза, а ожидания многократно. Профайлер показал лишь следствия-ничего отличающегося из запросов нет-что когда сервер нормально работает, что когда тормозил.
                          0
                          IMHO сбрасывать процедурный кэш — плохая практика. Поскольку это приводит к повторной компиляции процедур и построению нового плана, что является причиной временного снижения производительности запросов.
                          Иногда некоторые процедуры/запросы не имеют типичных параметров поэтому может оказаться, что рекомпиляция процедуры приводит к более эффективному плану выполнения (см. "parameter sniffing") в этом случае имеет смысл создать процедуру WITH RECOMPILE, что позволит при каждом запуске находить оптимальный план.
                          Нет серебряной пули, к каждому случаю необходимо подходить индивидуально.
                            0
                            Пробовали ставить на определенных хранимках такие параметры-стало только хуже, т е всегда тяжелые стали. Вообще говоря, есть гипотеза-зачем вообще планы строить на запрос, если почти никогда не знаешь какие параметры придут. Или план строится для одних параметров, но никто не гарантирует, что план будет эффективен для того же запроса с другими параметрами. Так что возможно, если вообще каждый раз новый план строить-это нагрузка на ЦП (но обычно он мощный), но зато эффективный план для каждого случая. Серебряной пули нет, но в моей практики это решило минимум 90% проблем (я сюда не беру проблемы, связанные с плохими запросами и т д, а оставшиеся-непонятно почему вдруг простой запрос или несложная хранимка начинает в 10-ки раз дольше ожидать). Вопрос-как частно нужно вообще кэш сбрасывать-тут нужно по обстоятельствам. Если для входных параметров каждый раз нужен свой план и запросы такие выполняются часто, то нужно смотреть на частоту таких запросов. А если выполняются редко такие запросы или их мало, то процедурный кэш можно чистить раз в сутки или вообще раз в неделю.
                              0
                              IMHO сбрасывать процедурный кэш — плохая практика. Поскольку это приводит к повторной компиляции процедур и построению нового плана, что является причиной временного снижения производительности запросов.

                              Вы проверяли так ли это? Это вызывает большую нагрузку на ЦП, но в общем лучше построить эффективный план для каждого запроса с новыми параметрами, чем быстро использовать неэффективный план.
                              Думаю наверняка есть СУБД, где планы не кэшируются, но здесь утверждать не буду. Слышал, что вроде Vertica не использует кэширование планов для повторного использования, а только для асинхронного доступа. Но здесь утверждать не буду-сам не читал.
                                0
                                Проверяли, это имено так. На проде применяется в экстренных случаях и роняет производительность в разы. Запуск сброса кэша бд по джобу не буду даже комментировать. Обычно — неверный план равен неэффективному запросу в хп или устаревшей статистике. Запрос надо переписать, если нужно использовать хинты unknown. Статистику обновить. В общем лучше использовать один раз построенный план. Выше вам про магию очень точно подметили.
                                  0
                                  Статистику обновлять пробовали. Проблема была в том, что долгие ожидания появляются даже у простых запросов. Я конечно полностью процедурный кэш не отнял у серверов, но чистка 1 раз в час существенно улучшило время отклика. Ни один сервер не упал при чистке процедурного кэша, равно как и очистке кэша ОС вообще.
                                    0
                                    Запрос надо переписать, если нужно использовать хинты unknown. Статистику обновить. В общем лучше использовать один раз построенный план. Выше вам про магию очень точно подметили.

                                    Мне так все время отвечали (мои коллеги) и не сдвинулись тогда мы с места еще несколько месяцев-пытаясь улучшать запросы и т д. В итоге-недовольны пользователи, и время съедается у администратора. Данное решение позволяет ловить действительно плохие запросы и серьезные проблемы, а с остальным данный подход сам разберется. И уж точно ничего не должно падать при очистке кэша.
                                      0
                                      Про несколько месяцев не очень понял, сколько хп у вас? Поставьте мониторинг и разбирайте проблемы. Улучшить надо только то что тормозит, остальное не надо трогать. Это 5-7 дней работы DBA. Вроде прописные вещи.

                                      Еще раз для ясности, если перегружаете продакт чтобы избавится от проблем или чистите кэш всей бд постоянно, признайтесь себе честно — вы не понимаете что происходит.
                                        0
                                        Про несколько месяцев не очень понял, сколько хп у вас? Поставьте мониторинг и разбирайте проблемы. Улучшить надо только то что тормозит, остальное не надо трогать. Это 5-7 дней работы DBA. Вроде прописные вещи.

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

                                        Еще раз для ясности, если перегружаете продакт чтобы избавится от проблем или чистите кэш всей бд постоянно, признайтесь себе честно — вы не понимаете что происходит.

                                        Перезагружать теперь не нужно, когда чистится периодически кэш всей ОС.
                                        И я понимаю что происходит-чистить кэш ОС нужно периодически, до этого не было чистки. Процедурный кэш тоже чистить надо, но и там и там нужно подобрать частоту чистки.
                                        Ниже написали, что еще сжатие файлов приводит к увеличению времени ожидания выполнения запросов-на заметку взял. Сегодня уже поправим на 2-х серверах. О результатах отпишу.
                                          0
                                          Перезагружать теперь не нужно, когда чистится периодически кэш всей ОС.
                                          И я понимаю что происходит-чистить кэш ОС нужно периодически, до этого не было чистки.

                                          можно пруф который подтвердит это утверждение?
                                          Кэш видимо SQL все-таки имелся ввиду?
                                            0
                                            Кэш видимо SQL все-таки имелся ввиду?

                                            Нет, именно кэш ОС.
                                            Т е вместо перезагрузок плановых достаточно чистить кэш.
                                            можно пруф который подтвердит это утверждение?

                                            Ссылки я давал-можно посмотреть и почитать. И сами можете попробовать на своем компьютере.
                                            Если Вы не чистите периодически кэш, ОС без перезагрузки начнет тормозить рано или поздно.
                                            И вместо перезапуска попробуйте почистить кэш ОС.
                                +2
                                Есть еще одна тема, приводящая к «случайным» зависаниям простых и в обычных условиях быстрых процедур — это расширение файлов БД. Например, по умолчанию БД создается в которой лог растет неограниченно прибавляя 10% к своему размеру. При размере лога в несколько Gb операция расширения лога может занять весьма существенное время. На производительность очень сильно влияет план резервного копирования БД, кроме этого часто к не очень хорошим последствиям приводит периодические операции shrink file/db
                                  +1
                                  Читал даже какие то статьи с рекомендации по регулярному шринку базы — это как раз явно плохой совет, почти любое автоматическое сжатие промышленной OLTP базы — это зло.
                                    0
                                    кроме этого часто к не очень хорошим последствиям приводит периодические операции shrink file/db

                                    Верно, а ведь у нас такое делается-как раз на тех, где редко, но скуль съедает всю ОЗУ ему отведенную.
                                    Вы сейчас очень сильно помогли в поиске еще одной причины.
                                    Поэкспериментирую-расскажу результаты.
                                      0
                                      А как тогда поступать с файлами, если не делать шринк? Оставлять? Пусть растут?
                                      У Вас как в производстве или на работе?
                                        +1
                                        Да, пусть растут. Если место периодически освобождается, то оно же потом будет занято растущими данными — без необходимости увеличивать размеры файлов.
                                        А шринк приводит к фрагментации (т.к. данные уплотняются в процессе — распихиваются по всем пустым страницам, которые находятся в разных местах файла) и к автоматическому росту файла, когда место в нем заканчивается при добавлении новых данных.
                                          0
                                          Спасибо за совет
                                          0
                                          Главное периодически делать transaction log backup, это позволяет повторно использовать уже выделенное место в логе, иначе он будет расти бесконечно. Периодичность зависит от количества, размера транзакций и ценности данных.
                                            0
                                            А если используется модель восстановления простая, нужно ли тогда делать эту инструкцию? У нас БД относительно небольшие-до 10 ГБ. Полные резервные копии делаются каждые 3-4 часа.
                                              +1
                                              При использовании Simple recovery model, полный бэкап автоматически освобождает используемое место в логе. Но само резервное копирование при Simple Recovery Model достаточно сильно нагружает подсистему ввода/вывода и чем дальше тем больше, базы данных обычно со временем растут, соответственно и копировать каждый раз приходится все большие объемы данных. Да и данных за 3-4 часа можно много потерять. Я сторонник использования Full Recovery Model, особенно если это БД для значимого/ключевого корпоративного приложения. Тогда можно себе позволить достаточно часто делать transaction log backup, а во времена минимальной нагрузки делать full database backup.
                                                0
                                                Спасибо за совет.
                                                Правда сразу такое изменение не удастся у нас ввести, но постепенно сделаем)
                                                  0
                                                  Согласно информации https://msdn.microsoft.com/ru-ru/library/ms177446(v=sql.110).aspx
                                                  получается, что нужно всю цепочку журналов восстанавливать. А если резервная копия хотя бы одна окажется поврежденной? Ведь нередко бывает, когда бэкап сделан, но он поврежден. Вы же не можете мониторить систему 24 часа в сутки. Ночью сделался поврежденный бэкап журнала транзакций, через некоторое время скажем сгорел сервер (абстрактно, но скажем молния ударила. Кстати, несмешно, реально такой случай был-в серверную (серверная находилась на вышке) шарахнула молния и сервер сгорел). И как восстанавливать? Я поэтому и не сторонник разностных копий, если размеры БД позволяют.
                                                    +1
                                                    Вы путаете разностные копии (differential) и бэкапы лога транзакций. От первых можно отказаться, если вы можете с комфортной для себя частотой делать полные бэкапы.
                                                    Ну и дифференциальные бэкапы не составляют цепочку — они всегда содержат все изменения от последнего полного бэкапа.
                                                    А вот бэкапы лога транзакций действительно восстанавливаются по цепочке, но они и делаются гораздо быстрее, и затрагивают только лог, т.е. основные файлы базы данных не читаются и влияние на производительность меньше, чем при полном бэкапе.
                                                    Поэтому я рекомендовал бы вам таки использовать FULL модель восстановления, при этом продолжать делать полные бэкапы с той же частотой, а бэкапы лога транзакций, например, каждые 15 минут.
                                                    В случае необходимости восстановления, если вы потеряете какой то из бэкапов лога транзакций, в худшем случае вы получите те же 4 часа потерянных данных, что и сейчас, а в лучшем — не потеряете ничего (если потеряны основные файлы базы — вы можете сделать последний бэкап лога транзакций вручную и включить его в цепочку восстановления) или 15 минут (если потерян лог транзакций).
                                                      0
                                                      Спасибо за совет-попробуем)
                                                    0
                                                    Эээ. Вы путаете. При использовании Simple Recovery Model полный бэкап никак не влияет на используемое место в логе. Я попробую на пальцах объяснить. У каждой транзакции в логе есть признак — можно ее затирать или нет. Когда новая транзакция пишется в лог, сервер ищет, что можно затереть, если находит — пишет поверх. Когда используется FULL модель — признак, что транзакцию можно затереть, проставляется только после того, как эта транзакция попала в бэкап лога транзакций. Когда используется SIMPLE модель — признак, что транзакцию можно затереть, проставляется сразу после того, как транзакция была завершена. Как следствие, резервное копирование (полное или разностное) при FULL и SIMPLE модели ничем не отличается, а бэкап лога транзакций возможен только при FULL модели — потому что только при такой модели возможно сохранение всей цепочки транзакций.
                                              0
                                              В тех местах, где скуль съедал всю выделенную ему память и при этом иногда тормозил, было выявлено следующее-в свойствах баз данных был включен режим Автосжатия. Его я снял сегодня ночью. После этого со временем скуль освободил больше 1 ГБ оперативной памяти. Возможно проблему, связанную с потреблением всей выделенной памяти скулем и тормозами решена. По индексам (фрагментация) пока нет результатов значительных. По запросам отпишу позже-пока эффект несильно виден.
                                                0
                                                После выключения автосжатия в свойствах базы данных, индексы перестали быстро фрагментироваться (раньше за 1 сутки фрагментировались свыше 60%, а сейчас и до 30% за сутки не доходит).
                                                0
                                                Еще имеет смысл проверить auto close параметр БД, на всякий случай…

                                                -- sqlcmd mode
                                                
                                                :setvar DatabaseName "AdventureWorks2014"
                                                
                                                IF DATABASEPROPERTY('$(DatabaseName)','IsAutoClose') = 1 
                                                begin
                                                	print 'autoclose = true'
                                                	ALTER DATABASE [$(DatabaseName)] SET AUTO_CLOSE OFF WITH NO_WAIT
                                                	print 'now autoclose = false'
                                                end
                                                

                                                  0
                                                  Да это проверил-не стоит)
                                                  Об этом еще писали: ссылка «https://habrahabr.ru/post/275873/»
                                                  Так, по ходу в комментариях ссылки не работают(

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

                                                Самое читаемое