Широко известна фраза: «Повторение – мать учения» — звучит банально, и только на втором году работы в должности 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 не ограничивается описанными выше примерами. Надеюсь, что эта статья поможет взглянуть на Ваши ежедненые задачи с другой стороны.
Автор: AlanDenton
