Многие компании, работающие с более ранними версиями SQL Server и испытывающие проблемы с производительностью и масштабируемостью в рамках своей рабочей нагрузки на SQL Server, планируют использовать больше аппаратного обеспечения для решения проблемы, сохраняя  версии приложения и SQL Server без изменений. Проблема такого подхода заключается в том, что вы ожидаете от программного обеспечения, написанного более 10 лет назад, успешный запуск и масштабирование на современных высокопроизводительных серверах с большой памятью, с более мощными процессорными ядрами. Хотя программное обеспечение может работать, но оно может не масштабироваться и в некоторых случаях может работать плохо, особенно если вы сталкиваетесь с проблемами масштабирования объектов памяти (CMEMTHREAD), так как теперь у вас больше ресурсов для синхронизации параллельных потоков, но все потоки ожидают на едином объекте памяти, который становится точкой соперничества и узким местом на высокопроизводительном сервере.

SQLOS и улучшения в работе с памятью в SQL Server 2016/2017 позволяют SQL Server масштабироваться на серверах высокого уровня.

• Динамическое масштабирование объектов памяти. Это улучшение динамически разделяет объекты памяти с одного нераспределенного объекта на уровень NUMA (по 1 на каждый узел) и далее вниз к узлам центрального процессора (1 объект памяти на процессор) в зависимости от коэффициента конкуренции на объекте памяти.
• Динамическое разделение блокировки базы данных. Это улучшение разделяет блокировки базы данных (SH), что сводит к минимуму хэш-коллизии, когда параллельные соединения выше в одной базе данных, что является обычным явлением при переходе на высокопроизводительные серверы.
• Реконструирование SOS_RWLock. SOS_RWLock. SOS_RWLock - это примитив синхронизации, используемый в разных местах всей базы кода SQL Server. С SQL Server 2016 SOS_RWLock использует оптимистичную синхронизацию при доступе к состоянию базы данных, что минимизирует блокировку и позволяет загружать рабочую нагрузку читателя / писателя из коробки.
• Косвенная контрольная точка. Контрольные контрольные точки не только сглаживают IO-пакет на больших серверах памяти, но также позволяют отслеживать измененные незафиксированные страницы) для баз данных и планировщиков, которые отделяют сканы незафиксированных страниц от объема памяти и позволяют контрольным точкам масштабироваться для баз данных независимо от размера буферного пула.

Примечание. При обновлении баз данных из более низкой версии косвенная контрольная точка ** NOT ** включена по умолчанию для обновленной базы данных. Вам нужно будет включить его, установив target_recovery_time для баз данных.

• Auto Soft NUMA - это улучшение подразделяет узлы NUMA для дальнейшего разбиения и масштабирования некоторых внутренних структур (CMEMTHREAD), что очень полезно для процессоров высокой частоты.

Кроме того, обновление до SQL Server 2016/2017 позволяет использовать новые функции, такие как колоночные индексы, оптимизированные для памяти (in-memory) таблицы, табличные переменные, параллельные вставки для дальнейшего масштабирования рабочей нагрузки, если вы можете позволить себе некоторые изменения схемы базы данных.

В большинстве сценариев клиенты предпочитают добавлять новое оборудование, а не обновлять свой SQL Server, поскольку общее мнение заключается в том, что стоимость обновления высока. Несмотря на это нелепое предположение, сперва нужно оценить и подсчитать стоимость обновления. Здесь очень полезен такой инструмент, как помощник по миграции данных (DMA). DMA - это полностью переделанный инструмент SQL Upgrade Advisor. Команда проделала большую работу над DMA, чтобы помочь определить факторы, которые могут мешать обновлению, а также подсказать способы их преодоления. Кроме того, DMA оценивает базы данных по нескольким уровням совместимости за один запуск, чтобы дать  рекомендацию, по которой настройка DBCompat подойдет вашему приложению базы данных. Запуск DMA позволит вам точно оценить стоимость, изменения и риск, связанные с обновлением.

Кроме того, при обновлении мы рекомендуем сохранить уровень совместимости базы данных с более старой версией с включенным хранилищем запросов. Позже вы можете поменять уровень DBCompat на последний уровень и использовать хранилище запросов для идентификации запросов, у которых произошла регрессия в плане производительности.

Давайте вернемся к вышеописанной теории и подтвердим ее некоторым примером. Этот подход был применен на практике одной из компаний, запустившей критическую рабочую нагрузку уровня  Tier 1 на SQL Server 2008 R2. Бизнес рос и соответственно росла рабочая нагрузка на сервере, так что приложение начало получать замедленное время отклика и тайм-ауты. Компания установила базовый уровень производительности примерно в 16-18 тыс. пакетных запросов  в секунду и наблюдала, когда рабочая нагрузка превышает 19-20 тыс., приложение начинает получать замедленное время отклика и тайм-ауты от базы данных. При изучении, команды CSS (Microsoft Premier Support) обнаружили, что рабочая нагрузка вызывала конфликт в памяти объекта, который не был разбит на разделы в SQL Server 2008 R2, ограничивая параллелизм и масштабируемость приложения. Первоначальный план компании заключался в том, чтобы добавить больше аппаратных ресурсов для масштабирования, но команда CSS была уверена,  что это не решит проблему и потенциально может ухудшить ее. Они рекомендовали запустить DMA и оценить их схему базы данных и TSQL-код, чтобы узнать, есть ли какие-либо препятствия для обновления. Было обнаружено несколько хранимых процедур с использованием синтаксиса TSQL, которые были устаревшими, но никогда не использовались, поэтому можно было их игнорировать.

После некоторого функционального тестирования было ясно, что приложение просто работает на SQL Server 2016 SP1 CU5 под DBCompat 100 без внесения каких-либо изменений в код. Затем было решено протестировать, решает ли обновление до SQL Server 2016 исходную проблему масштабируемости, изначально возникшую в приложении. Были проведены некоторые тесты производительности приложений и стресс-тесты с добавлением дополнительной рабочей нагрузки (22-25 тыс. пакетных запросов  в секунду) сначала в SQL 2008 / Windows 2008 R2, чтобы установить базовый уровень, а затем обновили до SQL 2016 / Windows Server 2012 для выполнения тестирования A / B, оставляя неизменным аппаратное обеспечение и приложение.

Ниже приведены результаты тестирования приложения A / B при одной и той же рабочей нагрузки на SQL 2016 и SQL 2008.

С помощью косвенной контрольной точки критические значения  значительно понижались, чтобы сгладить сбой ввода-вывода, как показано ниже, с максимумом в 70 страниц в секунду вместо 250 страниц в секунду в SQL 2008 R2.

SQL 2016 (Фоновое средство записи, страниц в секунду)

SQL 2008 (Страниц контрольных точек в секунду)

Общее время ожидания для запросов latch сократилось до 2,4 мс по сравнению с 26 мс при запуске SQL 2008.

SQL 2016

SQL 2008

Циклическая блокировка LOCK_HASH уменьшена, а период ожидания циклической блокировки SOS_RWLOCK исчез, как и ожидалось, в SQL 2016.

SQL 2016

SQL 2008

Было протестировано и определено, что данная конфигурация в SQL Server 2016 дала нам наилучшие результаты с точки зрения производительности, масштабирования и поддержки. Не наблюдается влияния на производительность приложения при включении хранилища Query, за исключением того, что потребляется немного больше памяти, но учитывая, насколько полезно, чтобы администраторы баз данных быстро сконцентрировались и идентифицировали регрессии запросов на рабочих серверах, оно того стоит.

• SQL Server 2016 SP1 CU5 (сборка 13.0.4451.0)
• Хранилище запросов включено с последующей настройкой

ALTER DATABASE <database name>

SET QUERY_STORE

(

OPERATION_MODE  = READ_WRITE,

CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),

DATA_FLUSH_INTERVAL_SECONDS= 900,

MAX_STORAGE_SIZE_MB = 1024,

INTERVAL_LENGTH_MINUTES= 15,

SIZE_BASED_CLEANUP_MODE  = AUTO,

QUERY_CAPTURE_MODE = AUTO,

MAX_PLANS_PER_QUERY = 1000

);   

• Флаг отслеживания 7752 включен для запуска асинхронной загрузки хранилища запросов.
• Косвенная контрольная точка включена для пользовательских баз данных при target_recovery_time = 60 seconds
• DBCompat для пользовательских баз данных: 100

Дальнейшее расширение с временными таблицами  в SQL Server 2016

Как только были проработаны некоторые из начальных узких мест, которые зажимают рабочую нагрузку на более ранней версии,  это могло привести к разрывам или появлению другой узкой точки, поскольку вы продолжаете увеличивать нагрузку и увеличивать параллелизм. В большинстве рабочих нагрузок после определенной критической точки время отклика снова начинает страдать из-за  базы данных tempdb или конфликтов метаданных или флеш-сообщений, предполагая, что в базе данных нет другой блокировки. В SQL Server 2016/2017 мы можем преодолеть это и позволить рабочей нагрузке масштабироваться дальше с памятью, преобразовывая временные таблицы, табличные переменные для некоторых высокоинтенсивных хранимых процедур  и таблиц с высокочувствительным интерфейсом для обработки in-memory

Правильные таблицы или хранимые процедуры для таблиц in-memory можно легко открыть с помощью Отчет об анализе производительности транзакции в SSMS.

В данном случае в качестве хороших вариантов для преобразования в таблицу in-memory были выявлены таблица состояний сеанса и очень хорошо выполненная хранимая процедура.

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

Если вам не удается преобразовать таблицы в схемы, вы можете использовать память классов хранения (энергонезависимое хранилище NVDIMM-N) и буфер хранения постоянного хранилища SQL Server, начиная с SQL1011 SP1 для дальнейшего масштабирования рабочей нагрузки, минимизирующей служебные данные writelog. Если вы используете флэш-массив с малой задержкой для файлов журналов, ожидания WRITELOG также могут быть уменьшены до минимума (4-8 мс).

Как объяснялось выше, целесообразно модернизировать ваш SQL Server прежде, чем обновлять ваше оборудование, поскольку обновление SQL Server и самой ОС может обеспечить лучший масштаб из коробки, даже если аппаратное обеспечение останется прежним. Кроме того, с последней версией SQL Server вы можете использовать новейшие функции и возможности, созданные на основе некоторых новейших аппаратных технологий, чтобы наилучшим образом использовать современное оборудование для дальнейшего увеличения рабочей нагрузки.

No Comments