Статьи

Все о статистике [Часть I]

Апрель 1, 2018

Вступление

Я получил запрос от одного из моих друзей, спрашивающий о том, когда использовать sp_updatestats и как часто его использовать. Чтобы ответить на его запрос, я решил написать несколько слов, связанных со статистикой. Я надеюсь, что это будет информативным для всех нас.

Использование объектов статистики

Все мы знаем о важности объекта статистики для составления эффективного плана выполнения оптимизатором. Если объект статистики не обновляется должным образом, это приводит к плохому плану выполнения и снижению производительности запроса.

Когда создан объект статистики

Когда мы создаем Индекс, объект Статистика создается автоматически и называется Статистикой Индекса . Эта статистика будет существовать до тех пор, пока существует индекс.

Во-вторых, при условии, что включена опция базы данных «Автоматическое создание статистики», которая по умолчанию включена, SQL Server будет создавать статистику в один столбец всякий раз, когда в предикате запроса используется столбец, который не является ведущим столбцом в существующем индексе (например, в условии поиска предложения WHERE или в условии JOIN). Мы называем это  статистикой столбцов . Мы также можем использовать команду CREATE STATISTICS для создания статистики из одного или нескольких столбцов вручную.

Когда обновляется объект статистики

Когда мы вставляем / обновляем / удаляем записи из объектов таблицы, SQL Server автоматически вставляет / обновляет / удаляет соответствующие строки в индексе.

 Но статистика не обновляется таким образом, здесь мы имеем в виду, что когда мы вставляем / обновляем / удаляем записи из объектов таблицы, индекс обновляется автоматически, а статистика — нет. Хотя опция Автообновление статистики базы данных включена.

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

По мере изменения данных в наших таблицах статистика — вся статистика — будет обновляться на основе следующей формулы:

·   Когда таблица без строк получает строку

·   Когда 500 строк заменены на таблицу, которая меньше 500 строк

·   При изменении 20% + 500 в таблице, содержащей более 500 строк

Каждый раз, когда мы изменяем запись в таблице, SQL Server отслеживает ее через столбец rcmodified в скрытой системной таблице. SQL Server 2005 отслеживал эту информацию в таблице sys.rowsetcolumns. В SQL Server 2008 (и более поздних версиях) sys.rowsetcolumns объединился с sys.syshobtcolumns и стал sys.sysrscols.

Когда мы создаем или перестраиваем (не реорганизуем, просто перестраиваем) индекс, SQL Server генерирует статистику с помощью FULLSCAN, то есть сканирует все строки таблицы, чтобы создать гистограмму, которая представляет распределение данных в ведущем столбце индекса. , Аналогично, SQL будет автоматически создавать статистику столбцов с полной выборкой.

Когда обновлять статистику вручную

Предположим, у нас есть объекты таблиц из 100 миллионов записей, и SQL-сервер собирается обновить объекты статистики, когда 20% записей из 100 миллионов записей выполняется с помощью вставки / обновления / удаления. Таким образом, нам приходится долго ждать обновления объекта статистики, и результатом является плохое создание плана выкупа сервером SQL. В этой ситуации мы должны обновить объект статистики вручную.

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

Изучение статистики

 sp_helpstats 'tbl_CUSTOMERDTLS', 'ALL'

statistics_name                     statistics_keys
---------------                   ----------------
_WA_Sys_00000001_03BB8E22           CUSTID
_WA_Sys_00000002_03BB8E22           CUSTNAME
_WA_Sys_00000003_03BB8E22           TOTALSALES
_WA_Sys_00000004_03BB8E22           GRADE
IX_CUSTID_tbl_CUSTOMERDTLS          CUSTID

Лучший подход

 SELECT  [sch].[name] + '.' + [so].[name] AS [TableName] ,
        [si].[index_id] AS [Index ID] ,
        [ss].[name] AS [Statistic] ,
        STUFF(( SELECT  ', ' + [c].[name]
                FROM    [sys].[stats_columns] [sc]
                        JOIN [sys].[columns] [c]
                         ON [c].[column_id] = [sc].[column_id]
                            AND [c].[object_id] = [sc].[OBJECT_ID]
                WHERE   [sc].[object_id] = [ss].[object_id]
                        AND [sc].[stats_id] = [ss].[stats_id]
                ORDER BY [sc].[stats_column_id]
              FOR
                XML PATH('')
              ), 1, 2, '') AS [ColumnsInStatistic] ,
        [ss].[auto_Created] AS [WasAutoCreated] ,
        [ss].[user_created] AS [WasUserCreated] ,
        [ss].[has_filter] AS [IsFiltered] ,
        [ss].[filter_definition] AS [FilterDefinition] ,
        [ss].[is_temporary] AS [IsTemporary]
FROM    [sys].[stats] [ss]
        JOIN [sys].[objects] AS [so] ON [ss].[object_id] = [so].[object_id]
        JOIN [sys].[schemas] AS [sch] ON [so].[schema_id] = [sch].[schema_id]
        LEFT OUTER JOIN [sys].[indexes] AS [si]
              ON [so].[object_id] = [si].[object_id]
                 AND [ss].[name] = [si].[name]
WHERE   [so].[object_id] = OBJECT_ID(N'tbl_CUSTOMERDTLS')
ORDER BY [ss].[user_created] ,
        [ss].[auto_created] ,
        [ss].[has_filter];
GO

В следующей версии мы поговорим об этом подробнее.

Надеюсь, вам понравится.

39
Share: