SQL-Ex blog

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

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

Давайте посмотрим, как это делается на практике.

Вот несколько ранее использованных фрагментов кода SQL для создания таблицы и заливки в нее миллиона строк:

--Создание таблицы для тестирования

CREATE TABLE dbo.Test(

   Id INT IDENTITY(1,1) CONSTRAINT PK_Test PRIMARY KEY CLUSTERED, 

   TextValue VARCHAR(20) NULL

);

--Создание индекса на TextValue

CREATE INDEX IX_Test_TextValue ON dbo.Test(TextValue);

--Вставка строк в таблицу

INSERT INTO dbo.Test(TextValue) 

SELECT TOP 1000000 'blah'

FROM sys.objects a, sys.objects b, sys.objects c, sys.objects d; 

Я еще не обновлял статистику. Как это видно при просмотре:

--Просмотр статистики

DBCC SHOW_STATISTICS('dbo.Test', IX_Test_TextValue) WITH STAT_HEADER;

Статистика пока не содержит никакой информации, но на одну вещь уже можно обратить внимание. Видите этот последний столбец Persisted Sample Percent (процент сохраняемой выборки)? Он был добавлен для поддержки этой функциональности.

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

--Произвольный запрос

SELECT * 

FROM dbo.Test

WHERE TextValue = 'not blah';

Снова посмотрим статистику:



Видно, что статистика обновилась, и таблица оказалась достаточно большой, чтобы SQL решил сделать выборку, а не сканировать весь индекс. В выборку попало примерно 42% строк. Persisted Sample Percent равен 0, т.е. не установлен.

Пусть я хочу убедиться, что этот объект статистики всегда обновляется при полном сканировании.

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

--Обновить статистику и записать частоту выборки

UPDATE STATISTICS dbo.Test IX_Test_TextValue 

WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;

Давайте проверим, что было сделано, то что мы хотели:



Отлично, мы видим, что все строки попали в выборку и Persisted Sample Percent теперь установлен в 100.

Теперь посмотрим, что случится, если произойдет еще одно автоматическое обновление. Я вставлю еще один миллион строк с помощью того же запроса. Затем я выполню снова запрос на выборку. Оптимизатор заметит, что статистика устарела, и поэтому он обновит её и перекомпилирует план запроса. Если мы проверим статистку, то увидим это:



SQL сделал то, что мы хотели, он выполнил автоматическое обновление статистики в фоновом режиме и использовал постоянную частоту выборки (дискретизации) 100%.