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

Область применения: 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. Создайте хранимые процедуры, обращающиеся к таблицам.

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

См. также

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

  • Last updated on