Секционирование таблиц и время компиляции плана запроса в SQL Server

Для будущих учащихся на курсе "MS SQL Server Developer" подготовили перевод полезной статьи.

Также приглашаем всех желающих на открытый вебинар на тему «Polybase: жизнь до и после». На вебинаре рассмотрим, как можно было взаимодействовать с другими базами данных до Polybase, и как это работает сейчас.

Меня иногда спрашивают: «Если в таблице много индексов и SQL Server вынужден анализировать больше вариантов, то не замедлит ли это построение плана запроса?»

Что же, вполне может быть, но что действительно сбивает с толку оптимизатор, так это секционирование. Даже простейшие запросы к секционированным таблицам могут привести к значительному увеличению использования процессорного времени. Хуже того, как однажды сказал знаменитый философ: «Больше секционирования — больше проблем».

Итак, давайте возьмем какую-нибудь из баз данных Stack Overflow и создадим функцию секционирования, которая будет разбивать наши данные по дням:

USE StackOverflow;
GO

/* Create date partition function by day since Stack Overflow's origin,
modified from Microsoft Books Online: 
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-partition-function-transact-sql?view=sql-server-ver15#BKMK_examples
 
DROP PARTITION SCHEME [DatePartitionScheme];
DROP PARTITION FUNCTION [DatePartitionFunction];
*/
DECLARE @DatePartitionFunction nvarchar(max) = 
    N'CREATE PARTITION FUNCTION DatePartitionFunction (datetime) 
    AS RANGE RIGHT FOR VALUES (';  
DECLARE @i datetime = '2008-06-01';
WHILE @i <= GETDATE()
BEGIN  
SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(20)) + '''' + N', ';  
SET @i = DATEADD(DAY, 1, @i);  
END  
SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(20))+ '''' + N');';  
EXEC sp_executesql @DatePartitionFunction;  
GO  
 
/* Create matching partition scheme, but put everything in Primary: */
CREATE PARTITION SCHEME DatePartitionScheme  
AS PARTITION DatePartitionFunction  
ALL TO ( [PRIMARY] ); 
GO

Далее создадим секционированную копию таблицы Users, разбив ее по значениям в колонке CreationDate:

DROP TABLE IF EXISTS dbo.Users_partitioned;
GO
CREATE TABLE [dbo].[Users_partitioned](
	[Id] [int] NOT NULL,
	[AboutMe] [nvarchar](max) NULL,
	[Age] [int] NULL,
	[CreationDate] [datetime] NOT NULL,
	[DisplayName] [nvarchar](40) NOT NULL,
	[DownVotes] [int] NOT NULL,
	[EmailHash] [nvarchar](40) NULL,
	[LastAccessDate] [datetime] NOT NULL,
	[Location] [nvarchar](100) NULL,
	[Reputation] [int] NOT NULL,
	[UpVotes] [int] NOT NULL,
	[Views] [int] NOT NULL,
	[WebsiteUrl] [nvarchar](200) NULL,
	[AccountId] [int] NULL
) ON [PRIMARY];
GO
 
CREATE CLUSTERED INDEX CreationDate_Id ON 
	dbo.Users_partitioned (Id)
	ON DatePartitionScheme(CreationDate);
GO
 
INSERT INTO dbo.Users_partitioned (Id, AboutMe, Age,
	CreationDate, DisplayName, DownVotes, EmailHash,
	LastAccessDate, Location, Reputation, UpVotes,
	Views, WebsiteUrl, AccountId)
SELECT Id, AboutMe, Age,
	CreationDate, DisplayName, DownVotes, EmailHash,
	LastAccessDate, Location, Reputation, UpVotes,
	Views, WebsiteUrl, AccountId
	FROM dbo.Users;
GO
Let’s c

Сравним производительность с секционированием и без

Создадим некластеризованный индекс для таблиц Users и Users_partitioned. Обратите внимание, даже если при создании индекса для Users_partitioned не указать, что он секционирован, то он все равно будет секционирован по умолчанию, что весьма замечательно:

CREATE INDEX DisplayName ON dbo.Users(DisplayName);
CREATE INDEX DisplayName ON dbo.Users_partitioned(DisplayName);

Теперь выполним чрезвычайно простой запрос, который возвращает одну строку:

SET STATISTICS TIME, IO ON;
SELECT * FROM dbo.Users WHERE DisplayName = N'Brent Ozar';
GO
SELECT * FROM dbo.Users_partitioned WHERE DisplayName = N'Brent Ozar';
GO

Планы запросов, на первый взгляд, выглядят одинаково, но обратите внимание, что стоимость запроса к несекционированной таблице составляет 0% от общей стоимости, а к секционированной — 100%:

Это связано с тем, что предполагаемая стоимость запроса к несекционированной таблице значительно меньше 0,001, а оценка стоимости запроса к секционированной — более 15. Что еще хуже, время компиляции (compile time), время выполнения (execution time) и количество логических чтений (logical reads) совершенно разные. На скриншоте ниже в верхней части показана статистика несекционированного запроса, а внизу — секционированного (для удобочитаемости из вывода убрана лишняя информация):

И только на компиляцию плана к секционированной таблице ушло 27 мс процессорного времени. Я знаю, о чем вы подумали: «Кого волнуют 27 мс процессорного времени?» Но вспомните — у нас был очень простой запрос! В реальной жизни вполне нормально, когда на составление плана уходит более 250 мс процессорного времени. Это означает, что в секунду на одном ядре процессора вы сможете скомпилировать только четыре запроса. Вот когда загрязнение кеша планов из-за непараметризированных запросов действительно портит вам жизнь.

Написать этот пост меня побудил один из моих клиентов, который решил секционировать все таблицы независимо от их размера при этом использовал везде одинаковую гранулярность секций, чтобы его динамические ETL-запросы остались простыми. К сожалению, даже запросы к простым таблицам конфигурации занимали более 250 мс процессорного времени только для компиляции плана.

Хорошо, но как насчет несекционированных индексов?

Давайте удалим секционированный индекс для таблицы Users_partitioned и создадим несекционированный. Обратите внимание, что для секционированной таблицы необходимо указать ON PRIMARY в качестве файловой группы, иначе любой некластеризованный индекс по умолчанию также будет автоматически секционирован.

CREATE INDEX DisplayName ON dbo.Users(DisplayName);
CREATE INDEX DisplayName ON dbo.Users_partitioned(DisplayName) ON [PRIMARY];

И выполним запросы еще раз:

SET STATISTICS TIME, IO ON;
SELECT * FROM dbo.Users WHERE DisplayName = N'Brent Ozar';
GO
SELECT * FROM dbo.Users_partitioned WHERE DisplayName = N'Brent Ozar';
GO

Теперь оценки стоимостей одинаковые:

Но все-таки это только оценка. Единственное, что здесь совпадает — это логические чтения:

Компиляция и выполнение плана запроса занимают больше времени, если какой-либо из задействованных объектов секционирован

Эти накладные расходы невелики, если сравнивать их с накладными расходами запросов к большим хранилищам данных, когда секционирование может уменьшить количество чтений. Но если сравнивать с небольшими объектами (например, rowstore-индексы размером до 100 ГБ), к которым часто обращаются с разнообразными запросами, требующими построения новых планов выполнения, то тогда накладные расходы на секционирование уже начинают суммироваться. Чем больше секционированных объектов, тем больше секций в каждом объекте, тем больше проблем.

Секционирование — одна из тех возможностей, о которой я никогда не слышал: «Вау, что бы я не сделал, секционирование всегда улучшает производительность!» Скорее всего, об этом продолжат говорить: «Я понятия не имел, что секционирование может создать проблемы и ТАМ».

Узнать подробнее о курсе "MS SQL Server Developer".

Смотреть открытый вебинар «Polybase: жизнь до и после».