Русский
Русский
English
Статистика
Реклама

Polybase

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

01.03.2021 20:22:05 | Автор: admin

Для будущих учащихся на курсе "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;GOCREATE 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, AccountIdFROM dbo.Users;GOLets 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';GOSELECT * 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';GOSELECT * FROM dbo.Users_partitioned WHERE DisplayName = N'Brent Ozar';GO

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

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

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

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

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


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

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

Подробнее..

Хранимая процедура с возвращаемыми значениями в SSIS

05.03.2021 18:07:04 | Автор: admin

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

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


При всем моем двадцатилетнем опыте работы с MS SQL Server и SSIS (когда-то еще DTS), никогда не любил хранимые процедуры с возвращаемым значением. Не знаю, почему так сложилось. Может быть потому, что хранимки чаще приходилось использовать для реализации какой-то логики или возвращении набора записей, а для получения одного значения применял функции. Ну, так вот сложилось. Подразумеваю, что нелюбовь эта взаимная, что подтвердилось в последнем проекте, где хоть убей, но надо было с SSIS присваивать переменным возвращаемые значения процедуры. Изначально, пакет был не мой, а другого разработчика. Ничего плохого говорить не буду, все было сделано достаточно грамотно.

Процедура возвращала два значения типа DATETIME. В процессе работы я наступил на некоторые грабли, что и сподвигнуло на написание этой статьи.

Итак, дано:

1. Пакет SSIS

2. Переменные пакета:

Пакет, естественно, привожу тестовый, но сделанный по аналогу. Извините, но оригинальный, к сожалению, показать не могу.

3. Есть процедура, которая забирает две даты из таблицы. Даты нужны для определения интервалов инкрементальной заливки хранилища базы. Ниже приводится упрощенный текст процедуры:

Как видно, ничего сложного тут нет, даже слишком все просто. Можно было, в принципе, не делать возвращаемые параметры, а просто вернуть значения. Но, хозяин барин, что есть, то есть. Если мы запустим процедуру, то результат будет такой:

Теперь наш пакет.

Берем Execute SQL Command задание, настройки:

Как видим, возвращающий результат не выбран, так как у нас возвращаемые параметры. В выражение SQL ставим нашу процедуру:

И теперь назначаем параметры. Здесь самое интересное.

Для возвращаемых параметров выбираем Output и переменные пакета. Вопрос какой тип данных для этих параметров мы должны выбрать?

Для проверки того, что будет возвращаться, я создал скрипт задание, который будет показывать C# MessageBox со значением переменных. Настройки и код такие:

Вернемся к нашим параметрам, вернее, к их типу. Какой тип выбрать? У возвращаемых параметров процедуры тип DATETIME. Посмотрим, что предлагает SSIS.

SSIS package "F:\Projects\SSIS\TestMultiplePutput\TestMultiplePutput\TestMultiplePutput.dtsx" starting.Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query " exec dbo.testMultipleOutput ?, ? OUTPUT, ? OUTPUT" failed with the following error: "Error HRESULT E_FAIL has been returned from a call to a COM component.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Execute SQL TaskWarning: 0x80019002 at TestMultiplePutput: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.SSIS package "F:\Projects\SSIS\TestMultiplePutput\TestMultiplePutput\TestMultiplePutput.dtsx" finished: Failure.The program '[50800] DtsDebugHost.exe: DTS' has exited with code 0 (0x0)

Интересно. Причем ошибка вылезает из COM компонента проблемы с запросом, не настроен результат или параметры. Но, как мы видели, процедура отрабатывает. Если выражение просто скопировать в SSMS, поменять параметры и запустить, все работает. Честно говоря, ошибка меня заставила потерять какое-то время, но вменяемого ответа почему так происходит, я не нашел. Возможно, здесь происходит ошибка конвертации DATETIME в DATE. Причем, это происходило только, если я использовал OUTPUT параметры в компоненте.

Сам пакет выглядит таким образом:

Все настолько просто, что даже скучно. Смотрим дальше.

Выбираю DBDATE, сохраняю, запускаю. Как и следовало ожидать, вернулась дата.

DBTIME выбирать смысла нет, поэтому идем дальше. Хотя я все-таки попробовал. Вернулось ожидаемое время, но с сегодняшней датой. То же самое произошло и с DBTIME2. Осталось еще пара типов.

DBTIMESTAMP. Кто-то мне говорил, что не стоит с этим типом работать в SSIS. Дескать, неправильно отображает дату и время, так как это не совсем DATETIME. Сейчас мы это увидим. Вернулось то, что и ожидалось:

Если выбрать, ради эксперимента, последний временной тип DBTIMESTAMPOFFSET, который, в принципе, предназначен для работы с временными зонами, но чем черт не шутит, то он вернет правильную дату, но другое время:

Есть еще один вариант работы с возвращаемыми параметрами, но это, скажем, на любителя или для тех, кто сильно, как и я их не любит. В Execute SQL Command в тексте можно написать выражение T-SQL, примерно такое:

И настроить возвращаемый результат

И определить переменные для присвоения значений

Как говорится, дело вкуса. По производительности разницы нет, по стилю и самому решению, мне, даже при всей нелюбви к возвращаемым параметрам в пакете, больше импонирует первый вариант.


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

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

Подробнее..

Категории

Последние комментарии

  • Имя: Макс
    24.08.2022 | 11:28
    Я разраб в IT компании, работаю на арбитражную команду. Мы работаем с приламы и сайтами, при работе замечаются постоянные баны и лаги. Пацаны посоветовали сервис по анализу исходного кода,https://app Подробнее..
  • Имя: 9055410337
    20.08.2022 | 17:41
    поможем пишите в телеграм Подробнее..
  • Имя: sabbat
    17.08.2022 | 20:42
    Охренеть.. это просто шикарная статья, феноменально круто. Большое спасибо за разбор! Надеюсь как-нибудь с тобой связаться для обсуждений чего-либо) Подробнее..
  • Имя: Мария
    09.08.2022 | 14:44
    Добрый день. Если обладаете такой информацией, то подскажите, пожалуйста, где можно найти много-много материала по Yggdrasil и его уязвимостях для написания диплома? Благодарю. Подробнее..
© 2006-2024, personeltest.ru