Вы когда-нибудь задавались вопросом, почему статистика не обновляется в вашей базе данных SQL Server, даже если вы выполнили множество вставок? В этой статье мы рассмотрим причины этой проблемы и обсудим, как ее исправить.
При создании некластеризованного индекса на столбце в SQL Server автоматически создается статистика для этого столбца. Эта статистика помогает оптимизатору запросов принимать обоснованные решения о наиболее эффективном способе выполнения запросов. Однако статистика не всегда обновляется автоматически, что может привести к устаревшим планам выполнения и неоптимальной производительности запросов.
Давайте рассмотрим пример, чтобы понять это поведение:
-- Создание примера базы данных
CREATE DATABASE SampleDB
GO
USE SampleDB
GO
-- Создание таблицы
CREATE TABLE ExecTable (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100))
GO
-- Создание некластеризованного индекса на столбце City
CREATE NONCLUSTERED INDEX IX_ExecTable1
ON ExecTable (City);
GO
-- Вставка тысячи записей
INSERT INTO ExecTable (ID,FirstName,LastName,City)
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 3 THEN 'Los Angeles'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 7 THEN 'La Cinega'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 13 THEN 'San Diego'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 17 THEN 'Las Vegas'
ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Отображение статистики таблицы
sp_helpstats N'ExecTable', 'ALL'
GO
-- Выборка данных
SELECT FirstName, LastName, City
FROM ExecTable
WHERE City = 'New York'
GO
-- Отображение статистики таблицы
sp_helpstats N'ExecTable', 'ALL'
GO
-- Очистка базы данных
DROP TABLE ExecTable
GO
В этом примере мы создаем некластеризованный индекс на столбце “City” таблицы “ExecTable”. Это автоматически создает статистику для этого столбца. Затем мы вставляем 1000 записей в таблицу и отображаем статистику. На этом этапе статистика актуальна.
Однако, когда мы вставляем еще 1000 записей в таблицу и выполняем ту же самую выборку, мы обнаруживаем, что статистика не обновляется. Это происходит потому, что план выполнения повторно используется, и оптимизатор запросов не видит необходимости обновлять статистику.
Чтобы исправить эту проблему, мы можем создать некластеризованный индекс на столбце “City”. Это приведет к обновлению индекса, что в свою очередь вызовет обновление статистики. Вот обновленный код:
-- Создание некластеризованного индекса на столбце City
CREATE NONCLUSTERED INDEX IX_ExecTable1
ON ExecTable (City);
GO
Создав некластеризованный индекс, мы гарантируем, что статистика будет обновляться при обновлении индекса. Это приводит к более точным планам выполнения и улучшенной производительности запросов.
Важно отметить, что такое поведение не происходит для куч или столбцов, где индекс создается автоматически. В этих случаях вам может потребоваться явно обновить индекс, чтобы обновить статистику.
Хотя очистка кэша планов выполнения или повторная компиляция каждого запроса также может обновить статистику, это не рекомендуется для серверов в производственной среде. Эти методы следует использовать только для целей устранения неполадок.
В заключение, понимание статистики SQL Server и способов их обновления является важным для оптимизации производительности запросов. Создавая некластеризованные индексы на соответствующих столбцах, вы можете гарантировать автоматическое обновление статистики, что приводит к более эффективным планам выполнения.
Если у вас есть какие-либо предложения или лучшие решения для обновления статистики в SQL Server, пожалуйста, не стесняйтесь делиться ими. Мы всегда открыты для улучшения наших решений и предоставления bewt практик для администрирования SQL Server.
Спасибо за чтение!