Поделиться через

Статистика для таблиц, оптимизированных для памяти

  • Статья

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

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

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

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

Замечания по статистике для оптимизированных для памяти таблиц.

  • Начиная с SQL Server 2016 и в База данных SQL Azure автоматическое обновление статистики поддерживается для таблиц, оптимизированных для памяти, при использовании уровня совместимости базы данных не менее 130. См. раздел Уровень совместимости инструкции ALTER DATABASE (Transact-SQL). Если в базе данных есть таблицы, которые были созданы ранее с использованием более низкого уровня совместимости, статистику необходимо обновить один раз вручную, чтобы затем ее обновление могло происходить автоматически.

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

  • Скомпилированные в собственном коде хранимые процедуры можно перекомпилировать вручную с помощью процедуры sp_recompile (Transact-SQL). Кроме того, они перекомпилируются автоматически, если база данных переводится в режим "не в сети", а затем возвращается в режим "в сети", либо если произошла отработка отказа базы данных или перезапуск сервера.

Включение автоматического обновления статистики в существующих таблицах

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

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

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

  1. Измените уровень совместимости базы данных. ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130

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

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

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

-- Assuming AUTO_UPDATE_STATISTICS is already ON for your database:
-- ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS ON;

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 130;
GO
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT
      @sql += N'UPDATE STATISTICS '
         + quotename(schema_name(t.schema_id))
         + N'.'
         + quotename(t.name)
         + ';' + CHAR(13) + CHAR(10)
   FROM sys.tables AS t
   WHERE t.is_memory_optimized = 1 AND 
		t.object_id IN (SELECT object_id FROM sys.stats WHERE no_recompute=1)
;
EXECUTE sp_executesql @sql;
GO
-- Each row appended to @sql looks roughly like:
-- UPDATE STATISTICS [dbo].[MyMemoryOptimizedTable];

Проверка включения автоматического обновления. Приведенный ниже скрипт проверяет, включено ли автоматическое обновление статистики для оптимизированных для памяти таблиц. По завершении выполнения приведенный выше скрипт возвращает 1 в столбце auto-update enabled для всех объектов статистики.

SELECT 
	quotename(schema_name(o.schema_id)) + N'.' + quotename(o.name) AS [table],
	s.name AS [statistics object],
	1-s.no_recompute AS [auto-update enabled]
FROM sys.stats s JOIN sys.tables o ON s.object_id=o.object_id
WHERE o.is_memory_optimized=1

Рекомендации по развертыванию таблиц и процедур

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

  1. Убедитесь в том, что база данных имеет уровень совместимости не ниже 130. См. раздел Уровень совместимости инструкции ALTER DATABASE (Transact-SQL).

  2. Создайте таблицы и индексы. Индексы необходимо указывать как встроенные в инструкциях CREATE TABLE .

  3. Загрузите данные в таблицы.

  4. Создайте хранимые процедуры, обращающиеся к таблицам.

Создание скомпилированных в собственном коде хранимых процедур после загрузки данных гарантирует то, что оптимизатор имеет доступ к статистическим данным для оптимизированных для памяти таблиц. Это обеспечит формирование эффективных планов запросов при компиляции процедуры.

См. также

Таблицы, оптимизированные для памяти