Вступление
Я получил запрос от одного из моих друзей, спрашивающий о том, когда использовать 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
В следующей версии мы поговорим об этом подробнее.
Надеюсь, вам понравится.