God bless Dynamic SQL

в 10:37, , рубрики: dynamic sql, sql server, t-sql, метки: , , ,

Широко известна фраза: «Повторение – мать учения» — звучит банально, и только на втором году работы в должности DBA, я смог в полной мере прочувствовать смысл этой фразы, услышанной впервые ещё в школе.

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

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

Под катом приведено несколько жизненных примеров применения динамического SQL для решения реальных задач.

1. Автоматическое обслуживание индексов

То, что удовлетворительно работало на этапе проектирования, с течением времени, может вызывать существенное падение производительности при работе с базой данных.

Причин этому может быть много, поэтому, чтобы минимизировать вероятность возникновения самых очевидных из них, — формируют, так называемый, план обслуживания, в джентельменский набор которого входят задачи по перестройке (дефрагментации) индексов.

При разовом обслуживании можно перестроить индексы вручную, например, через пункт контекстного меню в SSMS — Rebuild Index.

Также, можно воспользоваться одним из специализированных инструментов – в своё время, я достаточно активно использовал бесплатный инструмент SQL Index Manager (очень жаль, что на момент написания статьи RedGate уже сделала его платным):

God bless Dynamic SQL

Однако этот факт не должен сильно нас печалить, поскольку основной функционал этого приложения легко реализовать посредством применения динамического SQL.

В первую очередь, необходимо получить список фрагментированных индексов, отсеяв при этом таблицы без первичного ключа (кучи):

SELECT
	  <code>[object_name] = SCHEMA_NAME(o.[schema_id]) + '.' + o.name</code>
	, [object_type] = o.type_desc
	, index_name = i.name
	, index_type = i.type_desc
	, s.avg_fragmentation_in_percent
	, s.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') s
JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
JOIN sys.objects o ON o.[object_id] = s.[object_id]
WHERE s.index_id > 0
	AND avg_fragmentation_in_percent > 0

После этого мы сформируем динамический запрос, который, в зависимости от степени фрагментации, будет перестраивать либо реорганизовывать индексы:

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = (
	SELECT
	'ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +
	CASE WHEN s.avg_fragmentation_in_percent > 50
		THEN 'REBUILD WITH (SORT_IN_TEMPDB = ON'
		 + CASE WHEN SERVERPROPERTY('Edition') IN ('Enterprise Edition', 'Developer Edition')
			 THEN ', ONLINE = ON' ELSE '' END + ')'
		ELSE 'REORGANIZE'
	END + ';
	RAISERROR(''Processing ' + i.name + '...'', 0, 1) WITH NOWAIT;'
	FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') s
	JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
	JOIN sys.objects o ON o.[object_id] = s.[object_id]
	WHERE s.index_id > 0
		AND page_count > 100
		AND avg_fragmentation_in_percent > 10
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

EXEC sys.sp_executesql @SQL

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

Дефрагментация индексов – очень ресорусоемкая операция, которая может занимать продолжительное время для таблиц, содержащих большие объемы данных.

Чтобы не блокировать работу пользователей, выполнять дефрагментацию индексов наиболее оптимально в ночное время, когда на базу оказывается минимальная нагрузка. Но не у каждого есть желание работать ночью, поэтому разумно воспользоваться возможностями SQL Agent.

Через SQL Agent был добавлен Job, который ежедневно выполнял скрипт.

2. Автоматическое добавление столбца к выбранным таблицам

На этапе внедрения, заказчик попросил реализовать возможность логирования изменений по всем имеющимся таблицам. В итоге потребовалось добавить 2 столбца для более чем 300 таблиц:

CreatedDate  DATETIME
ModifiedDate DATETIME

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

В результате получили список всех таблиц, у которых не имелось указанных столбцов:

SELECT SCHEMA_NAME(o.[schema_id]) + '.' + o.name
FROM sys.objects o
LEFT JOIN (
	SELECT *
	FROM (
		SELECT c.[object_id], c.name
		FROM sys.columns c
		WHERE c.name IN ('ModifiedDate', 'CreatedDate')
	) c
	PIVOT (MAX(name) FOR name IN (ModifiedDate, CreatedDate)) p
) c ON o.[object_id] = c.[object_id]
WHERE o.[type] = 'U'
	AND (ModifiedDate IS NULL OR CreatedDate IS NULL)

Был сформировал и выполнен динамический запрос на изменение этих таблиц:

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = (
    	SELECT '
		ALTER TABLE [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + ']
		ADD ' +
			CASE WHEN ModifiedDate IS NULL
				THEN '[ModifiedDate] DATETIME'
				ELSE ''
			END +
			CASE WHEN CreatedDate IS NULL
				THEN CASE WHEN ModifiedDate IS NULL THEN ', ' ELSE '' END
					+ '[CreatedDate] DATETIME'
				ELSE ''
			END + ';'
		FROM sys.objects o
		LEFT JOIN (
			SELECT *
			FROM (
				SELECT c.[object_id], c.name
				FROM sys.columns c
				WHERE c.name IN ('ModifiedDate', 'CreatedDate')
			) c
			PIVOT (MAX(name) FOR name IN (ModifiedDate, CreatedDate)) p
		) c ON o.[object_id] = c.[object_id]
		WHERE o.[type] = 'U'
			AND (ModifiedDate IS NULL OR CreatedDate IS NULL)
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

EXEC sys.sp_executesql @SQL
3. Создание консолидированных таблиц

Задачи по созданию сводных отчетов на предприятиях очень сильно распространены. Помнится, в свое время, для отдела Бухгалерии, я их создал великое множество. При этом возникало много проблем. Одна из них – затрата большого количества времени на реализацию конкретного отчета.

Чтобы частично оптимизировать этот процесс, было решено формировать некоторые отчеты динамически, через функциональность Pivot, реализованную в продукте dbForge for SQL Server (очень пригодился аргумент в пользу бесплатности этого продукта для рускоязычных пользователей, когда я убеждал начальство использовать его на постоянной основе).

God bless Dynamic SQL

Однако, не всегда создание консолидированных таблиц сводилось к необходимости создания отчетов. В некоторых сценариях, консолидированные таблицы были более эффективны, чем постоянное использование PIVOT запросов.

Подобные таблицы можно создать через табличный редактор SSMS или того же dbForge простым copy-paste столбцов.

Однако, данный вариант не является оптимальным, особенно, если есть возможность использовать динамический SQL. Всё, что требуется от пользователя, — указать количество столбцов, которое будет в таблице, префикс столбца, его тип, а далее выполняем следующий запрос:

IF OBJECT_ID ('dbo.temp') IS NOT NULL
   DROP TABLE dbo.temp

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = 'CREATE TABLE dbo.temp (EmployeeID INT IDENTITY(1,1) PRIMARY KEY' + (
    SELECT ', Day' + RIGHT('0' + CAST(sv.number AS VARCHAR(2)), 2) + ' INT'
	FROM [master].dbo.spt_values sv
	WHERE sv.[type] = 'p'
		AND sv.number BETWEEN 1 AND 31
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') + ')'

PRINT @SQL
EXEC sys.sp_executesql @SQL

Voila! После выполнения мы получим таблицу со следующей структурой:

CREATE TABLE dbo.temp 

(
	  EmployeeID INT IDENTITY (1, 1) PRIMARY KEY
	, Day01 INT
	, Day02 INT
	, Day03 INT
	, Day04 INT
	, Day05 INT
	, ...
	, Day30 INT
	, Day31 INT
)

Вывод:

Стоит отметить, что применение динамического SQL не ограничивается описанными выше примерами. Надеюсь, что эта статья поможет взглянуть на Ваши ежедненые задачи с другой стороны.

Автор: AlanDenton

Источник


* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js