Настройка производительности запросов на рабочих базах данных время от времени выполняется, хотя в действительности этого быть не должно.
Во-первых, это производственная среда: от нее зависит профессиональная деятельность вашей компании или компании клиента, если вы — независимый подрядчик. Даже просто настраивая предложения SELECT — те, что выполняют чтение, но не манипулируют данными, вы создаете лишнюю нагрузку для производственных систем. Во-вторых, вы, вероятно, применяете блокировки к строкам, страницам или таблицам и тем блокируете рабочий трафик.
В-третьих, настраивая производительность запросов, вы наверняка выполняете одно из двух действий: изменяете код запроса или хранимой процедуры, чтобы увидеть, улучшают ли изменения существующий код со слабой производительностью, или создаете копии хранимых процедур, тем самым ухудшая структуру базы данных.
Любому специалисту по обработке данных понятно, что очевидная альтернатива, создание копии базы данных, не намного лучше. Создать копию небольшой базы данных для настройки запросов достаточно просто: элементарный процесс резервного копирования или восстановления для сервера разработки — задача для администратора баз данных начального уровня. Однако в настоящее время исключительно важна конфиденциальность данных. Копия рабочей базы данных часто содержит личную, финансовую или медицинскую информацию, хранение которой регулируется бесчисленными нормативными актами, предусматривающими уголовную ответственность, если данные попадут в чужие руки. Конфиденциальность в той же мере важна и для крупных баз данных, но возникает дополнительная проблема выделения хранилища значительного размера для среды, в которой такое хранилище в принципе не требуется. Это проблема в том числе и финансовая, особенно в век «облачных» вычислений и с учетом платы за потребляемые ресурсы.
Что же делать современному администратору баз данных? Представьте, что вы можете создать базу данных SQL Server, которая ведет себя как большая база данных. Представьте, что вы можете подготовить массу записей и связанную статистику, которые на уровне метаданных объясняют распределение значений данных в каждой таблице. Это обязательное условие, чтобы оптимизатор запросов мог создавать планы выполнения для анализа, необходимого для настройки производительности, но не занимая большого объема памяти. Вы спросите — черная магия? Я отвечу: DBCC CLONEDATABASE.
Много лет назад я присутствовал на мероприятии SQLSaturday (https://sqlsaturday.com/), на котором мой друг, обладатель звания Microsoft Data Platform MVP, рассказывал о том, как с помощью сценария изъять объекты из базы данных, а затем, получив «копию оболочки» базы данных, импортировать в нее статистику из исходной базы данных. Этот гениальный ход позволяет администраторам настраивать производительность запросов, не занимая места в процессе и не передавая конфиденциальные данные в руки разработчиков или других специалистов, не имеющих права на доступ к ним. Команда DBCC CLONEDATABASE выполняет именно эту задачу.
Использовать DBCC CLONEDATBASE просто, синтаксис следующий:
DBCC CLONEDATABASE (‘SOURCE_DATABASE’, ‘NEW_DATABASE’)
Рассмотрим команду в действии с базой данных из моей тестовой среды. Сначала мне предстоит создать пустую базу данных с единственной таблицей. Я создам кластеризованный индекс для пустой таблицы, а также некластеризованный индекс для столбца данных, который будет в конечном итоге заполнен. Чтобы иметь возможность быстро создать вторую таблицу для присоединения в этом примере, выполняется запрос SELECT … INTO, как показано в листинге 1.
Затем добавляется умеренное количество записей (а именно 1200) и запускается команда tblFoo, чтобы создать новую таблицу с тем же числом записей, но без индексов:
SELECT * INTO tblFOO FROM dbo.JunkDrawer;
Выполнение запроса из листинга 2 в SourceDB обеспечивает план выполнения, представленный на рисунке 1. Я ввожу графический план выполнения в SentryOne Plan Explorer, чтобы показать количество записей, важное для рассмотрения клонированной базы данных.
.jpg) |
| Рисунок 1. План выполнения запроса листинга 2 |
Мы получаем операцию Index Scan для некластеризованного индекса и просмотра таблицы для кучи в tblFoo, поскольку эта таблица была создана с использованием инструкции SELECT… Into. Обратите внимание на число записей рядом с операциями.
Пришло время запустить DBCC CLONEDATABASE.
Выполнение следующей команды дает нам новую базу данных с именем TargetDB:
DBCC CLONEDATABASE (SourceDB, TargetDB);
Должны быть показаны результаты и соответствующие заявления об отказе от ответственности (рисунок 2).
.jpg) |
| Рисунок 2. Создание новой базы данных командой DBCC CLONEDATABASE |
На данном этапе мы можем направить запрос к TargetDB. dbo. JunkDrawer, и он должен оказаться пустым (рисунок 3).
SELECT COUNT(ID) FROM TargetDB.dbo.JunkDrawer;
.jpg) |
| Рисунок 3. Результаты запроса к TargetDB.dbo.JunkDrawer |
Однако, если выполнить тот же запрос, как показано в листинге 2, он должен возвратить графический план выполнения (рисунок 4), как при полном заполнении, аналогично SourceDB (листинг 3).
.jpg) |
| Рисунок 4. План исполнения запроса к новой базе данных |
План выполнения идентичен для TargetDB, несмотря на отсутствие записей и значительно меньший размер, чем у исходной базы данных (в реальных условиях). Обратите внимание на количество записей в версии TargetDB. Хотя базе данных известно, что записи отсутствуют, планы выполнения строятся на основе статистики распределения.
Благодаря использованию DBCC CLONEDATABASE можно диагностировать различные проблемы, связанные с настройкой запросов, не занимая места на сервере и не подвергая опасности конфиденциальные данные в областях, контролируемых не столь строго, как рабочие базы данных. Размер клонированной базы данных соответствует модели базы данных, а не исходной базе данных.
Также важно отметить, что при использовании DBCC CLONEDATABASE, если вы добавили любой пользовательский объект в модель базы данных, команда клонирования завершится неудачей. Это происходит из-за возможного конфликта значений object_id между пользовательскими объектами, созданными в модели, и объектами в исходной базе данных пользователя. Процесс создает скрытый моментальный снимок базы данных, чтобы сформировать клон. Если по какой-то причине моментальный снимок не создан, процесс клонирования завершится неудачей. Также нельзя создать клонированную базу данных, если заданное имя уже используется.
Таким образом, существует гораздо более быстрый способ пройти процесс, с которым я познакомился много лет назад. Он занимает секунды и не требует понимания переноса объектов статистики между базами данных, что является сложной задачей для многих администраторов баз данных.
CREATE DATABASE [SourceDB]; GO ALTER DATABASE [SourceDB] SET RECOVERY SIMPLE GO USE [SourceDB] GO CREATE TABLE [dbo].[JunkDrawer]( [ID] [int] IDENTITY(1,1) NOT NULL, [theDate] [datetime] NOT NULL, [theValue] [int] NOT NULL, [someGUID] [uniqueidentifier] NOT NULL ) ON [PRIMARY] GO CREATE UNIQUE CLUSTERED INDEX [MyAmazingClusteredIndex] ON [dbo].[JunkDrawer] ( [ID] ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [NotSoAmazingNonClusteredIndex] ON [dbo].[JunkDrawer] ( [theDate] ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90 ) ON [PRIMARY] GO
SELECT S.ID, S.theDate, F.someGUID FROM SourceDB.dbo.JunkDrawer AS S INNER JOIN SourceDB.dbo.tblFOO AS F ON S.ID = F.ID ORDER BY S.theDate
SELECT S.ID, S.theDate, F.someGUID FROM TargetDB.dbo.JunkDrawer AS S INNER JOIN TargetDB.dbo.tblFOO AS F ON S.ID = F.ID ORDER BY S.theDate