Распределение статистики

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

Что бы понять что из себя представляет статистика, рассмотрим следующий пример. Возьмем таблицу Orders из базы данных Northwind. Если мы выполним следующий запрос:

SELECT TOP 24 OrderID, convert(char(11), OrderDate)
FROM Orders
ORDER BY OrderDate

То получим следующий результат:

OrderID     OrderDate

10248       Jul 4 1996
10249       Jul 5 1996
10250       Jul 8 1996
10251       Jul 8 1996
10252       Jul 9 1996
10253       Jul 10 1996
10254       Jul 11 1996
10255       Jul 12 1996
10256       Jul 15 1996
10257       Jul 16 1996
10258       Jul 17 1996
10259       Jul 18 1996
10260       Jul 19 1996
10261       Jul 19 1996
10262       Jul 22 1996
10263       Jul 23 1996
10264       Jul 24 1996
10265       Jul 25 1996
10266       Jul 26 1996
10267       Jul 29 1996
10268       Jul 30 1996
10269       Jul 31 1996
10270       Aug 1 1996
10271       Aug 1 1996

В этом примере выбраны записи с 4 июля 1996 по 1 августа 1996. Теперь посчитаем сколько раз встречаются те или иные значения:

SELECT convert(char(11), A.orderdate) as OrderDate , count(*) as '# of OrderDate'
FROM       (
                 SELECT TOP 24 OrderID, OrderDate
                 FROM Orders
                 ORDER BY OrderDate
                ) as A
GROUP BY A.orderdate

OrderDate              # of OrderDate'

Jul 4 1996                       1
Jul 5 1996                       1
Jul 8 1996                       2
Jul 9 1996                       1
Jul 10 1996                     1
Jul 11 1996                     1
Jul 12 1996                     1
Jul 15 1996                     1
Jul 16 1996                     1
Jul 17 1996                     1
Jul 18 1996                     1
Jul 19 1996                     2
Jul 22 1996                     1
Jul 23 1996                     1
Jul 24 1996                     1
Jul 25 1996                     1
Jul 26 1996                     1
Jul 29 1996                     1
Jul 30 1996                     1
Jul 31 1996                     1
Aug 1 1996                     2

Когда SQL Server считает или сортирует данные, он заранее знает как много тех или иных значений он найдет в указанном запросе. Например, выполним следующий запрос:

SELECT * 
FROM Orders
WHERE OrderDate BETWEEN ‘1996-07-15’ AND ‘1996-07-20’

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

Выполним команду

dbcc show_statistics (Orders,OrderDate)

Statistics for INDEX 'OrderDate'.

Updated                      Rows   Rows_Sampled        Steps                      Density           Average key length
------------------------------------------------------------------------------------------------------------------------------
Dec 23 2002 9:30AM   830           830                  187                  1.6842016E-3            12.0

Этот результат показывает:

1. статистика была просчитана в последний раз 23 декабря 2002
   
2. таблица содержит 830 записей
   
3. все записи были проанализированы для получения статистики
   
4. в статистике информация сохранена в дискретности на 187 записей
   
5. средняя плотность распределения примерно 0.17%

Пожалуй самым интересным здесь будет значение плотности распределения(Density - это значение плотности не используется оптимизатором запросов в SQL Server 2008 и отображается для обратной совместимости с версиями, выпущенными до SQL Server 2008)). Если каждое значение в таблице уникальное, то плотность будет 1/830, то есть 0.12%. Но в нашем примере мы имеем 0.17% показывает что некоторые значения встречаются 2 и более раз. Например мы видим что для 8 июля 1996 встречается дважды. Теория говорит, что чем меньше плотность, тем лучше – это увеличивает избирательность, а следовательно и ценность построенного индекса.

Например если колонка содержит только 3 значения, плотность распределения будет равна 33.3%, что показывает бесполезность построения индекса по данному полю. Индексы занимают место на диске и в оперативной памяти и отнимает быстродействие. В идеале самый лучший индекс имеет плотность распределения равную единице, деленной на количество записей в таблице - все записи уникальны. При построении индексов, обращайте внимание на плотность распределения – если она превышает 10%, то индекс можно считать бесполезным. Сканирование по таблице в таком случае будет более эффективным.

Второй результат, возвращаемый командой dbcc show_statistics

All density       Average Length      Columns
----------------------------------------------------------------
2.0833334E-3      8.0                 OrderDate
1.2048193E-3      12.0                OrderDate, OrderID

Это дает очень интересный результат потому что плотность(All density) для одного поля OrderDate 0.2%, а для пары OrderDate, OrderID уже 0.12%. Поскольку OrderID является primary key для таблице, то понижение плотности вполне очевидно.

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

RANGE_HI_KEY   RANGE_ROWS        EQ_ROWS      DISTINCT_RANGE_ROWS
1996-07-04                  0.0                     1.0                       0
1996-07-15                  7.0                     1.0                       6
1996-07-19                  3.0                     2.0                       3
1996-07-25                  3.0                     1.0                       3
1996-08-01                  4.0                     2.0                       4

Во-первых, заметим что только пять значений в зоне распределения вместо 24 в самой таблице. Тем не менее учитывая значения по всем колонками, система знает сколько записей будет в выборке. Колонка RANGE_HI_KEY дает высшее значение для значения, сохраненного в статистике. Мы знаем что 1996-07-04 является первым значением и следующим за ним идет 1996-07-15. Между этими двумя значениями находится 7 записей. Колонка RANGE_ROWS дает нам эту информацию. Только три значения есть между 1996-07-15 и 1996-07-19, и так далее. Колонка DISTINCT_RANGE_ROWS содержит информацию о том сколько определенных (неповторяющихся) значений в интервале. Например в интервале с 1996-07-04 по 1996-07-15 есть 6 определенных значений из 7 записей в интервале. Это говорит что одно значение в указанном интервале повторяется дважды. Мы не указали еще одну колонку в результате команды dbcc show_statistics - AVG_RANGE_ROWS, которое является результатом простой арифметической операции RANGE_ROWS /DISTINCT_RANGE_ROWS.

Создание статистики можно включить в автоматический режим на уровне базы данных. Параметры: AUTO_CREATE_STATISTICS, AUTO_UPDATE_STATISTICS, AUTO_UPDATE_STATISTICS_ASYNC.Тогда оптимизатор запросов в случае необходимости создает статистику по отдельным столбцам в предикате запроса, чтобы улучшить оценку количества элементов для плана запроса. Если оптимизатор запросов создает статистику по отдельным столбцам в результате использования параметра AUTO_CREATE_STATISTICS, то имя статистики будет начинаться с _WA. Для просмотра таких статистик нужно использовать следующую инструкцию:

SELECT OBJECT_NAME(s.object_id) AS object_name,
    COL_NAME(sc.object_id, sc.column_id) AS column_name,
    s.name AS statistics_name
FROM sys.stats AS s Join sys.stats_columns AS sc
    ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name like '_WA%'
ORDER BY object_name;

Следующий запрос отобразит более детально статистики по отдельной таблице:

DECLARE @tbl nvarchar(265)
SELECT @tbl = 'Имя таблицы'

SELECT o.name, s.stats_id, s.name, s.auto_created, s.user_created,
       substring(scols.cols, 3, len(scols.cols)) AS stat_cols,
       stats_date(o.object_id, s.stats_id) AS stats_date,
       s.filter_definition
FROM   sys.objects o
JOIN   sys.stats s ON s.object_id = o.object_id
CROSS  APPLY (SELECT ', ' + c.name
              FROM   sys.stats_columns sc
              JOIN   sys.columns c ON sc.object_id = c.object_id
                                  AND sc.column_id = c.column_id
              WHERE  sc.object_id = s.object_id
                AND  sc.stats_id  = s.stats_id
              ORDER  BY sc.stats_column_id
              FOR XML PATH('')) AS scols(cols)
WHERE  o.name = @tbl
ORDER  BY o.name, s.stats_id

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

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

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

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

SELECT name AS "Name", 
    is_auto_update_stats_async_on AS "Asynchronous Update" 
FROM sys.databases;
GO

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

SELECT name AS "Name", 
    is_auto_create_stats_on AS "Auto Create Stats",
    is_auto_update_stats_on AS "Auto Update Stats",
    is_read_only AS "Read Only" 
FROM sys.databases
WHERE database_ID > 4;
GO

В следующем примере для базы данных AdventureWorks2008R2 параметры AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS устанавливаются в значение ON:

USE AdventureWorks2008R2;
GO
ALTER DATABASE AdventureWorks2008R2
    SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE AdventureWorks2008R2
    SET AUTO_UPDATE_STATISTICS ON;
GO