God bless Dynamic SQL

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

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

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

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

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

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

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

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

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

Однако этот факт не должен сильно нас печалить, поскольку основной функционал этого приложения легко реализовать посредством применения динамического 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 (очень пригодился аргумент в пользу бесплатности этого продукта для рускоязычных пользователей, когда я убеждал начальство использовать его на постоянной основе).

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