MS SQL Server - дело тонкое...


Важное изменение алгоритма создания LSN в SQL Server 2014

http://www.sqlskills.com/blogs/paul/important-change-vlf-creation-algorithm-sql-server-2014/

Автор: Paul Randal

Опубликовано: 6 января 2015г.

SQL Server 2014 был выпущен еще в апреле прошлого года, и ходили некоторые слухи об изменениях в алгоритме создания VLF. Они направлены на уменьшение числа VLF, когда журнал увеличивается по команде или автоматически (далее я буду говорить для простоты авто-приращение, поскольку это наиболее распространенный сценарий). Я сделал несколько экспериментов и подумал, что понял изменения указанного алгоритма. Оказывается, я понял не всё. На прошлой неделе в переписке MVP всплыл вопрос, который породил целую дискуссию, и мы вместе пришли к выводу, что алгоритм ведет себя недетерминированно... другими словами, мы не знаем, что он делает. Так что я обратился к моим друзьям в CSS, которые исследовали код (спасибо Bob Ward и Suresh Kandoth!) и объяснили изменения.

Изменение одно и довольно глубокое, оно направлено на предотвращение создания огромного количества VLF при частом авто-приращении. Это здорово, потому что слишком большое количество VLF (это зависит от размера журнала, но несколько тысяч обычно слишком много) может вызвать проблемы с производительностью для резервного копирования, восстановления, очистки журнала, репликации, восстановления после сбоев, откатов транзакций и даже рядовых операций DML.

До 2014 алгоритм определения необходимого числа VLF при создании, увеличении или авто-приращении журнала зависел от его размера и следующих вопросов:

  • Менее 1 Мб, тут всё довольно сложно, игнорируйте этот вариант.
  • До 64 МБ: 4 новых VLF, каждый примерно 1/4 размера прироста
  • От 64 МБ до 1 ГБ: 8 новых VLF, каждый примерно 1/8 размер прироста
  • Более 1 GB: 16 новых VLF, каждый примерно 1/16 размер прироста
  • Так что, если вы создали свой журнал размером 1 Гб и авто-прирост выполнялся порциями по 512 МБ до 200 Гб, у вас получится: 16 + ((200 - 1) х 2 х 8) = 3200 VLF (16 VLF при первоначальном создании журнала, 200 - 1 = 199 Гб роста по 512 Мб на автоматическое увеличение = 398 операций авто-прироста, каждый из которых создаст по 8 VLF).

    Для SQL Server 2014 алгоритм изменился так:

  • Является ли размер прироста менее 1/8 размера журнала?
  • Да: создать 1 новый VLF, равный размеру прироста
  • Нет: воспользоваться приведенной ранее формулой
  • Так что для SQL Server 2014, если вы создали свой журнал размером 1 Гб с авто-приростом кусками по 512 МБ до 200 Гб, у вас получится:

  • 16 VLF при первоначальном создании журнала
  • Все новообразования вплоть до размера журнала 4,5 ГБ происходили в соответствии с формулой, и можно было бы наблюдать такие размеры файла: 1, 1.5, 2, 2.5, 3, 3.5, 4 Гбайт, и на каждом приращении добавлялись по 8 VLF = 56 VLF в сумме.
  • Все новообразования для размера журнала более 4 ГБ будут создавать только по 1 VLF - процентный рост = (200 - 4) х 2 = 392 VLF
  • Всего = 392 + 56 + 16 = 464 VLF
  • 464 многим более разумное число VLF чем 3200, и будет создавать гораздо меньше проблем с производительностью.

    Меня также спрашивали влияет ли на это уровень совместимости? Нет, уровень совместимости игнорируется внутри механизмов Storage Engine.

    Я думаю, что это отличное изменение, и не вижу каких-либо недостатков в нём (кроме того, что оно не было обнародовано, когда SQL Server 2014 был выпущен). В блоге CSS в ближайшее время появится комплексный обзор об этом.

    Вы можете подумать, что это может привести к появлению VLF очень большого размера (например, если вы установите размер авто-приращения 4 Гб для журнала размером 100 Гб), и это возможно. Но что с того? Очень большие VLF будут проблемой только тогда, когда они создаются первоначально, а затем вы пытаетесь сжать журнал до минимального размера. Как минимум, вы можете иметь в журнале только два VLF, так что у вас получилось бы два гигантских VLF в начале журнала, а за ними более мелкие, появившиеся после прироста журнала. Это может быть проблемой, которая помешает журналу использовать повторно высвобождающиеся виртуальные журналы без нужды в авто-приращении, но это не идёт ни в какое сравнение с тем, как часто можно столкнуться со слишком большим числом VLF. И этот сценарий нетипичен для нового алгоритма. (К слову, вы можете решить эту проблему путем создания моментального снимка базы данных, а затем восстановить базу данных до состояния, сохраненного в моментальном снимке, что удалит журнал и создаст новый размером 0,5 Мб с двумя крошечными VLF ... это баг особенность, которая появилась с 2005 года, но она нарушит ваш цепочку резервного копирования журнала, когда такое восстановление закончится).

    Конечно, для улучшения управления VLF нужно сделать в будущем ещё очень многое (например, для решения проблемы, которую я только что описал), но сделанное - гигантский шаг в правильном направлении.

    добавлено: 08 янв 15 просмотры: 3153, комментарии: 1



    Автономный Монитор зеркального отображения баз данных

    Эта короткая заметка адресуется тем, кто после внедрения SQL Server 2014 столкнулся с
    проблемой запуска Монитора зеркального отображения баз данных. Чаще всего его
    вызываем из Management Studio. Однако, его можно запустить самостоятельно, на моём компьютере он
    обнаруживается по следующему пути: C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\sqlmonitor.exe


    После запуска с правами администратора программы sqlmonitor.exe, вам предстанет главное
    окно Монитора репликации, в меню которого в пункте «Перейти» можно выбрать Монитор
    зеркального отображения баз данных. Следующий скриншот демонстрирует эту
    возможность.


    добавлено: 24 дек 14 просмотры: 3141, комментарии: 1



    Блог переехал :(

    С прискорбием сообщаю, что блог неожиданно для меня переехал сюда: http://blogs.msmvps.com/gladchenko/
    Картинки пропали, ссылки не работают... Пока ещё не решил, буду чинить всё там, или переносить контент сюда. Пока знаю точно, что устаревшие статьи править не стану, некогда. Извините за возможные неудобства!
    добавлено: 28 июл 14 просмотры: 3143, комментарии: 3



    SQLCAT's Guide to High Availability and Disaster Recovery

    Представляем вашему вниманию новую бесплатную электронную книгу в формате PDF, выпущенную командой SQLCAT: SQLCAT's Guide to High Availability and Disaster Recovery

    Ниже представлен свободный перевод одной из глав книги.

    http://blogs.msmvps.com/gladchenko/2013/10/28/aoag/
    добавлено: 07 ноя 13 просмотры: 2399, комментарии: 0



    Tips for DBA: Scripting jobs using Powershell (separated files)

    Вашему вниманию предлагается сильно упрощённый пример сценария Powershell, который предназначен для скриптования заданий SQL Server в отдельные файлы. Тут используется папка для файлов C:\TEMP, которая должна быть предварительно создана и, желательно, пуста. Поскольку имена заданий будут использованы в качестве имён файлов, желательно, что бы в них не использовались недопустимые для имён файлов символы. Если это неудобно, попробуйте внести изменения в то место сценария, где подобные символы заменяются на пробелы.

    Продолжить чтение: http://msmvps.com/blogs/gladchenko/archive/2013/03/27/1825936.aspx
    добавлено: 28 мар 13 просмотры: 2202, комментарии: 0



    Изменения в SQL Server 2012, связанные с диспетчером памяти

    По материалам статьи Juergen Thomas: Memory Manager surface area changes in SQL Server 2012 в блоге SQLOS Team
    Автор: G Bowerman
    Перевод: Александр Гладченко
    Технический редактор: Ирина Наумова

    Множество изменений было внесено в компоненты диспетчера памяти SQLOS новой версии SQL Server 2012. Эти изменения можно обнаружить в динамических административных представлениях (DMV) объектов памяти, в команде выдачи статуса объектов памяти DBCC и в счётчиках производительности. Изменения в диспетчере памяти позволяют теперь получать более точную информацию о потреблении памяти сервером и помогают SQL Server более эффективно управлять распределением страниц, а также существенно улучшают обработку и отслеживание распределения памяти между узлами NUMA. Задача этой статьи собрать эти изменения в одном месте, чтобы можно было быстро найти ссылки на подробную информацию о деталях, относящихся к новшествам диспетчера памяти.
    Некоторые из этих изменений призваны были стандартизировать измерения счётчиков памяти в килобайтах, а не в байтах или страницах, логически продолжая тот процесс, который уже был начат в более ранних версиях. Сами процедуры подсчёта использования памяти теперь стали более релевантными и достоверными, поскольку модули, отвечающие за одностраничные и многостраничные распределения, были заменены одним модулем, который теперь заведует распределением страниц любого размера и любого числа этих страниц. Также консолидация распределения страниц разного размера в одном модуле позволила придать целостный и законченный вид счётчикам производительности, отражающим распределение страниц.
    Другие изменения относятся к изменению места диспетчера памяти в модели интерфейсов взаимодействия с операционной системой. Он теперь играет центральную роль, обеспечивая распределение страниц практически для всех компонент.

    Читать весь текст статьи: http://msmvps.com/blogs/gladchenko/archive/2012/09/03/1815794.aspx
    добавлено: 04 сен 12 просмотры: 3563, комментарии: 0



    Tips for DBA: Экспресс-диагностика достаточности памяти системе и экземпляру SQL Server


    По ссылке в сценарии можно найти статью, которая меня вдохновила написать пример сценария, который может оказаться полезным для экспресс-диагностики проблем распределения оперативной и виртуальной памяти для нужд запрашиваемого экземпляра SQL Server и операционной системы. Я оставил только реальные (как мне думается) сценарии, которые могут случиться с памятью. Прогон на моих серверах вроде показал правдивость обнаруженного. Посмотрите у себя? Обсудить результаты и сам сценарий можно в коментариях.



    -- Быстрый тест проблем с памятью
    -- По мотивам: http://bit.ly/LkT05M
    WITH RingBufferXML
    AS(SELECT CAST(Record AS XML) AS RBR FROM sys .dm_os_ring_buffers
       WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
      )
    SELECT DISTINCT 'Зафиксированы проблемы' =
              CASE
                        WHEN XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint')  = 0 AND
                             XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint')   = 2
                        THEN 'Недостаточно физической памяти для системы'
                        WHEN XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint')  = 0 AND
                             XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint')   = 4
                        THEN 'Недостаточно виртуальной памяти для системы' 
                        WHEN XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]', 'tinyint') = 2 AND
                             XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint')   = 0
                        THEN'Недостаточно физической памяти для запросов'
                        WHEN XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]', 'tinyint') = 4 AND
                             XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]', 'tinyint')  = 4
                        THEN 'Недостаточно виртуальной памяти для запросов и системы'
                        WHEN XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint')  = 2 AND
                             XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint')   = 4
                        THEN 'Недостаточно виртуальной памяти для системы и физической для запросов'
                        WHEN XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]', 'tinyint') = 2 AND
                             XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]', 'tinyint')  = 2

                        THEN 'Недостаточно физической памяти для системы и запросов'
             END
    FROM        RingBufferXML
    CROSS APPLY RingBufferXML.RBR.nodes ('Record') Record (XMLRecord)
    WHERE       XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint') IN (0,2,4) AND
                XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]' ,'tinyint') IN (0,2,4) AND
                XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint') +
                XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]' ,'tinyint') > 0

    добавлено: 20 июн 12 просмотры: 3700, комментарии: 4



    Оптимизации ввода-вывода для нагруженных баз данных

    http://www.techdays.ru/videos/4314.html
    В докладе рассмотрены вопросы оборудования дисковых подсистем для задач SQL Server в приложениях хранилищ данных и OLTP нагрузки. Будут рассмотрены варианты использования сетей на основе Fibre Channel и коммутаторов SAN. Вы увидите какие ограничения могут накладывать на производительность ввода-вывода разные компоненты дисковой подсистемы. Будут даны основы современных дисковых подсистем и их важные особенности с точки зрения обслуживания нагрузки SQL Server. Будет показано, на какие параметры конфигурации СХД, операционной системы и СУБД нужно обращать внимание. В презентации будут представлены несколько примеров, демонстрирующих возможности современных дисковых подсистем.



    Другие доклады
    Конференция "24 Hours of PASS. Russian Edition"
    http://www.techdays.ru/videos/24 HOP

    добавлено: 02 май 12 просмотры: 3416, комментарии: 1



    Изменения в автоматическом обновлении статистики SQL Server -T2371

    По материалам статьи Juergen Thomas: Changes to automatic update statistics in SQL Server - traceflag 2371
    Перевод: Александр Гладченко
    Технический редактор: Ирина Наумова

    C 1998 года, когда вышел SQL Server 7.0, базовым принципом дизайна автоматического обновления статистики было отслеживание количества изменений в таблице. Когда количество изменений в таблице превышало определённый порог (процент), выполнялось автоматическое обновление статистики.
    В последующих версиях, вплоть до SQL Server 2005, в дизайне сбора статистической информации произошли некоторые изменения, например, была добавлена возможность асинхронного обновления статистики. Кроме этого, в SQL Server была добавлена возможность оптимизации исполнения запросов за счёт отслеживания изменений на уровне столбцов с целью предоставления возможности обновления только статистики по селективности строк таблицы. Для получения более подробной информации можно почитать более полный обзор статистических данных, используемых оптимизатором запросов, который доступен здесь: http://msdn.microsoft.com/en-us/library/dd535534(SQL.100).aspx

    Изначально, в версиях SQL Server 2008 и 2008 R2 не было никаких крупных изменений в дизайне сбора и использования статистики. Появившийся ещё во времена разработки SQL Server 7.0 алгоритм, основанный на фиксированном пороговом значении, остался без существенных изменений. Превышение этого порога вызывало автоматическое обновление статистики. Пороговое значение, используемое изначально, равнялось 20%, и оно оставалось таким во всех выпусках SQL Server, которые до недавнего времени появлялись на рынке. Это означает, что до недавнего времени все выпуски SQL Server инициировали процедуру автоматического обновления статистики, если для столбца таблицы были зафиксированы изменения, которые затрагивали более 20% от числа строк таблицы. Обновлению подлежит индекс на основе B-дерева, который включает такой столбец в качестве первого столбца индекса, или если существует отдельная статистика для этого столбца среди относящихся к таблице статистик столбцов. Индексы, в которых не участвует этот столбец в качестве первого (ведущего) столбца индекса, не нуждаются в обновлении статистики. Ещё одно ограничение, это реализация в коде SQL Server 7.0 специальных защитных мер для маленьких таблиц от частого обновления статистики. Требуется, чтобы в таблице было, по меньшей мере, 500 строк (6 строк для временных таблиц), тогда автоматический пересчёт статистики у этой таблицы будет происходить после превышения двадцатипроцентного порога изменений.

    Читать дальше: http://bit.ly/xu4z2V
    добавлено: 27 янв 12 просмотры: 3912, комментарии: 3



    Повышение пропускной способности сетевых интерфейсов для SQL Server с помощью настройки RSS

    По материалам статьи: Кун Ченг (Kun Cheng) Maximizing SQL Server Throughput with RSS Tuning



    Рецензенты: Thomas Kejser, Curt Peterson, James Podgorski, Christian Martinez, Mike Ruthruff
    Перевод: Александр Гладченко
    Технические редакторы перевода: Алексей Халяко, Ирина Наумова

    Функциональность Receive-Side Scaling (RSS) впервые появилась в Windows 2003. Это нововведение было призвано повысить возможности масштабируемости операционной системы Windows, и этим предоставить новые возможности по обслуживанию большого сетевого трафика. Такой трафик характерен для систем, где SQL Server обслуживает OLTP нагрузку. Подробное описание того, какие усовершенствования RSS получила операционная система Windows 2008, можно узнать из отчёта - Receive-Side Scaling Enhancements in Windows Server 2008 и в блоге - Scaling Heavy Network Traffic with Windows.



    Продолжение

    добавлено: 17 окт 11 просмотры: 2670, комментарии: 1