God bless Dynamic SQL

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

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

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

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

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

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

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

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

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

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

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

SELECT
	  [object_name] = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
	, [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 запросов.

Подобные таблицы можно создать через табличный редактор.

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

Далее выполняем следующий запрос:

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

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 не ограничивается описанными выше примерами. Надеюсь, что эта статья поможет взглянуть на Ваши ежедненые задачи с другой стороны.