SQL Server перемещение таблицы в новую файловую группу

Второй блок по теме перемещение таблицы в новую файловую группу. Это может быть полезно в таких случаях как нехватка дискового пространства, если например вся база находится в одном файле с расширением .mdf или есть желание разнести по разным дисковым массивам таблицы и их индексы. Ещё как вариант, таблицы к которым мало обращаются вынести на медленный дисковый массив, а часто используемые таблицы переместить на быстрые дисковые массивы.

Прежде чем проводить подобные эксперементы сделайте BACKUP данной базы данных и проверьте его восстановлением из BACKUP вручную.

Запланировано разобрать 2 ситуации перемещения таблицы:

1. Таблица НЕ содержащая большие объекты - LOB, BLOB;
2. Таблица содержащая большие объекты - LOB, BLOB.

Данный эксперимент буду проводить всё на том же ноутбуке с ОС Windows 10 и на SQL Server 2019, но все эти действия будут работать на более ранних версиях, например на SQL SERVER 2008 R2.

---

Сразу хотелось бы уточнить что же такое LOB и BLOB:

LOB - расшифровывается как Large Object, к LOB'ам относятся такие типы данных как TEXT(устаревший тип данных), VARCHAR(max), NVARCHAR(max), XML и др. ячейка с таким типом данных может занимать объём диска до 2-х ГигаБайт! Не вся таблица, а каждая ячейка этой таблицы может быть такого объёма. И эти типы данных по сути текстовые, т.е. выполнив SELECT запрос можно увидеть эти самые текстовые данные без всякого приведения типов.

BLOB - расшифровывается как Binary Large Object, к ним относятся такие типы данных как VARBINARY(max), IMAGE и др. Они также могут быть до 2ГБ, т.е. каждая ячейка с такими типами данных может быть до 2 Гигабайт!!!

---

Для обзора всех таблиц буду использовать следующий запрос:

use db_for_1c
go
SELECT t.object_id AS ObjectID,
		s.name + '.' + 
       +OBJECT_NAME(t.object_id) AS ObjectName,
       SUM(u.total_pages) * 8 AS Total_Reserved_kb,
       SUM(u.used_pages) * 8 AS Used_Space_kb,
       u.type_desc AS TypeDesc,
       MAX(p.rows) AS RowsCount
FROM sys.allocation_units AS u
JOIN sys.partitions AS p ON u.container_id = p.hobt_id
JOIN sys.tables AS t ON p.object_id = t.object_id
JOIN sys.schemas AS s ON t.schema_id=s.schema_id
GROUP BY t.object_id,
         OBJECT_NAME(t.object_id)
		 , u.type_desc
		 , s.name
ORDER BY Used_Space_kb DESC,
         ObjectName;

Результа запроса данного запроса показан ниже:

Возьму данные две таблицы: _AccRgED1050 и _Reference127 и немного изменю предыдущий запрос:

SELECT t.object_id AS ObjectID,
		s.name + '.' + 
       +OBJECT_NAME(t.object_id) AS ObjectName,
       SUM(u.total_pages) * 8 AS Total_Reserved_kb,
       SUM(u.used_pages) * 8 AS Used_Space_kb,
       u.type_desc AS TypeDesc,
       MAX(p.rows) AS RowsCount
FROM sys.allocation_units AS u
JOIN sys.partitions AS p ON u.container_id = p.hobt_id
JOIN sys.tables AS t ON p.object_id = t.object_id
JOIN sys.schemas AS s ON t.schema_id=s.schema_id
where OBJECT_NAME(t.object_id) = '_AccRgED1050'
or OBJECT_NAME(t.object_id) = '_Reference127'
GROUP BY t.object_id,
         OBJECT_NAME(t.object_id)
		 , u.type_desc
		 , s.name
ORDER BY Used_Space_kb DESC,
         ObjectName;

Итак, таблица _Reference127 содержи LOB данные, а таблица _AccRgED1050  не содержит, значит для первого эксперимента будет использоваться таблица _AccRgED1050.

---

С таблицами определились, теперь необходимо определиться в какие файловые группы будем перемещать таблицы.

Посмотрим какие группы есть в базе данных use db_for_1c, это можно сделать с помощью SQL запроса:

use db_for_1c
go
select * from sys.filegroups;

данный запрос необходимо выполнять в контексте нужной нам базы данных, USE и определяет данный контекст. Но также можно использовать так называемый полный квалификатор:

select * from db_for_1c.sys.filegroups;

т.е. в клаузуле FROM используется имя базы данных:

обратите внимание, что запрос с полным квалификатором выполняется в контексте базы данных MASTER, но полученные файловые группы относятся к нужной нам базы данных.

Также можно не использовать USE и полный квалификатор, но при этом нужную нам базу выбрать графически:

результат всех трёх  SELECT запросов будет одинаков.

Также можно полностью графически посмотреть файловые группы нужной базы данных при создании новых файловых групп.

Итак приступим к созданию двух фаловых групп:

• fg_for_data - данная группа для самой таблицы;
• fg_for_index - данная группа для индексов.

В SSMS кликаем правой кнопкой на нужной базе и во всплывшем окне левой кнопкой мыши жмём на "Свойства":

и перехдим во вкладку "Файловые группы", жмём на кропку "Добавить файловую группу" - появится пустая строка для новой файловой группу, в которую запишем одну из наших групп. После того, как впишем/скопируем и вставим имя файловой группы кнопку ОК нажимать не надо, надо снова нажать кнопку "Добавить файловую группу" для добавления второй файловой группы:

файловые группы созданы, но они пустые о чём свидетельствую нули в колонке "Файлы", т.е. к ним не привязаны файлы:

Переходим в раздел "Файлы", жмём на кропку "Добавить" и появится новая пустая строка для нашего нового файла. В колонке "Файловая группа" установилась db_for_1c_fg1, так как данная файловая группа указана по умолчанию и её надо будет заменить.

• В колонке "Логическое имя" придумываем логическое имя файла;
• В колонке "Файловая группа"  выбираем нужную нам файловую группу;
• В колонке "Путь" указываем где будет находится физический файл базы данных;
• В колонке "Имя файла" придумываем физическое имя файла и по традиции добавляем расширение .ndf:

В результате после добавления двух нужных для эксперимента файлов можно жать ОК:

Обратите внимание на то, что логическое имя файла не обязано совпадать с физическим именем файла. В данном примере я специально сделал логическое имя file_for_indexes и физическое имя файла db_for_1c_indexes.ndf разными. Также стоит упомянуть то, что любой физический файл базы данных может быть прикреплён только к одной файловой группе, а любая файловая группа может содержать в себе множетсво физических файлов. И как результат, по данному пути D:\c\base\sql\newfiles появились файлы:

Результат нашей работы пожно посмотреть используя SQL запрос

use db_for_1c
go
select 
	fg.name as fgName --Имя файловой группы
	, f.name as fName --Логическое имя файла
	,f.filename --Физическое имя файла
from sys.filegroups fg
	inner join sys.sysfiles f on f.groupid = fg.data_space_id
order by fgName;

Результат работы данного SQL запроса ниже на скрине:

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

---

1. Перенос таблицы без LOB, BLOB данных

Сразу пару слов нужно сказать что такое таблица? Или точнее нужно сказать о двух способах хранения таблицы, в данном случае это будет таблица _AccRgED1050:

• Первый способ хранения - это КУЧА(HEAP);
• Второй способ - это КЛАСТЕРНЫЙ ИНДЕКС(CLUSTERED)

Так вот чтобы перенести таблицу необходимо перестроить кластерный индекс с указанием новой файловой группы, но только в случае без LOB,BLOB.

Что нужно для этого сделать?

1. Сохранить код кластерного индекса;
2. В данном коде изменить файловую группу;
3. Удалить индекс из таблицы (при этом она станет HEAP);
4. Запустить код кластерного индекса и нужной нам файловой группой.

Приступим!!!

Посмотрим на эту таблицу с помощью SQL запроса:

use db_for_1c
go
select
	tab.name as tabName --Название таблицы
	, IX.name as ixName --Название индекса
	, IX.type_desc as ix_type_desc --Тип индекса
	, fg.name as fgName -- Имя файловой группы
from sys.indexes IX
INNER join sys.filegroups fg on fg.data_space_id = IX.data_space_id
join sys.tables tab on tab.object_id = IX.object_id
where tab.name = '_AccRgED1050';

Индекс _AccRgED1050_1 и есть таблица, вот его и надо перестроить с указанием новой файловой группы. Теперь с помощью SSMS посмотрим на всё это:

В нужной нам базе данных (в данном случае это db_for_1c) переходим во вкладку таблицы

Ищем таблицу _AccRgED1050  

заходим в вкладку "Индексы" и видим тот самый кластерный индекс.

Теперь необходимо сохранить/извлечь код создания данного индекса:

Правой кнопкой мыши жмём на _AccRgED1050_1 (Кластеризованный)

во всплывшем окне переходим "Создать скрипт для индекса"  -  "Используя CREATE" и левой кнопкой мыши жмём на "Новое окно редактора запросов":

в результате появляется новая вкладка с запросом:

обратите внимание, на указание файловой группы "ON [db_for_1c_fg1]" вот её и нужно изменить на fg_for_data.

В коде изменяем файловую группу на нужную.

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

Так что нажимаем правой кнопкой на индексе _AccRgED1050_1 и во всплывшем окне левой кнопкой мыши жмём "Удалить" - см. скрин ниже:

и жмём ОК:

Обратите внимание, что кластерный индекс исчез, см. скрин ниже:

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

use db_for_1c
go
select
	tab.name as tabName --Название таблицы
	, IX.name as ixName --Название индекса
	, IX.type_desc as ix_type_desc --Тип индекса
	, fg.name as fgName -- Имя файловой группы
from sys.indexes IX
INNER join sys.filegroups fg on fg.data_space_id = IX.data_space_id
join sys.tables tab on tab.object_id = IX.object_id
where tab.name = '_AccRgED1050';

результат выполнения данно SQL запроса ниже на скрине:

обратите внимание, что изчез кластерный индекс и была наложена HEAP(т.е. изменили способ хранения таблицы) на данную таблицу. Но таблица при этом никуда не ичезла!!!

Теперь время вновь создать тот же самый кластерный индекс с уже другой файловой группой, либо жмём мышкой кнопкой выполнить либо F5:

учтите, если данных много, то скрипт будет выполняться долго!

Скрипт отработал довольно-таки быстро, т.к. данных было немного:

Теперь посмотрим на результат:

файл db_for_1c_data.ndf вырос с 8 мегабайт до 72 мегабайт:

и посмотрим на результат SQL запроса:

use db_for_1c
go
select
	tab.name as tabName --Название таблицы
	, IX.name as ixName --Название индекса
	, IX.type_desc as ix_type_desc --Тип индекса
	, fg.name as fgName -- Имя файловой группы
from sys.indexes IX
INNER join sys.filegroups fg on fg.data_space_id = IX.data_space_id
join sys.tables tab on tab.object_id = IX.object_id
where tab.name = '_AccRgED1050';

снова появился тот же самый кластерный индекс, но хранящийся в другой файловой группе, которой привязан файл db_for_1c_data.ndf, который можно хранить/перенести на другой жёсткий диск.

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

Правой кнопкой мыши жмём на "Индексы" и во всплывшем окне жмём "Обновить":

после обновления индекс снова появился в списке:

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

a few moments later

Результат работы SQL запроса:

также увеличился файл для индексов:

На этом мы заканчиваем перенос без(B)LOB-вских таблиц/индексов и переходим к следующему этапу.

---

2. Перенос таблицы с LOB, BLOB данными

Теперь рассмотрим самый более сложный способ переноса таблицы. Запустим следующие два SQL запроса одновременно:

go
select
	tab.name as tabName --Название таблицы
	, IX.name as ixName --Название индекса
	, IX.type_desc as ix_type_desc --Тип индекса
	, fg.name as fgName -- Имя файловой группы
from sys.indexes IX
INNER join sys.filegroups fg on fg.data_space_id = IX.data_space_id
join sys.tables tab on tab.object_id = IX.object_id
where tab.name = '_Reference127';
 
SELECT 
         OBJECT_NAME(t.object_id) AS ObjectName -- Имя таблицы
	   , u.type_desc AS TypeDesc -- Тип данных
	   , fg.name --Имя файловой группы
FROM sys.allocation_units AS u
JOIN sys.partitions AS p ON u.container_id = p.hobt_id
JOIN sys.tables AS t ON p.object_id = t.object_id
left join sys.filegroups fg on fg.data_space_id = t.lob_data_space_id
where t.name = '_Reference127'
	and u.type_desc = 'LOB_DATA'

обращаем внимание что CLUSTERED и LOB_DATA находятся в одной файловой группе.

Перенесём таблицу в файловую группу fg_for_data используя вышеописанный метод я перенесу таблицу и посмотрим что получится.

...

Итак, смотрим результат:

Обратите внимание на то, что все индексы и CLUSTERED, и NONCLUSTERED находятся в нужных нам файловых группах, но при этом (B)LOB данные по прежнему находятся в файловой группе db_for_1c_fg1, которая является по умолчанию.

Как же быть в таком случае? А поступить придётся следующим образом:

• Необходимо сохранить код всех индексов (кластерных и некластерных) в один файл(например temp.sql) и конечно же у каждого индекса указать нужную файловую группу;
• Изменить файловую группу по умолчанию с db_for_1c_fg1 на fg_for_data;
• С помощью SELECT INTO создать новую таблицу _Reference127_temp на основе оригинальной _Reference127;
• Удалить оригинальную таблицу _Reference127;
• Временную таблицу _Reference127_temp переименовать _Reference127;
• Запустить по F5 или кнопкой "Выполнить" весь скрипт temp.sql чтобы все индексы разом создались;
• Сделать файловую группу db_for_1c_fg1 снова по умолчанию.

Создал файл temp.sql

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

Теперь меняем файловую группу по умолчанию:

USE [db_for_1c]
GO
select * from sys.filegroups;

Файловой группой по умолчанию будет та, у которой единица в колонке "is_default". В данном случае это db_for_1c_fg1. И нужно её временно поменять на fg_for_data.

А чтобы поменять, нужно зайти в "Свойства" нужной нам базы данных:

Переходим на вкладку "Файловые группы", устанавливаем причку напротив нужной нам файловой группы, в данном случае это fg_for_data и жмём ОК:

Для проверки снова запустим SQL запрос и посмотрим на результат:

USE [db_for_1c]
GO
select * from sys.filegroups;

В результате файловая группа изменена так как нам и нужно.

Теперь необходимо создать новую временную таблицу с помощь SQL запроса:

USE [db_for_1c]
select * into  _Reference127_temp  from  dbo._Reference127;

в результате создалась новая таблица _Reference127_temp без индексов:

сразу хотелось бы отметить, что это один из немногих случаев, когда допускается использовать SELECT запрос со звёздочкой(*)!!!

Проверим расположение LOB_DATA во временной таблице с помощью SQL запросов:

go
select
	tab.name as tabName --Название таблицы
	, IX.name as ixName --Название индекса
	, IX.type_desc as ix_type_desc --Тип индекса
	, fg.name as fgName -- Имя файловой группы
from sys.indexes IX
INNER join sys.filegroups fg on fg.data_space_id = IX.data_space_id
join sys.tables tab on tab.object_id = IX.object_id
where tab.name = '_Reference127_temp';
 
SELECT 
         OBJECT_NAME(t.object_id) AS ObjectName -- Имя таблицы
	   , u.type_desc AS TypeDesc -- Тип данных
	   , fg.name --Имя файловой группы
FROM sys.allocation_units AS u
JOIN sys.partitions AS p ON u.container_id = p.hobt_id
JOIN sys.tables AS t ON p.object_id = t.object_id
left join sys.filegroups fg on fg.data_space_id = t.lob_data_space_id
where t.name = '_Reference127_temp'
	and u.type_desc = 'LOB_DATA'

(B)LOB данные теперь находятся в нужной файловой группе!

Теперь удаляем оригинальную таблицу _Reference127: на данной таблице кликаем правой кнопкой мыши и жмём "Удалить", но будьте очень осторожны!:

далее жмём ОК:

в результате осталась только таблица _Reference127_temp:

Теперь нужно переименовать эту таблицу _Reference127_temp в _Reference127, т.е. как и было раньше.

Для переименования нажимаем правой кнопкой мыши на таблице и во вспывшем окне ждём "Переименовать":

выделям _temp жмём delete на клавиатуре и затем enter:

И вот у нас снова оригинальное название таблицы.

И посмотрим на её LOB_DATA:

Запускаем скрипт temp.sql:

и смотрим результат:

все данные теперь расположены в нужных нам файловых группах.

Осталось дело за малым, вернуть файловой группе db_for_1c_fg1  статус "По умолчанию":

в данном случае решил это сделать запуском SQL запросов:

USE [db_for_1c]
select * from sys.filegroups;

GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'db_for_1c_fg1') 
ALTER DATABASE [db_for_1c] MODIFY FILEGROUP [db_for_1c_fg1] DEFAULT
GO

select * from sys.filegroups;

в результате мы получим:

двумя SELECT'тами мы проверям файловую группу до и после изменения.

 

Итог: Все поставленные задачи выполнены!

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