Планирование освоения возможностей выполняющейся в памяти OLTP в SQL Server

  • Статья

Применимо к:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

В этой статье описываются способы внедрения функций в памяти в SQL Server, влияющих на другие аспекты вашей бизнес-системы.

Примечание.

А. Освоение возможностей выполняющейся в памяти OLTP

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

A.1. Предварительные условия

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

A.2. Прогнозирование объема активной памяти

В вашей системе достаточно активной памяти для обеспечения работы новой таблицы, оптимизированной для обработки в памяти?

Microsoft SQL Server

Для поддержки оптимизированной для памяти таблицы, содержащей 200 ГБ данных, требуется более 200 ГБ активной памяти. Прежде чем создавать оптимизированную для обработки в памяти таблицу с большим объемом данных, необходимо спрогнозировать объем дополнительной активной памяти, которую, возможно, потребуется добавить на серверный компьютер. Руководство по оценке см. в следующих статьях:

Аналогичное руководство доступно для Управляемый экземпляр SQL Azure:

База данных SQL Azure

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

Оптимизированные для памяти табличные переменные

Переменная таблицы, объявленная оптимизированной для памяти, иногда предпочтительнее традиционной #TempTable, которая находится в tempdb базе данных. Переменные таблицы могут обеспечить повышение производительности без использования значительных объемов активной памяти.

A.3. Таблицу можно преобразовать в оптимизированную для обработки в памяти, только если она не используется

Некоторые функциональные возможности инструкции ALTER TABLE доступны для таблиц, оптимизированных для обработки в памяти. Однако инструкцию ALTER TABLE не получится использовать, чтобы преобразовать дисковую таблицу в таблицу, оптимизированную для обработки в памяти. Вместо этого необходимо выполнить несколько неавтоматизированных действий. Ниже описано несколько способов преобразования дисковой таблицы в оптимизированную для обработки в памяти.

Написание скриптов вручную

Один из способов преобразования дисковой таблицы в таблицу, оптимизированную для обработки в памяти, заключается в самостоятельном создании кода для необходимых действий Transact-SQL.

  1. Приостановите работу приложения.

  2. Создайте полную резервную копию.

  3. Переименуйте дисковую таблицу.

  4. Выполните инструкцию CREATE TABLE для создания новой оптимизированной для обработки в памяти таблицы.

  5. Вставьте (INSERT INTO) в свою оптимизированную для обработки в памяти таблицу данные из дисковой таблицы (с использованием вложенного SELECT).

  6. Удалите (DROP) дисковую таблицу.

  7. Создайте еще одну полную резервную копию.

  8. Возобновите работу приложения.

Помощник по оптимизации памяти

Помощник по оптимизации памяти позволяет создавать скрипт преобразования дисковой таблицы в оптимизированную для обработки в памяти таблицу. Он устанавливается в составе SQL Server Data Tools (SSDT).

DACPAC-файл

Воспользовавшись DACPAC-файлом, которым управляют SSDT, можно обновить базу данных "на месте". В SSDT можно указать изменения схемы, закодированной в DACPAC-файле.

Работа с DACPAC-файлами выполняется в контексте проекта Visual Studio типа База данных.

A.4. Руководство по определению того, подходит ли выполняющаяся в памяти OLTP вашему приложению

Руководство по определению того, могут ли возможности технологии обработки в памяти OLTP повысить производительность конкретного приложения, см. в следующей статье:

B. Неподдерживаемые функции

Функции, которые не поддерживаются в определенных сценариях OLTP в памяти, описаны в следующих случаях:

В следующих подразделах рассматривается ряд весьма важных неподдерживаемых возможностей.

Б.1. Снимок (SNAPSHOT) базы данных

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

  • первый элемент, оптимизированный для обработки в памяти, делает невозможным удаление последнего файла из оптимизированной для обработки в памяти файловой группы (FILEGROUP); и
  • ни одна база данных с файлом в оптимизированной для обработки в памяти файловой группе не поддерживает снимки.

Как правило, SNAPSHOT удобно использовать в быстрых итерациях тестирования.

Б.2. Межбазовые запросы

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

Табличные переменные не является транзакционными. Следовательно, переменные оптимизированных для обработки в памяти таблиц можно использовать в межбазовых запросах.

Б.3. Табличное указание READPAST

Не существует запроса, способного применить табличное указание READPAST к какой-либо оптимизированной для обработки в памяти таблице.

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

Б.4. RowVersion, объект Sequence

  • Не существует столбца, который можно пометить как RowVersion в оптимизированной для обработки в памяти таблице.

  • Объект SEQUENCE невозможно использовать в оптимизированной для обработки в памяти таблице с ограничением. Например, нельзя создать ограничение DEFAULT с предложением NEXT VALUE FOR. SEQUENCE можно использовать с операторами INSERT и UPDATE.

C. Обслуживание в рамках администрирования

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

Сброс начального значения удостоверений C.1, добавочный > 1

ИнструкцияDBCC CHECKIDENTне может использоваться для повторного заполнения столбца IDENTITY начальными значениями в оптимизированной для обработки в памяти таблице.

Значение приращения столбца IDENTITY ограничено единицей (1) в таблице, оптимизированной для обработки в памяти.

В.2. Инструкция DBCC CHECKDB не может проверять оптимизированные для обработки в памяти таблицы

Если целевым объектом инструкции DBCC CHECKDB является оптимизированная для обработки в памяти таблица, никакие действия не выполняются. Обходное решение:

  1. Резервное копирование журнала транзакций.

  2. Выполните резервное копирование файлов в оптимизированной для обработки в памяти файловой группе на NULL-устройство. Процесс резервного копирования вызывает проверку контрольных сумм.

    При обнаружении повреждений перейдите к следующим шагам.

  3. Скопируйте данные из оптимизированных для обработки в памяти таблиц в дисковые таблицы на временное хранение.

  4. Восстановите файлы оптимизированной для обработки в памяти файловой группы.

  5. Вставьте (INSERT INTO) в оптимизированные для обработки в памяти таблицы данные, которые временно сохранены в дисковых таблицах.

  6. Удалите таблицы на основе дисков, временно удерживающие данные.

D. Производительность

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

Г.1. Несколько замечаний об индексах

Все индексы в оптимизированной для обработки в памяти таблице создаются и управляются при помощи табличных инструкций, CREATE TABLE и ALTER TABLE. Невозможно работать с оптимизированной для обработки в памяти таблицей через инструкцию CREATE INDEX.

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

Примечание.

В документации по SQL Server термин "сбалансированное дерево" обычно используется в отношении индексов. В индексах rowstore SQL Server реализует B+-дерево. Это не относится к индексам columnstore или хранилищам данных в памяти. Дополнительные сведения см. в руководстве по архитектуре и проектированию индексов SQL Sql Server и Azure.

Два специальных типа индексов требуют дополнительного обсуждения в контексте оптимизированных для обработки в памяти таблиц: хэш-индексы и индексы Columnstore.

Обзорную информацию об индексах в оптимизированных для обработки в памяти таблицах см. в следующих источниках:

Хэш-индексы

Иногда хэш-индексы — это самый быстрый способ получить доступ к конкретной строке, указав точное значение первичного ключа в операторе=.

  • Неточные операторы, такие как !=, > или BETWEEN, могут снизить производительность при использовании совместно с хэш-индексом.

  • Возможно, хэш-индекс не лучший выбор, если скорость дублирования значений ключа становится слишком высокой.

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

Некластеризованные индексы columnstore

Оптимизированные для обработки в памяти таблицы обеспечивают высокую скорость обработки типичных транзакционных бизнес-данных, что в парадигме называется обработкой транзакций в режиме реального времени (или OLTP). Индексы Columnstore обеспечивают высокую скорость обработки агрегирования и других аналогичных операций обработки данных, которые мы называем аналитикой. В прошлом для удовлетворения потребностей OLTP и обработки аналитических операций считалось оптимальным наличие отдельных таблиц с активным перемещением данных и определенной степенью дублирования. Сегодня доступно более простое гибридное решение : индекс columnstore в оптимизированной для обработки в памяти таблице.

  • Индекс columnstore может быть создан на основе дисковой таблицы и даже в форме кластеризованного индекса. Однако в таблице, оптимизированной для обработки в памяти, индекс columnstore не может быть кластеризован.

  • Столбцы LOB или внестрочные столбцы в оптимизированной для обработки в памяти таблице не позволяют создавать в таблице индекс columnstore.

  • Инструкции ALTER TABLE не могут выполняться в отношении таблицы, оптимизированной для обработки в памяти, при наличии в последней индекса columnstore.

    • С августа 2016 года корпорация Майкрософт активно работает над повышением эффективности повторного создания индекса columnstore.

Г.2. Столбцы LOB и внестрочные столбцы

Большие объекты (LOB) — это столбцы таких типов, как varchar (max). Наличие пары столбцов LOB в таблице, оптимизированной для обработки в памяти, наверное, не окажет сколько-нибудь ощутимого отрицательного влияния на производительность. Однако старайтесь не создавать лишние столбцы LOB. То же самое касается внестрочных столбцов. Не присваивайте столбцу тип nvarchar(3072), если достаточно varchar(512).

Чуть более подробные сведения о столбцах LOB и внестрочных столбцах доступны в следующих источниках:

Е. Ограничения, связанные со скомпилированными в машинный код процедурами

Определенные элементы Transact-SQL не поддерживаются в скомпилированных в машинный код модулях T-SQL, включая хранимые процедуры. Дополнительные сведения о поддерживаемых компонентах см.:

Рекомендации по переносу модуля Transact-SQL, использующего неподдерживаемые функции для компиляции в собственном коде, см. в статье:

Помимо ограничений на некоторые элементы Transact-SQL, также существуют ограничения для операторов запросов, поддерживаемых в модулях T-SQL в собственном коде. Из-за этих ограничений скомпилированные в собственном коде хранимые процедуры не подходят для аналитических запросов, которые обрабатывают большие наборы данных.

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

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

Типы соединений

Хэш-соединения и соединения слиянием не могут быть частью любого плана запроса для собственного proc. Используются соединения вложенных циклов.

Агрегирование хэшей отсутствует

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

  • Агрегирование хэша лучше, если необходимо агрегировать данные из большого количества строк.

F. Проектирование приложений: транзакции и логика повторных попыток

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

В SQL Server 2016:

  • Допустимый максимум — восемь зависимых транзакций. Восемь также является ограничением транзакций, от которые может зависеть любая данная транзакция.
  • Номер ошибки — 41839. (В SQL Server 2014 номер ошибки — 41301.)

Скрипты Transact-SQL можно сделать более устойчивыми к ошибкам, связанным с транзакциями, добавив в скрипты логику повторных попыток . Логика повторных попыток окажется полезной, если вызовы UPDATE и DELETE выполняются часто или если на оптимизированную для обработки в памяти таблицу ссылается внешний ключ другой таблицы. Подробная информация доступна в следующих статьях:

Связанный контент