Распределение статистики
Индексы не выбираются на основании анализа распределенной статистики. В SQL Server 2000 статистика хранится в поле данных для хранения имиджей. То есть – растет пропорционально размеру индекса, это позволяет не терять точность при увеличении размера индексов.
Что бы понять что из себя представляет статистика, рассмотрим следующий пример. Возьмем таблицу Orders из базы данных Northwind. Если мы выполним следующий запрос:
То получим следующий результат:
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. Теперь посчитаем сколько раз встречаются те или иные значения:
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 считает или сортирует данные, он заранее знает как много тех или иных значений он найдет в указанном запросе. Например, выполним следующий запрос:
Выполним команду
Updated Rows Rows_Sampled Steps Density Average key length
------------------------------------------------------------------------------------------------------------------------------
Dec 23 2002 9:30AM 830 830 187 1.6842016E-3 12.0
Этот результат показывает:
Например если колонка содержит только 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. Для просмотра таких статистик нужно использовать следующую инструкцию:
Обновление статистики может выполняться синхронно (режим по умолчанию AUTO_CREATE_STATISTICS) или асинхронно (AUTO_UPDATE_STATISTICS_ASYNC). При синхронном обновлении статистики запросы всегда компилируются и выполняются с актуальной статистикой. Если статистика оказывается устаревшей, то оптимизатор запросов ожидает появления обновленной статистики перед компиляцией и выполнением запроса. При асинхронном обновлении статистики запросы компилируются с существующей статистикой, даже если она устарела. Если на момент компиляции запроса статистика оказывается устаревшей, то оптимизатор запросов может выбрать неоптимальный план запроса. Запросы, которые компилируются после выполнения асинхронного обновления, будут усовершенствованы благодаря использованию обновленной статистики.
Параметр AUTO_UPDATE_STATISTICS_ASYNC, который управляет асинхронным обновлением статистики на уровне базы данных, определяет, какой режим обновления статистики использует оптимизатор запросов, синхронный или асинхронный. По умолчанию параметр асинхронного обновления статистики отключен, и оптимизатор запросов обновляет статистику синхронно. Параметр AUTO_UPDATE_STATISTICS_ASYNC применяется к объектам статистики, создаваемым для индексов, отдельных столбцов в предикатах запросов и к статистике, создаваемой инструкцией CREATE STATISTICS.
Чтобы просмотреть параметр автоматического асинхронного обновления для всех баз данных, можно использовать следующую команду:
Что бы понять что из себя представляет статистика, рассмотрим следующий пример. Возьмем таблицу 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.orderdateOrderDate # 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
Этот результат показывает:
- статистика была просчитана в последний раз 23 декабря 2002
- таблица содержит 830 записей
- все записи были проанализированы для получения статистики
- в статистике информация сохранена в дискретности на 187 записей
- средняя плотность распределения примерно 0.17%
Например если колонка содержит только 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
Прочли: 3696 | Изменено: 09 сентября 2011 | Добавлено: 21 апреля 2011 | На главную