Работа со статистикой в Microsoft SQL Server. Часть 1 – Просмотр статистики

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

Как посмотреть статистику в Microsoft SQL Server. Инструкции и представления для работы со статистикой

Для начала напомню, что статистика – это статистические сведения о распределении значений в одном или нескольких столбцах таблицы, и эти сведения использует оптимизатор запросов Microsoft SQL Server для создания планов выполнения запросов, в частности для оценки кратности, т.е. числа строк в результатах запроса. Такая оценка кратности позволяет оптимизатору запросов создать оптимальный план выполнения запроса.

Примечание! Более подробно о том, что такое статистика в Microsoft SQL Server, для чего она нужна и из чего состоит, мы говорили в отдельном материале – Статистика в Microsoft SQL Server, поэтому перед тем как продолжить чтение данной статьи, я рекомендую прочитать этот материал.

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

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

Сейчас мы рассмотрим основные способы, с помощью которых можно посмотреть статистику в Microsoft SQL Server.

DBCC SHOW_STATISTICS

К числу основных инструментов, которые позволяют просматривать статистку, безусловно относится инструкция DBCC SHOW_STATISTICS.

DBCC SHOW_STATISTICS – это инструкция, которая отображает статистику оптимизации текущего запроса для таблицы или индексированного представления.

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

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

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

Синтаксис инструкции DBCC SHOW_STATISTICS

   
   DBCC SHOW_STATISTICS ( table_name, target )
       WITH option;

где:

  • DBCC SHOW_STATISTICS – инструкция для просмотра статистики;
  • table_name – имя таблицы или индексированного представления, для которого необходимо отобразить статистику;
  • target – имя индекса, статистики или столбца, для которого необходимо отобразить статистику. Если данный параметр является именем существующего индекса или статистики, то будут возвращены статистические данные об этом объекте, а если параметр представляет собой имя существующего столбца, при этом имеется статистика, автоматически созданная для этого столбца, то возвращаются сведения об этой автоматически созданной статистике, но, если автоматически созданная статистика для целевого столбца отсутствует, возвращается сообщение об ошибке 2767;
  • WITH – ключевое слово для перечисления параметров. Если предполагается запуск инструкции без параметров, указание данного ключевого слова не требуется и в данном случае будут возвращены все статистические сведения, т.е. все результирующие наборы данных;
  • option – параметры, которые позволяют ограничивать результирующие наборы, возвращаемые инструкцией. Можно указать следующие параметры (при этом возможно указывать несколько параметров через запятую):
    • NO_INFOMSGS – подавляет все информационные сообщения со степенями серьезности от 0 до 10;
    • STAT_HEADER – если указать данный параметр, то будет возвращен результирующий набор, содержащий заголовок статистики;
    • DENSITY_VECTOR – если указать данный параметр, то будет возвращен результирующий набор, содержащий вектор плотностей;
    • HISTOGRAM – если указать данный параметр, то будет возвращен результирующий набор, содержащий гистограмму статистики.

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

Пример запуска инструкции DBCC SHOW_STATISTICS

В данном случае показан пример вывода информации о статистике некластеризованного индекса IX_Person_LastName_FirstName, который создан для столбцов LastName и FirstName таблицы Person в схеме Person, при этом выводится только заголовок и гистограмма, так как указаны соответствующие параметры (WITH STAT_HEADER, HISTOGRAM).

   
   DBCC SHOW_STATISTICS (N'Person.Person', N'IX_Person_LastName_FirstName') WITH STAT_HEADER, HISTOGRAM;

Скриншот 1

Графический интерфейс SQL Server Management Studio

Информацию о статистике можно также посмотреть с помощью графического интерфейса SSMS, для этого в обозревателе объектов необходимо открыть контейнер «Таблицы -> Имя таблицы -> Статистика»

Скриншот 2

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

Скриншот 3

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

sys.stats

Посмотреть, какая статистика создана для конкретной таблицы, включая основные параметры этой статистики, такие как: идентификатор объекта, которому принадлежит статистика, имя и идентификатор статистики, признак того, была ли статистика создана автоматически или она была создана пользователем, а также имеет ли статистика фильтр или она рассчитывается для всех строк, можно с помощью системного представления sys.stats.

В примере ниже выводится информация о том, какая статистика имеется у таблицы Person.

   
   SELECT *
   FROM sys.stats
   WHERE object_id = OBJECT_ID(N'Person.Person');

Скриншот 4

sys.stats_columns

С помощью данного системного представления можно посмотреть, какие столбцы являются частью статистики. Представление возвращает по одной строке для каждого столбца, который является частью статистики, возвращаемой представлением sys.stats.

sys.stats_columns возвращает следующие столбцы:

Имя столбца Описание
object_id Идентификатор объекта, частью которого является этот столбец.
stats_id Идентификатор статистики, частью которой является этот столбец. Если статистика создана для индекса, то значение совпадает со значением index_id из представления sys.indexes
stats_column_id Порядковый номер столбца от 1 в пределах набора столбцов статистики.
column_id Идентификатор столбца из представления sys.columns.

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

В следующем примере выводятся столбцы (в частности имя и порядковый номер столбца в объекте статистики), для которых создана статистика с именем «IX_Person_LastName_FirstName_MiddleName» у таблицы Person.

   
   SELECT c.name AS column_name,
          sc.stats_column_id AS column_number
   FROM sys.stats AS s
   INNER JOIN sys.stats_columns AS sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
   INNER JOIN sys.columns AS c ON sc.object_id = c.object_id AND c.column_id = sc.column_id
   WHERE sc.object_id = OBJECT_ID('Person.Person')
     AND s.name = 'IX_Person_LastName_FirstName_MiddleName'
   ORDER BY s.object_id, s.stats_id, sc.stats_column_id;

Скриншот 5

sys.dm_db_stats_properties

В Microsoft SQL Server 2012 появилась DMV sys.dm_db_stats_properties, которая позволяет программно получать свойства статистики для указанного объекта базы данных, иными словами, она выводит заголовок статистики (какие сведения включаются в заголовок статистики, мы подробно рассмотрели в уже упомянутой статье – Статистика в Microsoft SQL Server).

Функция принимает два параметра:

  • object_id – идентификатор объекта текущей базы данных, для которого запрашиваются статистические свойства;
  • stats_id – идентификатор статистики для указанного объекта. Данный идентификатор может быть получен из динамического административного представления sys.stats.

Примечание! sys.dm_db_stats_properties возвращает пустой результирующий набор, если object_id или stats_id имеет значение null, или указанный объект не найден, а также, если пользователь не имеет разрешений на просмотр объекта статистики.

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

   
   SELECT s.name AS stat_name,
          p.last_updated,
          p.rows,
          p.rows_sampled,
          p.steps,
          p.unfiltered_rows
   FROM sys.stats AS s
   CROSS APPlY sys.dm_db_stats_properties (s.object_id, s.stats_id) AS p
   WHERE s.object_id = OBJECT_ID('Person.Person')
   ORDER BY s.object_id, s.stats_id;

Скриншот 6

Заметка! Список и расшифровка версий Microsoft SQL Server.

sys.dm_db_stats_histogram

В SQL Server 2016 компания Microsoft добавила DMV sys.dm_db_stats_histogram, с помощью которой можно получить данные гистограммы, содержащиеся в объекте статистики. Работает она аналогично sys.dm_db_stats_properties, т.е. на вход ей необходимо передать два параметра: object_id (идентификатор объекта) и stats_id (идентификатор статистики).

Примечание! Какие сведения содержит гистограмма статистики, мы подробно рассмотрели в материале – Статистика в Microsoft SQL Server. При этом sys.dm_db_stats_histogram дополнительно возвращает еще и object_id, stats_id и step_number.

Следующий запрос возвращает гистограмму статистики с именем IX_Person_LastName_FirstName_MiddleName.

   
   SELECT h.*
   FROM sys.stats AS s
   CROSS APPLY sys.dm_db_stats_histogram(s.object_id, s.stats_id) AS h
   WHERE s.name = N'IX_Person_LastName_FirstName_MiddleName';

Скриншот 7

STATS_DATE

STATS_DATE – системная функция, которая возвращает дату последнего обновления статистики для таблицы или индексированного представления.

Функция принимает два параметра:

  • object_id – идентификатор таблицы или индексированного представления, содержащего статистику;
  • stats_id – идентификатор объекта статистики.

В следующем примере возвращается дата последнего обновления для каждого объекта статистики по таблице Person.

   
   SELECT name AS stats_name,
          STATS_DATE(object_id, stats_id) AS stats_update_date
   FROM sys.stats
   WHERE object_id = OBJECT_ID('Person.Person');

Скриншот 8

Заметка! Чем отличаются функции от хранимых процедур в T-SQL.

sp_autostats

sp_autostats – системная хранимая процедура, которая отображает или изменяет параметр автоматического обновления статистики AUTO_UPDATE_STATISTICS для индекса, объекта статистики, таблицы или индексированного представления.

Может принимать три параметра:

  • @tblname – имя таблицы или индексированного представления для отображения параметра AUTO_UPDATE_STATISTICS. Не имеет значения по умолчанию;
  • @flagc – обновляет параметр AUTO_UPDATE_STATISTICS. Если данный параметр не указан, отобразится текущее значение параметра AUTO_UPDATE_STATISTICS. Значение по умолчанию NULL:
    • ON – включить;
    • OFF – выключить.
  • @indname – имя статистики для вывода или обновления параметра AUTO_UPDATE_STATISTICS. Чтобы отобразить статистику для индекса, можно использовать имя индекса. Имя индекса совпадает с именем соответствующего объекта статистики. Значение по умолчанию NULL.

Если процедура запущена на просмотр текущего значения параметра AUTO_UPDATE_STATISTICS, то она возвращает следующие данные:

Имя столбца Описание
Index Name Имя индекса или статистики.
AUTOSTATS Текущее значение параметра AUTO_UPDATE_STATISTICS.
Last Update Дата последнего обновления статистики.

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

Следующая SQL инструкция выводит информацию по состоянию статистики по таблице Person.

   
   EXEC sp_autostats 'Person.Person';

Скриншот 9

В данном случае инструкция включает параметр AUTO_UPDATE_STATISTICS для всей статистики по таблице Person.

   
   EXEC sp_autostats 'Person.Person', 'ON';

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

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

Удачи Вам, пока!

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Комментарии: 1
  1. Ольга

    Супер!!! Благодарю Вас за великолепно изложенный материал!

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

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.