При работе с SQL Server важно понимать, как происходит выборка статистики и как это может повлиять на производительность запросов. В этой статье мы рассмотрим поведение выборки статистики по умолчанию в SQL Server и как это может повлиять на точность результатов запросов.
По умолчанию SQL Server выбирает статистику при ее создании или обновлении. Пользователь может указать коэффициент выборки, который может варьироваться от очень низкого значения до 100%. Давайте проведем небольшой эксперимент, чтобы проверить, включена ли автоматическая обновление статистики, и изучить поведение выборки на большой таблице.
USE [AdventureWorks]
GO
-- Создание таблицы
CREATE TABLE [dbo].[StatsTest] (
[ID] [int] IDENTITY (1, 1) NOT NULL,
[FirstName] [varchar] (100) NULL,
[LastName] [varchar] (100) NULL,
[City] [varchar] (100) NULL,
CONSTRAINT [PK_StatsTest] PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]
GO
-- Вставка 1 миллиона строк
INSERT INTO [dbo].[StatsTest] (FirstName, LastName, City)
SELECT TOP 1000000 'Боб',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name) % 2 = 1 THEN 'Смит' ELSE 'Браун' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name) % 10 = 1 THEN 'Нью-Йорк'
WHEN ROW_NUMBER() OVER (ORDER BY a.name) % 10 = 5 THEN 'Сан-Марино'
WHEN ROW_NUMBER() OVER (ORDER BY a.name) % 10 = 3 THEN 'Лос-Анджелес'
ELSE 'Хьюстон' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Обновление статистики
UPDATE STATISTICS [dbo].[StatsTest]
GO
-- Показать статистику
DBCC SHOW_STATISTICS ("StatsTest", PK_StatsTest)
GO
-- Очистка
DROP TABLE [dbo].[StatsTest]
GO
Из результата команды DBCC SHOW_STATISTICS видно, что выборка результата производится для большого набора данных. Процент выборки зависит от распределения данных и типа данных в таблице. Перед удалением таблицы давайте проверим ее размер, который составляет 35 МБ.
Теперь давайте запустим тот же код с меньшим количеством строк:
USE [AdventureWorks]
GO
-- Создание таблицы
CREATE TABLE [dbo].[StatsTest] (
[ID] [int] IDENTITY (1, 1) NOT NULL,
[FirstName] [varchar] (100) NULL,
[LastName] [varchar] (100) NULL,
[City] [varchar] (100) NULL,
CONSTRAINT [PK_StatsTest] PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]
GO
-- Вставка 100 тысяч строк
INSERT INTO [dbo].[StatsTest] (FirstName, LastName, City)
SELECT TOP 100000 'Боб',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name) % 2 = 1 THEN 'Смит' ELSE 'Браун' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name) % 10 = 1 THEN 'Нью-Йорк'
WHEN ROW_NUMBER() OVER (ORDER BY a.name) % 10 = 5 THEN 'Сан-Марино'
WHEN ROW_NUMBER() OVER (ORDER BY a.name) % 10 = 3 THEN 'Лос-Анджелес'
ELSE 'Хьюстон' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Обновление статистики
UPDATE STATISTICS [dbo].[StatsTest]
GO
-- Показать статистику
DBCC SHOW_STATISTICS ("StatsTest", PK_StatsTest)
GO
-- Очистка
DROP TABLE [dbo].[StatsTest]
GO
В этом случае коэффициент выборки равен 100%, так как количество выбранных строк совпадает с общим количеством строк в таблице. Размер таблицы составляет менее 4 МБ.
Давайте сравним результаты для справки:
Тест 1: Всего строк: 1 000 000, Выбранных строк: 255 420, Размер таблицы: 35,516 МБ
Тест 2: Всего строк: 100 000, Выбранных строк: 100 000, Размер таблицы: 3,555 МБ
Причина выборки в Тесте 1 заключается в том, что объем данных больше 8 МБ и используется более 1024 страниц данных. Если объем данных меньше 8 МБ и используется менее 1024 страниц данных, то выборка не происходит (или происходит на 100%). Выборка помогает сократить избыточное сканирование данных, но может также снизить точность данных.
Обратите внимание, что это всего лишь тестовый пример и не может быть использован в качестве эталонного теста. Результаты могут отличаться на разных компьютерах.
Есть еще многое, что можно узнать о статистике SQL Server и ее влиянии на производительность запросов. В будущем посте мы рассмотрим эту тему более подробно. Если у вас есть ощущение, что что-то упущено или есть вопросы, пожалуйста, не стесняйтесь добавить их в раздел комментариев.