Статистика в Microsoft SQL Server – что это такое и для чего она нужна

Всем привет! Сегодня мы с Вами поговорим о том, что же такое статистика в Microsoft SQL Server, Вы узнаете, для чего она нужна, какую информацию она включает, какие параметры базы данных влияют на работу статистики и многое другое.

Заметка! Для чего нужны хранимые процедуры в языке T-SQL.

Что такое статистика в Microsoft SQL Server

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

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

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

Таким образом, «оптимизатор запросов» (именно так называется компонент, который формирует план выполнения запроса) перед тем, как сформировать итоговый план выполнения запроса, анализирует много различных факторов, и перебирает много различных планов выполнения, чтобы в конечном итоге сформировать оптимальный план. И здесь стоит отметить, что оптимизатор ищет именно оптимальный, так называемый «достаточно хороший план» выполнения запроса, дело в том, что на поиск самого лучшего плана оптимизатору может потребоваться очень много времени, т.е. в результате это может быть неэффективно, так как, допустим, оптимизатор может потратить 1 секунду на поиск самого лучшего плана, который выполнится за 0.1 секунды (т.е. суммарно запрос выполнится за 1.1 секунды), хотя, если оптимизатор не искал бы самый лучший план, он мог, например, за 0.1 секунды найти план, который выполнит запрос за 0.2 секунды (т.е. суммарно запрос выполнится за 0.3 секунды), что в конечном счете будет намного быстрее, чем при самом лучшем плане. Поэтому здесь нужен компромисс и оптимизатор ищет именно оптимальный план выполнения запроса.

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

И здесь возникает вопрос «как SQL Server получить эти сведения?». Собирать их при каждом запросе? Нет – это очень накладно, т.е. крайне неэффективно, поэтому SQL Server периодически собирает сведения о количестве и распределении данных в таблицах и хранит их в отдельном месте. И именно это и называется статистика.

Статистика – это статистические сведения о распределении значений в одном или нескольких столбцах таблицы или индексированного представления.

Оптимизатор запросов использует статистику для оценки кратности – числа строк в результатах запроса. Такая оценка кратности позволяет оптимизатору запросов создать оптимальный план запроса. Например, в зависимости от предикатов оптимизатор запросов может использовать оценку кратности, чтобы выбрать оператор Index Seek вместо оператора Index Scan, который потребляет больше ресурсов, если благодаря этому повысится производительность запроса.

SQL Server создает статистику автоматически для каждого индекса, при его создании, а также для отдельных столбцов, которые используются как аргументы поиска в предикатах запроса, однако в данном случае должен быть включён параметр базы данных AUTO_CREATE_STATISTICS (подробнее о параметрах базы данных, которые влияют на статистику, мы поговорим чуть позже). По умолчанию данный параметр включен, т.е. SQL Server настроен на автоматическое создание статистики. Однако стоит отметить, что он не влияет на создание статистики для индексов, т.е. даже если параметр AUTO_CREATE_STATISTICS выключен, статистика для индексов все равно будет создаваться.

• Работа со статистикой в Microsoft SQL Server. Часть 1 – Просмотр статистики
• Работа со статистикой в Microsoft SQL Server. Часть 2 – Создание и удаление статистики
• Работа со статистикой в Microsoft SQL Server. Часть 3 – Обновление статистики

Отфильтрованная статистика

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

Отфильтрованная статистика – это статистика, созданная для определенного подмножества данных.

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

Заметка! Всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней очень подробно рассмотрены основные конструкции языка.

Разделы статистики

Статистика включает 3 раздела.

Заголовок

Содержит общую информацию о статистике, такую как

Заметка! Обзор инструментов для работы с Microsoft SQL Server.

Плотность

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

Иными словами

Плотность = 1 / Число различных значений столбца (столбцов)

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

Плотность содержит следующую информацию о статистике, называемую «Вектор плотностей».

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

Заметка! Транзакции в T-SQL – основы для новичков с примерами.

Гистограмма

Гистограмма – это распределение данных в первом ключевом столбце объекта статистики.

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

Примечание! Гистограммы в SQL Server создаются только для одного столбца, которым является первый столбец в наборе ключевых столбцов объекта статистики.

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

Заметка! XACT_ABORT в T-SQL – что это такое и как использовать.

Пример статистики в Microsoft SQL Server

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

В данном случае показан пример вывода информации о статистике некластеризованного индекса IX_Person_LastName_FirstName, который создан для столбцов LastName и FirstName таблицы Person в схеме Person.

Примечание! Более подробно работа со статистикой в Microsoft SQL Server будет рассмотрена в следующих статьях.

   
   DBCC SHOW_STATISTICS (N'Person.Person', N'IX_Person_LastName_FirstName');

Параметры базы данных SQL Server, связанные со статистикой

В Microsoft SQL Server предусмотрено три параметра, которые влияют на условия и методы создания и обновления статистики. Эти параметры задаются только на уровне базы данных.

Данные параметры можно изменять в интерфейсе SQL Server Management Studio в свойствах базы данных на вкладке «Параметры», а также с помощью стандартной инструкции ALTER DATABASE SET.

Заметка! Обзор функционала SQL Server Management Studio (SSMS).

Параметр AUTO_CREATE_STATISTICS

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

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

Если оптимизатор запросов создает статистику при помощи параметра AUTO_CREATE_STATISTICS, имя статистики начинается с префикса _WA.

Параметр AUTO_UPDATE_STATISTICS

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

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

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

В общей практике принято считать, что пороговое значение, при котором SQL Server будет считать статистику устаревшей и обновит ее, это примерно 20% от количества строк в таблице, хотя алгоритм определения порогового значения, конечно же, чуть сложнее. Иными словами, SQL Server обновит статистику если в таблице изменилось примерно 20% данных. Например, если в таблице 100 000 строк, SQL Server обновит статистику если изменится 20 000 строк.

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

Заметка! Рейтинг популярности систем управления базами данных (СУБД).

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

Параметр AUTO_UPDATE_STATISTICS применяется к объектам статистики, которые создаются для индексов, отдельных столбцов в предикатах запросов, и к статистике, создаваемой инструкцией CREATE STATISTICS. Этот параметр также применяется к отфильтрованной статистике.

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

Параметр AUTO_UPDATE_STATISTICS_ASYNC

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

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

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

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

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

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

Обновление асинхронной статистики выполняется с помощью фонового процесса. Когда запрос готов к записи обновленной статистики в базу данных, он пытается получить блокировку изменения схемы для объекта метаданных статистики. Если другой сеанс уже удерживает блокировку того же объекта, обновление асинхронной статистики блокируется до тех пор, пока не появится возможность получить блокировку изменения схемы. Аналогичным образом сеансы, которым требуется получить блокировку схемы (Sch-S) для объекта метаданных статистики для компиляции запроса, могут блокироваться фоновым сеансом асинхронного обновления статистики, который уже удерживает блокировку модификации схемы или ожидает ее получение. Таким образом, для систем с очень частыми компиляциями запросов и частыми обновлениями статистики использование асинхронной статистики может повысить вероятность проблем с блокировками.

Параметр AUTO_UPDATE_STATISTICS_ASYNC применяется к объектам статистики, которые создаются для индексов, отдельных столбцов в предикатах запросов, и к статистике, создаваемой инструкцией CREATE STATISTICS.

Примечание! Статистика по локальным временным таблицам всегда обновляется синхронно независимо от параметра AUTO_UPDATE_STATISTICS_ASYNC. Статистика по глобальным временным таблицам обновляется синхронно или асинхронно в соответствии с параметром AUTO_UPDATE_STATISTICS_ASYNC, заданным для пользовательской базы данных.

Заметка! Курсы по Transact-SQL для начинающих.

На сегодня это все, надеюсь, материал был Вам полезен, пока!