Для чего используется статистика ms sql
Перейти к содержимому

Для чего используется статистика ms sql

  • автор:

Просмотр свойств статистики

Вы можете отобразить текущую статистику оптимизации запросов для таблицы или индексированного представления в SQL Server с помощью SQL Server Management Studio или Transact-SQL. Объекты статистики включают заголовок, содержащий метаданные о статистике, гистограмму, содержащую распределение значений в первом ключевом столбце объекта статистики, и вектор плотностей для измерения корреляции с охватом нескольких столбцов. Дополнительные сведения о гистограммах и векторах плотности см. в разделе DBCC SHOW_STATISTICS (Transact-SQL)

В этом разделе

  • Перед началом: Безопасность
  • Для просмотра свойств статистики используются:Среда SQL Server Management StudioTransact-SQL

Перед началом

Безопасность

Разрешения

Чтобы иметь возможность просматривать объект статистики, пользователь должен быть владельцем таблицы либо членом предопределенной роли сервера sysadmin , предопределенной роли базы данных db_owner или предопределенной роли базы данных db_ddladmin .

Использование среды SQL Server Management Studio

Просмотр свойств статистики
  1. В обозревателе объектовщелкните значок «плюс», чтобы развернуть базу данных, в которой нужно создать новую статистику.
  2. Чтобы развернуть папку Таблицы , щелкните значок «плюс».
  3. Щелкните значок плюса, чтобы развернуть таблицу, в которой нужно просмотреть свойства статистики.
  4. Щелкните значок «плюс», чтобы развернуть папку Статистика .
  5. Щелкните правой кнопкой мыши объект статистики, для которого нужно просмотреть свойства, и выберите команду Свойства.
  6. В диалоговом окне Свойства статистики —имя_статистики на панели Выбор страницы выберите Сведения. На странице Сведения в диалоговом окне Свойства статистики —имя_статистики отображаются следующие свойства. Имя таблицы
    Отображает имя таблицы, которую описывает данная статистика. Имя статистики
    Отображает имя объекта базы данных, в котором сохранена статистика. Статистика для INDEXимя_статистики
    В этом текстовом поле отображаются свойства, возвращенные из объекта статистики. Эти свойства делятся на три части: заголовок статистики, вектор плотностей и гистограмма. Следующие данные описывают столбцы, возвращенные в результирующем наборе для заголовка статистики. Наименование
    Имя объекта статистики. Обновлено
    Дата и время последнего обновления статистики. Строки
    Общее число строк в таблице или индексированном представлении при последнем обновлении статистики. Если статистика отфильтрована или соответствует отфильтрованному индексу, количество строк может быть меньше, чем количество строк в таблице. Rows Sampled
    Общее количество строк, выбранных для статистических вычислений. При наличии условия Rows Sampled < Rows отображаемые результаты гистограммы и плотности рассчитываются на основе строк выборки. Шаги
    Число шагов в гистограмме. Каждый шаг охватывает диапазон значений столбцов, за которым следует значение столбца, представляющее собой верхнюю границу. Шаги гистограммы определяются в первом ключевом столбце статистики. Максимальное число шагов — 200. Плотность
    Рассчитывается как 1 / различающиеся значения для всех значений в первом ключевом столбце объекта статистики, исключая возможные значения гистограммы. Это значение плотности не используется оптимизатором запросов и отображается для обратной совместимости с версиями, предшествующими SQL Server 2008. Средняя длина ключа
    Среднее число байтов на значение для всех ключевых столбцов в объекте статистики. String Index
    Значение «Да» указывает, что объект статистики содержит сводную строковую статистику, позволяющую уточнить оценку количества элементов для предикатов запроса, использующих оператор LIKE, например WHERE ProductName LIKE ‘%Bike’ . Сводная строковая статистика хранится отдельно от гистограммы и создается в первом ключевом столбце объекта статистики, если он имеет тип char, varchar, nchar, nvarchar, varchar(max), nvarchar(max), textили ntext. Критерий фильтра
    Предикат для подмножества строк таблицы, включенных в объект статистики. NULL — неотфильтрованная статистика. Unfiltered Rows
    Общее количество строк в таблице перед применением критерия фильтра. Если Filter Expression имеет значение NULL, то столбец Unfiltered Rows совпадает со столбцом Rows. Следующие данные описывают столбцы, возвращенные в результирующем наборе для вектора плотностей. Общая плотность
    Плотность равна 1 / различающиеся значения. В результатах отображаются плотности для каждого префикса столбцов объекта статистики, по одной строке на плотность. Различающееся значение — это отдельный список значений столбцов на строку и на префикс столбцов. Например, если объект статистики содержит ключевые столбцы (A, B, C), то в результатах приводится плотность отдельных списков значений в каждом из следующих префиксов столбцов: (A), (A, B) и (A, B, C). При использовании префикса (A, B, C) каждый из этих списков является отдельным списком значений: (3, 5, 6), (4, 4, 6), (4, 5, 6), (4, 5, 7). При использовании префикса (A, B) одинаковые значения столбцов имеют следующие отдельные списки значений: (3, 5), (4, 4) и (4, 5). Средняя длина
    Средняя длина (в байтах) для хранения списка значений столбца для данного префикса столбца. Если каждому значению в списке (3, 5, 6), например, требуется по 4 байта, то длина составляет 12 байт. Число столбцов
    Имена столбцов в префиксе, для которых отображаются значения «Общая плотность» и «Средняя длина». Следующие данные описывают столбцы, возвращенные в результирующем наборе для гистограммы. RANGE_HI_KEY
    Верхнее граничное значение столбца для шага гистограммы. Это значение столбца называется также ключевым значением. RANGE_ROWS
    Предполагаемое количество строк, значение столбцов которых находится в пределах шага гистограммы, исключая верхнюю границу. EQ_ROWS
    Предполагаемое количество строк, значение столбцов которых равно верхней границе шага гистограммы. DISTINCT_RANGE_ROWS
    Предполагаемое количество строк с различающимся значением столбца в пределах шага гистограммы, исключая верхнюю границу. AVG_RANGE_ROWS
    Среднее число строк с повторяющимися значениями столбцов на шаге гистограммы, за исключением верхней границы (RANGE_ROWS / DISTINCT_RANGE_ROWS для DISTINCT_RANGE_ROWS > 0).
  7. Щелкните OK.

Использование Transact-SQL

Просмотр свойств статистики
  1. В обозревателе объектов подключитесь к экземпляру ядра СУБД.
  2. На стандартной панели выберите пункт Создать запрос.
  3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.
USE AdventureWorks2022; GO -- The following example displays all statistics information for the AK_Address_rowguid index of the Person.Address table. DBCC SHOW_STATISTICS ("Person.Address", AK_Address_rowguid); GO
Поиск всех статистических данных по таблице или представлению
  1. В обозревателе объектов подключитесь к экземпляру ядра СУБД.
  2. На стандартной панели выберите пункт Создать запрос.
  3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.
USE AdventureWorks2022; GO /*Gets the following information: name and ID of the statistics, whether the statistics were created automatically or by the user, whether the statistics were created with the NORECOMPUTE option, and whether the statistics have a filter and, if so, what that filter is. */ SELECT name AS statistics_name ,stats_id ,auto_created ,user_created ,no_recompute ,has_filter ,filter_definition -- using the sys.stats catalog view FROM sys.stats -- for the Sales.SpecialOffer table WHERE object_id = OBJECT_ID('Sales.SpecialOffer'); GO

Дополнительные сведения см. в статье sys.stats (Transact-SQL).

SQL-Ex blog

Оптимизатор запросов SQL Server опирается на статистику для построения адекватного плана запроса. Если статистика неверна, устарела или отсутствует, вы имеете весьма слабую надежду на хорошую производительность ваших запросов. Поэтому важно понимать, как SQL Server поддерживает статистику распределения.

Что такое статистика?

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

Статистика распределения создается автоматически при создании индекса. Если у вас настроено автоматическое создание статистики (установка по умолчанию параметра базы данных AUTO_CREATE_STATISTICS), вы будете получать созданную статистику при всяком обращении к столбцу в предложениях запроса, выполняющих фильтрацию или задающих критерии соединения JOIN.

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

Плотность

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

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

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

SELECT 1.0 / COUNT(DISTINCT MyColumn) 
FROM dbo.MyTable;

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

SELECT 1.0 / COUNT(*) 
FROM (SELECT DISTINCT FirstColumn, 
SecondColumn 
FROM dbo.MyTable) AS DistinctRows;

И, конечно, вы можете добавить столбцы, по которым построен индекс, чтобы увидеть плотность индекса.

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

Следующая мера — распределение данных — несколько сложнее.

Распределение данных

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

Гистограмма — это функция, которая подсчитывает число вхождений данных в каждое множество категорий (известных как bins), и в статистике распределения эти категории выбираются так, чтобы представлять распределение данных. Именно эта информация может использоваться оптимизатором для оценки числа строк, возвращаемых заданным значением.

В SQL Server гистограмма содержит до 200 различных шагов, или bin’ов. Почему 200? 1) Это статистически существенно или, как мне говорят, 2) это мало, 3) это работает для большинства распределений данных в объеме до нескольких сотен миллионов строк. При больших объемах вам придется обратиться к материалам, относящимся к фильтрованной статистике, фрагментированным (секционированным) таблицам и другим архитектурным решениям. Эти 200 шагов представлены строками таблицы. Строки представляют способ распределения данных в столбце, показывая части данных, описывающих это распределение:

RANGE_HI_KEY Это верхняя граница шага, представленного данной строкой на гистограмме.
RANGE_ROWS Предполагаемое количество строк, значение столбцов которых находится в пределах шага гистограммы, исключая верхнюю границу.
EQ_ROWS Предполагаемое количество строк, значение столбцов которых равно верхней границе шага гистограммы.
DISTINCT_RANGE_ROWS Предполагаемое количество строк с различающимся значением столбца в пределах шага гистограммы, исключая верхнюю границу. Если все строки уникальны, то RANGE_ROWS и DISTINCT_RANGE_ROWS будут равны.
AVG_RANGE_ROWS Среднее количество строк с повторяющимися значениями столбцов в пределах шага гистограммы, исключая верхнюю границу (RANGE_ROWS/DISTINCT_RANGE_ROWS для DISTINCT_RANGE_ROWS > 0).

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

  • Если в таблице нет строк, то, когда вы добавляете строку (или строки), происходит автоматическое обновление статистики.
  • Если в таблице менее 500 строк, и вы добавляете более 500. Т.е. если у вас 499 строк, то вы должны добавить строки до 999, чтобы произошло автоматическое обновление.
  • Когда в таблице более 500 строк, вы должны добавить дополнительно 500 строк + 20% от размера таблицы, чтобы увидеть автоматическое обновление статистики.

Вы также можете обновлять статистику вручную. Для этого SQL Server предлагает два механизма. Во-первых, sp_updatestats. Эта процедура использует курсор для прохода по всей статистике в указанной базе данных. Она учитывает число модификаций строк, и если были выполнены какие-либо изменения, rowmodctr > 0, то обновляет статистику. Вы можете также обновить отдельную статистику с помощью UPDATE STATISTICS, указав имя. При этом вы можете задать использование FULL SCAN, чтобы гарантировать актуальную статистику, однако потребуется написание кода обслуживания, чтобы сделать это изменение постоянным.

Хватит говорить о том, что такое статистика. Давайте посмотрим на её представление и разберемся с данными, которые в ней содержатся.

DBCC SHOW_STATISTICS

  • Заголовок (Header): содержит метаданные о наборе статистики.
  • Плотность (Density): показывает значения плотности для столбца или столбцов, которые определяют набор статистики.
  • Гистограмма (Histogram): Таблица, которая определяет описанную выше гистограмму.

Информация заголовка может оказаться весьма полезной:

  • Updated: когда последний раз обновлялся этот набор статистики. Отсюда вы можете узнать о возрасте набора статистики. Если вы знаете, что в один из дней в таблицу были добавлены тысячи строк, но статистика относится к прошлой неделе, вы можете вручную обновить статистику.
  • Rows и Rows Sampled: Если эти значения совпадают, вы видите набор статистики, который является результатом полного сканирования. Если они различаются, то, вероятно, статистика получена на основе выборки.

Второй набор данных представляет собой меры плотности. Вот набор, который показывает плотность составного индекса:

Столбец All density содержит значение плотности, полученное по упомянутой выше формуле. Видно, что это значение уменьшается с каждым следующим столбцом. Ясно, что наиболее селективным является первый столбец. Можно также увидеть среднюю длину (Average Length) значений, которые содержатся в столбце, и, наконец, список столбцов, составляющих каждый уровень плотности.

Наконец, следующий график показывает раздел гистограммы:

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

Вы смотрите гистограмму, когда пытаетесь понять, почему план запроса SQL Server содержит scan или seek, в то время когда вы ожидаете увидеть что-то другое. Способ, которым распределяются данные, показывает, например, среднее число строк для заданного значения в пределах некоторого диапазона, что позволяет вам понять, насколько хорошо эти данные могут использоваться оптимизатором. Если вы наблюдаете большое расхождение между строками диапазона или уникальными строками диапазона, то, вероятно, ваша статистика устарела или построена на выборке. Кроме того, если диапазон и уникальные строки сильно расходятся при том, что у вас актуальная и точная статистика, это может говорить о серьезном перекосе данных, которые требуют других подходов к индексированию и построению статистики, например, фильтрованная статистика.

Заключение

Вы можете увидеть множество статистики, поддерживаемой SQL Server. Это жизненно важная часть достижения лучшей производительности системы. Понимание того, как она работает, создается, поддерживается и как её анализировать, поможет вам в работе с собственной статистикой.

Новое в SQL Server 2022: параметр AUTO_DROP для статистики

В SQL Server 2022 добавилась новая функция для статистики — AUTO_DROP. В этой статье мы расскажем, что она даёт и как её включать и выключать. Также будут представлены несколько примеров и показаны некоторые распространенные ошибки и способы их решения. Для демонстрационных примеров в этой статье мы будем использовать следующее:

  1. Установленный SQL Server 2022
  2. Развёрнута база данных AdventureWorks
  3. Установлена SSMS

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

Статистики — это объекты, содержащие информацию о распределении значений данных. SQL Server использует эту информацию при выборе плана исполнения запроса. Обновление статистики часто может повысить производительность запросов SQL Server. Как и наоборот, если статистика неактуальна, это может стать причиной снижения производительность запроса к таблице, где она устарела. Поэтому обновление устаревшей статистики является важной задачей обслуживания базы данных.

В SSMS при раскрытии дерева объектов таблицы можно увидеть папку «Statistics», а в ней статистики для этой таблицы.

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

select * from sys.stats

Вот пример результата её выполнения:

В SQL Server 2022 для административного представления sys.stats добавлен новый столбец с именем AUTO_DROP. Если AUTO_DROP равен 0 (ложь), это означает, что параметр отключен, а если 1 (истина), то значит включен.

Что такое настройка SQL Server AUTO_DROP?

До SQL Server 2022 параметра AUTO_DROP не было, поэтому обновление статистики могло блокировать изменение схемы. Давайте рассмотрим пример.

CREATE STATISTICS [myPasswordStats] ON [Person].[Password] (BusinessEntityID, [PasswordHash], [PasswordSalt]) WITH AUTO_DROP = ON;

Мы создали статистику myPasswordStats для таблицы Person.Password, в которой участвуют три столбца: (BusinessEntityID, PasswordHash, PasswordSalt). После этого, мы устанавливаем для параметра AUTO_DROP значение ON.

После создания, мы можем увидеть новую статистику в SSMS:

Щелкните правой кнопкой мыши по таблице, выберите пункт Design и удалите столбец PasswordHash.

Обратите внимание, что после этого статистика myPasswordStats тоже окажется удалённой:

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

Параметр AUTO_DROP недоступен до SQL Server 2022

Если вы запустите показанный ниже скрипт в SQL Server 2019 или более ранних версиях, сервер вернёт ошибку:

CREATE STATISTICS [myPasswordStats] ON [Person].[Password] (BusinessEntityID, [PasswordHash], [PasswordSalt]) WITH AUTO_DROP = ON;

Msg 155, Level 15, State 1, Line 4
‘AUTO_DROP’ is not a recognized CREATE STATISTICS option.

Параметр AUTO_DROP не распознается в более ранних версиях. Чтобы проверить версию SQL Server, перейдите по этой ссылке: Как узнать, какую версию SQL Server вы используете.

Удаление статистики без включения AUTO_DROP

Создадим статистику без параметра AUTO_DROP. Вот как её можно создать в базе данных SQL Server 2019.

CREATE STATISTICS [myPasswordStats] ON [Person].[Password] (BusinessEntityID, [PasswordHash], [PasswordSalt])

Теперь попробуем удалить столбец:

Вот сообщение об ошибке, которое мы в ответ получим:

‘Password (Person)’ table — Unable to modify table.
The statistics ‘myPasswordStats’ is dependent on column ‘PasswordHash’.
ALTER TABLE DROP COLUMN PasswordHash failed because one or more objects access this column.

Существует зависимость столбца от статистики, что не позволяет вносить такие изменения в таблицу.

Включение и отключение параметра AUTO_DROP

В этом примере показано, как отключить AUTO_DROP.

UPDATE STATISTICS [Person].[Password] [myPasswordStats] WITH AUTO_DROP = OFF;

Тут [Person].[Password] — имя таблицы, а myPasswordStats — имя статистики.

В этом примере показано, как включить настройку AUTO_DROP.

UPDATE STATISTICS [Person].[Password] [myPasswordStats] WITH AUTO_DROP = ON;
  • sql server
  • новое в sql server 2022

Почему для SQL Server важна статистика

За годы работы с SQL Server я обнаружила, что есть несколько тем, которые часто игнорируются. Их что боятся, думают, что они сложные или что они не такие важные. Также есть мнение, что эти знания не нужны, так как SQL Server «все делает за меня». Я слышала это об индексах. Я слышала это о статистике.

Итак, давайте поговорим, почему статистика важна и почему знание о том, что она важна, поможет вам существенно повлиять на производительность ваших запросов.

Есть несколько случаев, когда статистика обрабатывается автоматически:

  • SQL Server автоматически создает статистику для индексов
  • SQL Server автоматически создает статистику для столбцов, когда ему требуется больше информации для оптимизации запроса
    • ВАЖНО! Это происходит только тогда, когда включен параметр базы данных auto_create_statistics. Этот параметр включен по умолчанию во всех версиях и редакциях SQL Server. Однако иногда встречаются рекомендации его выключить. Я категорически против этого.
    • ВАЖНО! Это происходит только тогда, когда включен параметр базы данных auto_update_statistics. Этот параметр включен по умолчанию во всех версиях и редакциях SQL Server. Иногда также встречаются рекомендации его выключить. Обычно я против этого. Узнать больше об автоматическом обновлении вы можете в статье (англ.) Updating SQL Server Statistics Part I – Automatic Updates, а про ручное обновление статистики (но с помощью более избирательного подхода) в статье Updating SQL Server Statistics Part II – Scheduled Updates.

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

    Доступ к данным

    Обычно, когда вы отправляете запрос к SQL Server для получения данных, вы пишете код на Transact-SQL в виде простого SELECT или, возможно, в виде хранимой процедуры (да, есть и другие варианты). Однако главное в том, что вы говорите какой набор данных вы хотите получить, а не описываете то, как эти данные должны быть извлечены. Как же SQL Server “доберется“ до данных?

    Обработка данных

    В получении и обработке данных SQL Server’у помогает статистика. Статистика предоставляет информацию о том, какой объем данных нужно будет обработать при выполнении запроса. Если нужно будет обработать небольшой объем данных, то обработка может быть проще (возможно, другим способом), чем если бы запрос обрабатывал миллионы строк.

    В частности, SQL Server использует оптимизатор запросов на основе стоимости (cost based). Существуют и другие варианты оптимизации, но сегодня, чаще всего, используются оптимизаторы, основанные на стоимости. Почему? Оптимизаторы, основанные на стоимости, используют информацию о запрашиваемых данных, чтобы сформировать более эффективные, оптимальные и целенаправленные планы, с учетом информации об этих данных. Как правило, этот процесс работает хорошо. Хотя с планами, которые сохраняются для последующих выполнений (кэшированные планы), могут быть проблемы. Тем не менее, в других способах оптимизации есть еще более серьезные недостатки.

    ВАЖНО! Я здесь не говорю о кеше планов… Я говорю о начальном процессе оптимизации и о том, как SQL Server определяет, какой объем данных ему надо будет получить. Последующее выполнение кэшированного плана может привести к дополнительным проблемам с ним (известными как parameter sniffing). Об этом много написано в других статьях.

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

    Оптимизация на уровне синтаксиса

    SQL Server может обработать запрос, используя только его текст и не тратить время на поиск наилучшего порядка обработки таблиц. Оптимизатор может просто соединить (join) ваши таблицы в том порядке, в котором вы их указали во FROM. Хотя для того чтобы начать выполнять запрос не требуется никаких затрат, но выполнение самого запроса может быть далеко не оптимальным. В общем случае, соединение больших таблиц с маленькими менее оптимально, чем соединение маленьких таблиц с большими. Давайте посмотрим на эти два примера:

    USE [WideWorldImporters]; GO SET STATISTICS IO ON; GO SELECT [so].*, [li].* FROM [sales].[Orders] AS [so] JOIN [sales].[OrderLines] AS [li] ON [so].[OrderID] = [li].[OrderID] WHERE [so].[CustomerID] = 832 AND [so].[SalespersonPersonID] = 2 OPTION (FORCE ORDER); GO SELECT [so].*, [li].* FROM [sales].[OrderLines] AS [li] JOIN [sales].[Orders] AS [so] ON [so].[OrderID] = [li].[OrderID] WHERE [so].[CustomerID] = 832 AND [so].[SalespersonPersonID] = 2 OPTION (FORCE ORDER); GO

    Сравните стоимости планов. Второй запрос значительно дороже.

    Стоимость одинаковых запросов с FORCE ORDER с разным порядком соединения.

    Да, это чрезмерное упрощение способов оптимизации соединения, но дело в том, что вы вряд ли укажете самостоятельно оптимальный порядок таблиц во FROM. Хорошая новость в том, что если у вас есть проблемы с производительностью, то вы можете применить такие оптимизации, как соединение таблиц в указанном порядке (см. пример выше). Есть много других хинтов (hint), которые вы можете использовать:

    • QUERY Hints (принудительное использование уровня параллелизма [MAXDOP], оптимизация запроса для быстрого получения первых строк, а не всего набора данных с помощью FAST n и т.д.)
    • TABLE Hints (принудительное использование индекса [INDEX], поиска в индексе [FORCESEEK] и т.д.)
    • JOIN Hints (принудительное использование типа соединения LOOP / MERGE / HASH)

    Надо сказать, что использование хинтов всегда должно быть последним способом оптимизации. Следует попробовать другие способы оптимизации, прежде чем использовать хинты. Применяя их, вы не позволяете SQL Server оптимизировать ваши запросы при добавлении или изменении индексов, при добавлении или изменении статистики и при обновлении SQL Server. Конечно, если только вы не задокументировали используемые хинты и не протестировали их, чтобы убедиться, что они останутся полезными для вашего запроса после выполнения заданий по обслуживанию БД, модификации данных и обновлений SQL Server. Честно говоря, я не вижу, чтобы это делалось так часто, как хотелось бы. Большинство хинтов добавляются так, как будто они всегда будут работать отлично, и остаются в запросе до тех пор, пока не возникнут серьезные проблемы.

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

    Для проблем с конкретным запросом с его конкретными значениями стоит посмотреть:

    • Является ли статистика точной/актуальной? Исправит ли обновление статистики проблему?
    • Статистика основана на частичной выборке? Исправит ли FULLSCAN проблему?
    • Можете ли вы переписать запрос и улучшить план?
      • Есть ли плохие условия поиска? Столбцы всегда должны находиться с одной стороны выражения:
        • Так хорошо: MonthlySalary > expression / 12
        • Так плохо: MonthlySalary * 12 > expression

        FROM table2 AS t1
        JOIN table2 AS t2 ON t1.colX = t2.colX
        WHERE t1.colX = 12 AND t2.colX = 12

        • Иногда простой переход от соединения к подзапросу или от подзапроса к соединению исправляет проблему (нет, одно не всегда лучше другого, но иногда переписывание может помочь оптимизатору).
        • Иногда использование производных (derived) таблиц (подзапросов в FROM (. ) AS J1) может помочь оптимизатору более оптимально соединить таблицы.
        • Есть ли у вас условия OR? Можете ли вы переписать их через UNION или UNION ALL для получения такого же результата (это самое главное) с лучшим планом выполнения? Будьте осторожны, семантически это разные запросы. Вам нужно хорошо понимать различие между всеми ими.
          • OR удаляет дубликаты строк (на основе ID строки)
          • UNION удаляет дубликаты на основе столбцов, указанных в SELECT
          • UNION ALL объединяет множества (что может быть намного быстрее, чем удаление дубликатов), но это может быть (а может и не быть) проблемой:
            • иногда дубликатов нет (вы должны знать свои данные)
            • иногда допустимо вернуть дубликаты (вы должны знать ваших пользователей / аудиторию / приложение)

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

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

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

            Оптимизация с использованием правил (эвристика)

            Я упоминала, что оптимизация на основе стоимости требует статистики. Но что, если у вас нет статистики?

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

            Эвристика — это правила. Простые, статичные, фиксированные правила. Тот факт, что они простые, является их преимуществом. Не нужно смотреть на данные. Делается простая и быстрая оценка запроса на основе предикатов. Например, “меньше” и “больше” имеют внутреннее правило “30%”. Проще говоря, когда вы запускаете запрос с предикатом “больше” или “меньше” и нет информации о данных (статистики), SQL Server будет использовать правило, которое говорит, что условию будут соответствовать 30% данных. Оптимизатор будет использовать это в своих оценках и придумает план, соответствующий этому правилу.
            Чтобы это «заработало», нужно сначала отключить auto_create_statistics и проверить существующие индексы и статистику:

            USE [WideWorldImporters]; GO ALTER DATABASE [WideWorldImporters] SET AUTO_CREATE_STATISTICS OFF; GO EXEC sp_helpindex '[sales].[Customers]'; EXEC sp_helpstats '[sales].[Customers]', 'all'; GO

            Посмотрите sp_helpindex и sp_helpstats . В базе данных WideWorldImporters в таблице Customers на столбце DeliveryPostalCode по умолчанию нет индексов и статистики. Если вы добавили что-то самостоятельно (или SQL Server создал автоматически), то следует их удалить перед выполнением следующих примеров.

            Для первого запроса мы поставим ZipCode , равный 90248 и используем предикат “меньше”. Посмотрим как SQL Server оценит количество строк без использования статистики и возможности ее автоматического создания.

            SELECT [c1].[CustomerID], [c1].[CustomerName], [c1].[PostalCityID], [c1].[DeliveryPostalCode] FROM [sales].[Customers] AS [c1] WHERE [c1].[DeliveryPostalCode] < '90248';

            Столбцы без статистики будут использовать эвристику.

            Если не найти «идеальное» значение, то большую часть времени эти правила будут неправильными!

            Для первого запроса оценка работает хорошо (30% от 663 = 198,9), так как фактическое количество строк для запроса составляет 197. Один важный момент, на который стоит обратить внимание — это предупреждение рядом с таблицей Customers и около самого левого оператора SELECT. Оно говорит нам о том, что здесь что-то не так. Хотя, оценка количества строк «правильная».

            Для второго запроса мы возьмем значение ZipCode равное 90003. Запрос точно такой же, за исключением значения ZipCode. Как сейчас SQL Server оценит количество строк?

            SELECT [c1].[CustomerID], [c1].[CustomerName], [c1].[PostalCityID], [c1].[DeliveryPostalCode] FROM [sales].[Customers] AS [c1] WHERE [c1].[DeliveryPostalCode] < '90003';

            Столбцы без статистики используют эвристику (простые правила). Часто они сильно ошибаются!
            Для второго запроса оценка также равна 198,9, а фактически строк только 1. Почему? Потому что без статистики эвристика для “меньше” (и “больше”) составляет 30%. Тридцать процентов от 663 — это 198,9. Конкретное значение меняется при модификации данных, но процент остается постоянным 30%.

            Если этот запрос будет более сложным (с соединениями и/или дополнительными предикатами), то наличие некорректной информации — это уже проблема для последующих шагов оптимизации. Да, время от времени вам может везти с эвристиками, но это маловероятно. Более того эвристика для BETWEEN и “равно” отличается от значений для “меньше” и “больше” (равной равна 30%). На самом деле, некоторые из них даже меняются в зависимости от используемой вами модели оценки кардинальности (например, для “равно”). А меня, вообще, это должно беспокоить? На самом деле, нет! В действительности я никогда не хочу их использовать.

            Итак, SQL Server может использовать оптимизацию на основе правил… но только тогда, когда у него нет лучшей информации.

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

            Оптимизация на основе стоимости

            Что же на самом деле делает оптимизация на основе стоимости? Если кратко, то SQL Server быстро получает приблизительную оценку того, сколько данных будет обработано. Затем, используя эту информацию, он оценивает стоимость различных алгоритмов, которые могут быть использованы для доступа к данным. После этого, основываясь на “стоимости” этих алгоритмов, SQL Server выбирает тот, который, по его расчетам, является наименее дороги. Затем он его компилирует и выполняет.

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

            • Статистика может быть устаревшей
            • Статистика может быть не точной из-за ограниченной выборки
            • Статистика может быть не точной из-за размера таблицы и ограничений того, что храниться в ней.

            Некоторые спросят меня — может ли SQL Server иметь более подробную статистику (более подробные гистограммы и т.п.)? Да, может. Но тогда процесс чтения / доступа к этой, все большей и большей, статистике будет становиться все дороже (и занимать больше времени, больше кеша и т. д.). Что, в свою очередь, сделает процесс оптимизации более дорогим. Это сложная проблема. Везде есть плюсы и минусы, компромиссы. На самом деле, все не так просто, как “подробные гистограммы”.

            Наконец, в процессе оптимизации нельзя проанализировать все возможные комбинации планов. Это сделало бы сам процесс оптимизации настолько дорогим, что это было бы непозволительно!

            Итог

            Лучший способ думать о процессе оптимизации — это как найти “хороший план быстро”. Иначе процесс оптимизации стал бы настолько сложным и затянулся бы настолько, что не достиг бы своей цели!

            Итак, почему статистика очень важна:

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

              СТАТИСТИКА — КЛЮЧ К ЛУЧШЕЙ ОПТИМИЗАЦИИ И, СЛЕДОВАТЕЛЬНО, ЛУЧШЕЙ ПРОИЗВОДИТЕЛЬНОСТИ (но это все еще пока далеко от идеала!)

              Я надеюсь, что эта статья мотивирует вас изучить больше о статистике. Статистика в SQL Server на самом деле проще, чем вы думаете и, очевидно, она очень, очень важна!
              Довольно старая, но все еще полезная статья — Statistics Used by the Query Optimizer in Microsoft SQL Server 2008

              Похожие публикации:
              1. Elementary os что это
              2. Gnome shell themes как установить
              3. Как войти в бут меню на ноутбуке асус
              4. Как сменить регион в windows 11

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *