Загрузка...
Второй блок по теме перемещение таблицы в новую файловую группу. Это может быть полезно в таких случаях как нехватка дискового пространства, если например вся база находится в одном файле с расширением .mdf или есть желание разнести по разным дисковым массивам таблицы и их индексы. Ещё как вариант, таблицы к которым мало обращаются вынести на медленный дисковый массив, а часто используемые таблицы переместить на быстрые дисковые массивы.
Прежде чем проводить подобные эксперементы сделайте BACKUP данной базы данных и проверьте его восстановлением из BACKUP вручную.
Запланировано разобрать 2 ситуации перемещения таблицы:
Данный эксперимент буду проводить всё на том же ноутбуке с ОС 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 запросов будет одинаков.
Также можно полностью графически посмотреть файловые группы нужной базы данных при создании новых файловых групп.
Итак приступим к созданию двух фаловых групп:
В SSMS кликаем правой кнопкой на нужной базе и во всплывшем окне левой кнопкой мыши жмём на "Свойства":
и перехдим во вкладку "Файловые группы", жмём на кропку "Добавить файловую группу" - появится пустая строка для новой файловой группу, в которую запишем одну из наших групп. После того, как впишем/скопируем и вставим имя файловой группы кнопку ОК нажимать не надо, надо снова нажать кнопку "Добавить файловую группу" для добавления второй файловой группы:
файловые группы созданы, но они пустые о чём свидетельствую нули в колонке "Файлы", т.е. к ним не привязаны файлы:
Переходим в раздел "Файлы", жмём на кропку "Добавить" и появится новая пустая строка для нашего нового файла. В колонке "Файловая группа" установилась db_for_1c_fg1, так как данная файловая группа указана по умолчанию и её надо будет заменить.
В результате после добавления двух нужных для эксперимента файлов можно жать ОК:
Обратите внимание на то, что логическое имя файла не обязано совпадать с физическим именем файла. В данном примере я специально сделал логическое имя 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:
Так вот чтобы перенести таблицу необходимо перестроить кластерный индекс с указанием новой файловой группы, но только в случае без LOB,BLOB.
Что нужно для этого сделать?
Приступим!!!
Посмотрим на эту таблицу с помощью 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
здесь на скрине показана только часть кода, код в текстовом виде вставлять смысла нет, т.к. у каждого он получится свой собственный.
Теперь меняем файловую группу по умолчанию:
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 и проверьте его, мало ли, вдруг криво создался. Несколько раз потренируйтесь на тестовой базе. Ну и если вы решили последовать рекомендациям описанным выше, вы это делаете на свой страх и риск.