MS SQL секционирование

Многие администраторы Microsoft SQL Server сталкивались с проблемой значительного увеличения физического размера базы данных и файлов журнала транзакций и, конечно же, им хотелось бы каким-то образом уменьшить этот размер, для того чтобы не предпринимать какие-либо действия, связанные с увеличением свободного пространства на жестком диске. Способ уменьшить физический размер базы данных и файлов журнала транзакций в SQL сервере есть – это сжатие.

Что такое сжатие в Microsoft SQL Server?

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

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

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

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

Усечение журнала транзакций происходит автоматически:

  • В простой модели восстановления — после достижения контрольной точки, которая может возникнуть, например, после создания BACKUP базы данных, при явном выполнении инструкции CHECKPOINT, или тогда когда размер логического журнала транзакций заполняется на 70 процентов, во всех этих случаях происходит автоматическая очистка неактивной части журнала, т.е. его усечение;
  • В модели полного восстановления или в модели восстановления с неполным протоколированием — после создания резервной копии журнала при условии, что с момента создания последней резервной копии журнала была достигнута контрольная точка.

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

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

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

Как сжать базу данных в MS SQL Server?

Сжать файлы базы данных и журнала транзакций можно и с помощью графического интерфейса Management Studio и с помощью инструкций Transact-SQL: DBCC SHRINKDATABASE и DBCC SHRINKFILE. Также возможно настроить базу данных на автоматическое сжатие путем выставления параметра БД AUTO_SHRINK в значение ON.

Примечание! Сжатие базы данных я буду рассматривать на примере Microsoft SQL Server 2016 Express.

Сжимаем базу данных с помощью среды Management Studio

Запускаем Management Studio и в обозревателе объектов открываем объект «Базы данных». Затем щелкаем правой кнопкой мыши по БД, которую необходимо сжать, далее выбираем «Задачи ->Сжать -> База данных (или Файлы, если, например, нужно сжать только журнал транзакций)». Я для примера выбираю «База данных».

В итоге у Вас откроется окно «Сжатие базы данных», в котором Вы, кстати, можете наблюдать размер базы данных, а также доступное свободное место, которое можно удалить (т.е. сжать). Нажимаем «ОК».

Через некоторое время, в зависимости от размера базы данных, сжатие будет завершено.

Сжимаем базу данных с помощью инструкций SHRINKDATABASE и SHRINKFILE

В MS SQL Server для выполнения сжатия файлов базы данных и журнала транзакций существуют две инструкции SHRINKDATABASE и SHRINKFILE.

  • DBCC SHRINKDATABASE – это команда для сжатия базы данных;
  • DBCC SHRINKFILE – с помощью данной команды можно выполнить сжатие некоторых файлов базы данных (например, только журнала транзакций).

Для того чтобы выполнить сжатие БД (например, TestBase) точно также как мы это сделали чуть ранее в Management Studio, выполните следующую инструкцию.

DBCC SHRINKDATABASE(N’TestBase’)

SHRINKDATABASE имеет следующие параметры:

  • database_name или database_id — имя или идентификатор базы данных, которую необходимо сжать. Если указать значение 0, то будет использоваться текущая база данных;
  • target_percent – свободное пространство в процентах, которое должно остаться в базе данных после сжатия;
  • NOTRUNCATE — сжимает данные в файлах с помощью перемещения распределенных страниц из конца файла на место нераспределенных страниц в начале файла. Если указан данный параметр, физический размер файла не изменяется;
  • TRUNCATEONLY — освобождает все свободное пространство в конце файла операционной системе, но не перемещает страницы внутри файла. Файл данных сокращается только до последнего выделенного экстента. Если указан данный параметр, то параметр target_percent не обрабатывается;
  • WITH NO_INFOMSGS — подавляет все информационные сообщения со степенями серьезности от 0 до 10.

Синтаксис SHRINKDATABASE

DBCC SHRINKDATABASE ( database_name | database_id | 0 )

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

DBCC SHRINKFILE (N’TestBase_log’)

В данном случае мы осуществим сжатие файла журнала (TestBase_log – это название файла журнала транзакций), до его начального значения, т.е. до значения по умолчанию. Для того чтобы сжать файл до определенного размера, укажите вторым параметром размер в мегабайтах. Например, следующей инструкцией мы уменьшим размер файла журнала транзакций до 5 мегабайт.

DBCC SHRINKFILE (N’TestBase_log’ , 5)

Также необходимо учесть, что если Вы укажете размер меньше того, чем требуется для хранения данных в файле, то файл до этого размера сжат не будет. Например, допустим, если Вы указали 5 мегабайт, а для хранения данных в файле требуется 7 мегабайт, файл будет сжат только до 7 мегабайт.

SHRINKFILE также имеет параметры NOTRUNCATE и TRUNCATEONLY.

Синтаксис SHRINKFILE

DBCC SHRINKFILE ( { file_name | file_id } { | ] } )

Рекомендации и важные моменты при сжатии базы данных

  • Операция сжатия базы данных может вызвать фрагментацию индексов и замедлить работу БД. Поэтому слишком часто не рекомендуется выполнять сжатие базы данных;
  • Сжимать БД лучше до операции перестроения индексов, т.е. после сжатия запустите процедуру перестроения индексов;
  • Параметр базы данных AUTO_SHRINK (автоматическое сжатие) лучше не выставлять в значение ON, а оставлять по умолчанию, т.е. в OFF, если конечно у Вас нет на это достаточно серьезных оснований;
  • Инструкция SHRINKDATABASE не позволяет уменьшить размер базы данных до размера, который меньше начального, т.е. минимального. Однако инструкция SHRINKFILE сделать это может (вторым параметром указываем размер меньше минимального). Минимальный размер базы данных — это размер, который указан при создании базы данных или явно установленный операцией изменения размера БД, такой как DBCC SHRINKFILE или ALTER DATABASE. Например, если база данных была создана с размером 10 мегабайт, потом увеличилась до 100 мегабайт, ее можно сжать с помощью SHRINKDATABASE только до начальных 10 мегабайт, даже если все данные были удалены из базы данных;
  • Сжимать файлы базы данных и журнала транзакций нельзя, когда идет процесс их резервирования. И наоборот, создавать резервные копии базы и журнала транзакций нельзя пока идет процесс их сжатия;
  • Выполнение инструкции DBCC SHRINKDATABASE без указания параметра NOTRUNCATE или TRUNCATEONLY равносильно выполнению инструкции DBCC SHRINKDATABASE с параметром NOTRUNCATE после выполнения инструкции DBCC SHRINKDATABASE с параметром TRUNCATEONLY;
  • В процессе сжатия базы данных пользователи могут работать в ней (т.е. переводить БД в однопользовательский режим не нужно);
  • В любой момент времени Вы можете прервать процесс выполнения операций SHRINKDATABASE и SHRINKFILE, при этом вся выполненная работа сохраняется;
  • Перед запуском процедуры сжатия проверьте, есть ли свободное пространство для удаления в файлах базы данных, т.е. можно ли вообще сжать файлы, выполнив следующий запрос (он покажет в мегабайтах, на сколько Вы можете уменьшить файлы БД).
  • SELECT Name AS NameFile, size/128.0 — CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS INT)/128.0 AS AvailableSpaceInMB FROM sys.database_files;

  • Для того чтобы выполнить процедуру сжатия БД необходимо быть членом группы роли сервера sysadmin или роли базы данных db_owner;
  • Сжатие файлов базы данных и журнала транзакций достаточно ресурсоемкий процесс, требующий определенного количества времени (в зависимости от размера файлов), поэтому данную процедуру необходимо планировать и вообще выполнять ее только в случае крайней необходимости (например, размер БД и журнала стал слишком велик и больше половины отдельно взятого файла занимает неиспользуемое пространство).

Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.

На этом у меня все, надеюсь, статья была Вам полезна, удачи!

Нравится6Не нравится1

Секционирование («партицирование») в SQL Server, при кажущейся простоте («да чего там – размазываешь таблицу и индексы по файловым группам, получаешь профит в администрировании и производительности») – достаточно обширная тема. Ниже я попробую описать как создать и применить функцию и схему секционирования и с какими проблемами можно столкнуться. О преимуществах я говорить не буду, кроме одного — переключение секций, когда вы моментально убираете из таблицы огромный набор данных, либо наоборот — моментально загружаете в таблицу не менее огромный набор.
Как гласит msdn: «Данные секционированных таблиц и индексов подразделяются на блоки, которые могут быть распределены по нескольким файловым группам в базе данных. Данные секционируются горизонтально, поэтому группы строк сопоставляются с отдельными секциями. Все секции одного индекса или таблицы должны находиться в одной и той же базе данных. Таблица или индекс рассматриваются как единая логическая сущность при выполнении над данными запросов или обновлений».
Там же перечислены основные преимущества:

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

Другими словами, секционирование применяется для горизонтального масштабирования. Таблица/индексы «размазываются» по разным файловым группам, которые могут находиться на разных физических дисках, что значительно повышает удобство администрирования и, теоретически, позволяет повысить производительность запросов к этим данным – можно либо читать только нужную секцию (меньше данных), либо читать всё параллельно (устройства разные, читается быстро). Практически же, всё несколько сложнее и повышение производительности запросов к секционированным таблицам может работать только в том случае, если в ваших запросах используется отбор по тому полю, по которому вы проводили секционирование. Если у вас ещё нет опыта работы с секционированными таблицами, просто учтите, что производительность ваших запросов может не то, чтобы не измениться, но может ухудшиться, после того как вы секционируете свою таблицу.
Поговорим о стопроцентном преимуществе, которое вы однозначно получаете вместе с секционированием (но которым тоже нужно суметь воспользоваться) – это гарантированное повышение удобства управления вашей БД. Например, у вас есть таблица с миллиардом записей, из которых 900 миллионов относятся к старым («закрытым») периодам и используются только для чтения. С помощью секционирования вы можете вынести эти старые данные в отдельную файловую группу только для чтения, забэкапить её и больше не тащить их во все свои ежедневные бэкапы – скорость создания резервной копии возрастёт, а размер уменьшится. Вы можете перестраивать индекс не по всей таблице, а по выбранным секциям. Кроме того, вырастает доступность вашей БД – если одно из устройств, содержащих файловую группу с секцией, выйдет из строя, остальные будут по-прежнему доступны.
Чтобы добиться остальных преимуществ (мгновенное переключение секций; повышение производительности) – нужно специально проектировать структуру данных и писать запросы.
Предполагаю, что уже достаточно смутил читателя и теперь уже можно переходить к практике.
Во-первых, создадим базу с 4 файловыми группами, в которой будем проводить эксперименты:
create database on primary (name =’PTestPrimary’, filename = ‘E:\data\partitionTestPrimary.mdf’, size = 8092KB, filegrowth = 1024KB) , filegroup (name =’PTestFG1′, filename = ‘E:\data\partitionTestFG1.ndf’, size = 8092KB, filegrowth = 1024KB) , filegroup (name =’PTestFG2′, filename = ‘E:\data\partitionTestFG2.ndf’, size = 8092KB, filegrowth = 1024KB) , filegroup (name =’PTestFG3′, filename = ‘E:\data\partitionTestFG3.ndf’, size = 8092KB, filegrowth = 1024KB) log on (name = ‘PTest_Log’, filename = ‘E:\data\partitionTest_log.ldf’, size = 2048KB, filegrowth = 1024KB); go alter database set recovery simple; go use partitionTest;
Создадим таблицу, которую будем мучать.
create table ptest (id int identity(1,1), dt datetime, dummy_int int, dummy_char char(6000));
И заполним её данными за один год:
;with nums as ( select 0 n union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 ) insert into ptest(dt, dummy_int, dummy_char) select dateadd(hh, rn-1, ‘20180101’) dt, rn dummy_int, ‘dummy char column #’ + cast(rn as varchar) from ( select row_number() over(order by (select (null))) rn from nums n1, nums n2, nums n3, nums n4 )t where rn < 8761
Теперь таблица pTest содержит по одной записи за каждый час 2018-го года.
Теперь нужно создать функцию секционирования, описывающую граничные условия для разделения данных на секции. SQL Server поддерживает только секционирование по диапазонам.
Мы будем секционировать нашу таблицу по столбцу dt (datetime) таким образом, чтобы каждая секция содержала в себе данные за 4 месяца (тут я облажался — на самом деле первая секция будет содержать данные за 3, вторая за 4, третья за 5 месяцев, но для целей демонстрации — это не проблема)
create partition function pfTest (datetime) as range for values (‘20180401’, ‘20180801’)
Вроде бы всё нормально, но здесь я сознательно допустил одну «ошибку». Если посмотреть синтаксис в msdn, то вы увидите, что при создании можно указывать к какой секции будет относиться указанная граница – к левой, или к правой. По умолчанию, по какой-то неведомой причине, указанная граница относится к «левой» секции, поэтому для моего случая корректно было бы создать функцию секционирования следующим образом:
create partition function pfTest (datetime) as range right for values (‘20180401’, ‘20180801’)
В то время, как я, фактически, выполнил:
create partition function pfTest (datetime) as range left for values (‘20180401’, ‘20180801’)
Но к этому мы вернёмся позже и пересоздадим нашу функцию секционирования. Пока же продолжим с тем, что получилось, чтобы понять, что же получилось и почему это не очень хорошо для нас.
После создания функции секционирования, нужно создать схему секционирования. Она чётко привязывает секции к файловым группам:
create partition scheme psTest as partition pfTest to (, , )
Как вы видите, все три наши секции будут лежать в разных файловых группах. Теперь пришло время секционировать нашу таблицу. Для этого нам нужно создать кластерный индекс и вместо указания файловой группы, в которой он должен располагаться, указать схему секционирования:
create clustered index cix_pTest_id on pTest(id) on psTest(dt)
И здесь тоже я допустил «ошибку» в текущей схеме – я вполне мог создать уникальный кластерный индекс по этому столбцу, однако, при создании уникального индекса, столбец, по которому производится секционирование, обязательно должен входить в индекс. А я хочу показать с чем можно столкнуться при такой конфигурации.
Теперь посмотрим, что же мы получили в текущей конфигурации (запрос взят отсюда):
SELECT sc.name + N’.’ + so.name as , si.index_id as , si.type_desc as , si.name as , stat.row_count AS , stat.in_row_reserved_page_count * 8./1024./1024. as , stat.lob_reserved_page_count * 8./1024./1024. as , p.partition_number AS , pf.name as , CASE pf.boundary_value_on_right WHEN 1 then ‘Right / Lower’ ELSE ‘Left / Upper’ END as , prv.value as , fg.name as FROM sys.partition_functions AS pf JOIN sys.partition_schemes as ps on ps.function_id=pf.function_id JOIN sys.indexes as si on si.data_space_id=ps.data_space_id JOIN sys.objects as so on si.object_id = so.object_id JOIN sys.schemas as sc on so.schema_id = sc.schema_id JOIN sys.partitions as p on si.object_id=p.object_id and si.index_id=p.index_id LEFT JOIN sys.partition_range_values as prv on prv.function_id=pf.function_id and p.partition_number= CASE pf.boundary_value_on_right WHEN 1 THEN prv.boundary_id + 1 ELSE prv.boundary_id END /* For left-based functions, partition_number = boundary_id, for right-based functions we need to add 1 */ JOIN sys.dm_db_partition_stats as stat on stat.object_id=p.object_id and stat.index_id=p.index_id and stat.index_id=p.index_id and stat.partition_id=p.partition_id and stat.partition_number=p.partition_number JOIN sys.allocation_units as au on au.container_id = p.hobt_id and au.type_desc =’IN_ROW_DATA’ /* Avoiding double rows for columnstore indexes. */ /* We can pick up LOB page count from partition_stats */ JOIN sys.filegroups as fg on fg.data_space_id = au.data_space_id ORDER BY , , , ;

Таким образом, мы получили три не очень удачные секции – первая хранит данные с начала времён по 01.04.2018 00:00:00 включительно, вторая – с 01.04.2018 00:00:01 по 01.08.2018 00:00:00 включительно, третья с 01.08.2018 00:00:01 до конца света (доли секунд я сознательно упустил, потому что не помню с какой градацией SQL Server записывает эти доли, но смысл передан верно).
Теперь создадим некластерный индекс по полю dummy_int, «выровненный» по той же схеме секционирования.
А зачем нам выровненный индекс?выровненный индекс нам нужен, чтобы мы могли выполнять операцию переключения секции (switch) – а это одна из тех операций, ради которой, зачастую, и заморачиваются с секционированием. Если в таблице есть хотя бы один невыровненный индекс — вы не сможете выполнить переключение секции

create nonclustered index nix_pTest_dummyINT on pTest(dummy_int) on psTest(dt);
И посмотрим, почему я говорил, что ваши запросы могут стать медленнее, после внедрения секционирования. Выполним запрос:
SET STATISTICS TIME, IO ON; select id from pTest where dummy_int = 54 SET STATISTICS TIME, IO OFF;
И посмотрим статистику выполнения:
Table ‘ptest’. Scan count 3, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
И план выполнения:

Поскольку наш индекс «выровнен» по секциям, условно, на каждой секции создан свой собственный индекс, «не связанный» с индексами на других секциях. Условий на поле, по которому секционирован индекс, мы не наложили, поэтому SQL Server вынужден выполнять Index Seek в каждой секции, фактически, 3 Index Seek вместо одного.
Давайте попробуем исключить одну секцию:
SET STATISTICS TIME, IO ON; select id from pTest where dummy_int = 54 and dt < ‘20180801’ SET STATISTICS TIME, IO OFF;
И посмотрим статистику выполнения:
Table ‘ptest’. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Ага, одна секция была исключена и поиск нужного значения вёлся только в двух секциях.
Это то, о чём обязательно нужно помнить, принимая решение о секционировании. Если у вас есть запросы, которые не используют ограничение по тому полю, по которому секционирована таблица, у вас может возникнуть проблема.
Некластерный индекс нам больше не нужен, поэтому я его удаляю
drop index nix_pTest_dummyINT on pTest;
А зачем был нужен некластерный индекс?он, в общем-то и не был нужен, тоже самое я мог показать и с кластерным индексом, не знаю зачем его создавал, но раз уж сделал и скриншотов понаделал — не пропадать же добру
Теперь рассмотрим следующий сценарий: данные из этой таблицы мы каждые 4 месяца архивируем – убираем старые данные и добавляем секцию для следующих четырёх месяцев (организация «скользящего окна» описана в msdn и куче блогов).
Разобьём задачу на мелкие и понятные подзадачи:

  1. Добавим секцию для данных с 01.01.2019 по 01.04.2019
  2. Создадим пустую stage-таблицу
  3. Переключим секцию с данными до 01.04.2018 в stage-таблицу
  4. Избавимся от пустой секции

Поехали:
1. Объявляем, что новая секция будет создана в файловой группе FG1, потому что она у нас скоро освободится:
alter partition scheme psTest next used ;
И меняем функцию секционирования, добавляя новую границу:
SET STATISTICS TIME, IO ON; alter partition function pfTest() split range (‘20190101’); SET STATISTICS TIME, IO OFF;
Смотрим статистику:
Table ‘ptest’. Scan count 1, logical reads 76171, physical reads 0, read-ahead reads 753, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table ‘Worktable’. Scan count 1, logical reads 7440, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Всего в таблице (кластерном индексе) 8809 страниц, так что количество чтений, конечно, за гранью добра и зла. Посмотрим, что у нас теперь есть по секциям.

В целом, всё как и ожидалось – появилась новая секция с верхней границей (помните, что граничные условия у нас относятся к левой секции) 01.01.2019 и пустая секция, в которой будут остальные данные, у которых дата больше.
Вроде бы всё нормально, но почему так много чтений? Посмотрим внимательно на рисунок выше, и увидим, что данные из третьей секция, которые были в FG3 оказались в FG1, а вот следующая секция, пустая, в FG3.
2. Создаём stage-таблицу.
Для переключения (switch) секции в таблицу и обратно, нам требуется пустая таблица, в которой созданы все те же ограничения и индексы, что и на нашей секционированной таблице. Таблица должна быть в той же файловой группе, что и секция, которую мы хотим туда «переключить». Первая (архивная) секция лежит в FG1, поэтому создаём таблицу и кластерный индекс там же:
create table stageTest (id int identity(1,1), dt datetime, dummy_int int, dummy_char char(6000)) ; create clustered index cix_stageTest_id on stageTest(id) on ;
Секционировать эту таблицу не нужно.
3. Теперь мы готовы к переключению:
SET STATISTICS TIME, IO ON; alter table pTest switch partition 1 to stageTest SET STATISTICS TIME, IO OFF;
И вот, что мы получаем:
Сообщение 4947, уровень 16, состояние 1, строка 59 ALTER TABLE SWITCH statement failed. There is no identical index in source table ‘PartitionTest.dbo.pTest’ for the index ‘cix_stageTest_id’ in target table ‘PartitionTest.dbo.stageTest’ .
Забавно, посмотрим, что у нас в индексах:
select o.name tblName, i.name indexName, c.name columnName, ic.is_included_column from sys.indexes i join sys.objects o on i.object_id = o.object_id join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id join sys.columns c on ic.column_id = c.column_id and o.object_id = c.object_id where o.name in (‘pTest’, ‘stageTest’)

Помните, я писал, что нужно было делать уникальный кластерный индекс на секционированной таблице? Вот именно поэтому и нужно было. При создании уникального кластерного индекса, SQL Server потребовал бы явного включения столбца, по которому мы секционируем таблицу, в индекс, а так он добавил его сам и забыл сказать об этом. И я правда не понимаю почему так.
Но, в общем, проблема понятна, пересоздаём кластерный индекс на stage-таблице.
create clustered index cix_stageTest_id on stageTest(id, dt) with (drop_existing = on) on ;
И теперь ещё раз пробуем выполнить переключение секции:
SET STATISTICS TIME, IO ON; alter table pTest switch partition 1 to stageTest SET STATISTICS TIME, IO OFF;
Та-дам! Секция переключена, смотрим чего нам это стоило:
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 3 ms.
А ничего. Переключение секции в пустую таблицу и наоборот (полной таблицы в пустую секцию) – это операция исключительно над метаданными и это именно то, из-за чего секционирование — это очень и очень крутая штука.
Посмотрим, что там с нашими секциями:

А с ними всё здорово. В первой секции осталось ноль записей, они благополучно уехали в таблицу stageTest. Можем двигаться дальше
4. Всё, что нам осталось – это удалить нашу пустую первую секцию. Выполним и посмотрим, что произойдёт:
SET STATISTICS TIME, IO ON; alter partition function pfTest() merge range (‘20180401’); SET STATISTICS TIME, IO OFF;
И это тоже операция только над метаданными, в нашем случае. Смотрим на секции:

У нас осталось, как и было, всего 3 секции, каждая в своей файловой группе. Миссия выполнена. Что можно было бы тут улучшить? Ну, во-первых, хотелось бы, чтобы граничные значения относились к «правым» секциям, чтобы секции содержали все данные за 4 месяца. И хотелось бы, чтобы создание новой секции обходилось меньшей кровью. Читать данных в десять раз больше, чем сама таблица – перебор.
С первым мы сделать сейчас ничего не можем, а вот со вторым – попробуем. Создадим новую секцию, которая будет содержать данные с 01.01.2019 по 01.04.2019, а не до конца времён:
alter partition scheme psTest next used ; SET STATISTICS TIME, IO ON; alter partition function pfTest() split range (‘20190401’); SET STATISTICS TIME, IO OFF;
И видим:
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
Ха! То есть теперь это операция только над метаданными? Да, если вы «делите» пустую секцию – это операция только над метаданными, поэтому правильным решением будет держать и слева, и справа по гарантированно пустой секции и при необходимости выделения новой – «вырезать» их оттуда.
Посмотрим теперь, что произойдёт, если я захочу вернуть данные из stage-таблицы назад в секционированную таблицу. Для этого мне будет нужно:

  1. Создать новую секцию слева для данных
  2. Переключить (switch) таблицу в эту секцию

Пробуем (и помним, что stageTest в FG1):
alter partition scheme psTest next used ; SET STATISTICS TIME, IO ON; alter partition function pfTest() split range (‘20180401’); SET STATISTICS TIME, IO OFF;
Видим:
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table ‘ptest’. Scan count 1, logical reads 2939, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Ну неплохо, т.е. прочитали только левую секцию (которую делим) и всё. Окей. Чтобы переключить несекционированную непустую таблицу в секцию секционированной таблицы, на таблице-источнике обязательно нужны ограничения, чтобы SQL Server знал, что всё будет хорошо и переключение можно сделать как операцию над метаданными (а не читать всё подряд и проверять – подходит под условия секции или нет):
alter table stageTest add constraint check_dt check (dt <= ‘20180401’)
Пробуем переключить:
SET STATISTICS TIME, IO ON; alter table stageTest switch to pTest partition 1 SET STATISTICS TIME, IO OFF;

Статистика:
SQL Server Execution Times: CPU time = 15 ms, elapsed time = 39 ms.
Опять-таки, операция только над метаданными. Смотрим, что там с нашими секциями:

Окей. Вроде разобрались. А теперь попробуем пересоздать функцию и схему секционирования (я удалил схему и функцию секционирования, пересоздал и перезаполнил таблицу и заново создал кластерный индекс по новой схеме секционирования):
create partition function pfTest (datetime) as range right for values (‘20180401’, ‘20180801’)
Посмотрим, какие секции есть у нас сейчас:

Отлично, теперь у нас три «логичных» секции – с начала времен до 01.04.2018 00:00:00 (не включительно), с 01.04.2018 00:00:00 (включительно) по 01.08.2018 00:00:00 (не включительно) и третья, всё, что больше или равно 01.08.2018 00:00:00.
Теперь попробуем выполнить ту же задачу по архивации данных, которую мы выполняли с предыдущей функцией секционирования.
1. Добавляем новую секцию:
alter partition scheme psTest next used ; SET STATISTICS TIME, IO ON; alter partition function pfTest() split range (‘20190101’); SET STATISTICS TIME, IO OFF;
Смотрим статистику:
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table ‘ptest’. Scan count 1, logical reads 3685, physical reads 0, read-ahead reads 4, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table ‘Worktable’. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Неплохо, по крайней мере разумно – прочитали только крайнюю секцию. Смотрим, что там у нас по секциям:
Обратите внимание, что теперь, заполненная третья секция осталась на месте, в FG3, а новая пустая секция создалась в FG1.
2. Создаём stage-таблицу и ПРАВИЛЬНЫЙ кластерный индекс по ней
create table stageTest (id int identity(1,1), dt datetime, dummy_int int, dummy_char char(6000)) ; create clustered index cix_stageTest_id on stageTest(id, dt) on ;
3. Переключаем секцию
SET STATISTICS TIME, IO ON; alter table pTest switch partition 1 to stageTest SET STATISTICS TIME, IO OFF;
Статистика говорит, что операция над метаданными:
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 5 ms.
Теперь уже всё без сюрпризов.
4. Убираем ненужную секцию
SET STATISTICS TIME, IO ON; alter partition function pfTest() merge range (‘20180401’); SET STATISTICS TIME, IO OFF;
А вот тут нас ждёт сюрприз:
Table ‘ptest’. Scan count 1, logical reads 27057, physical reads 0, read-ahead reads 251, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Смотрим, что там у нас с секциями:
И вот тут становится понятно: наша секция #2 переехала из файловой группы fg2 в файловую группу fg1. Класс. Можем ли мы с этим что-то сделать?
Можем, просто нам всегда надо иметь пустую секцию и «уничтожать» границу между «вечнопустой» левой секций и той секцией, которую мы «переключили» (switch) в другую таблицу.
В качестве заключения:

  1. Используйте полный синтаксис create partition function, не полагайтесь на значения по умолчанию – вы можете получить не то, что хотели.
  2. Держите слева и справа по пустой секции – они вам очень пригодятся при организации «скользящего окна».
  3. Split и merge непустых секций – это всегда больно, по возможности избегайте этого.
  4. Проверьте свои запросы — если они не используют фильтр по тому столбцу, по которому вы планируете секционировать таблицу и вам нужна возможность переключения секций — их производительность может значительно снизиться.
  5. Если вы хотите что-то сделать, сначала протестируйте не в продакшене.

Надеюсь, материал был полезен. Возможно вышло скомкано, если считаете, что что-то из заявленного не раскрыто, пишите, постараюсь доделать. Спасибо за внимание.

Доброго времени суток! Как известно, в sql server появилась разновидность таблиц — файловые таблицы, File tables. У меня есть веб-приложение asp.net mvc, использующее бд sql server 2012. В базе хранятся некие текстовые статьи. В статьях используется множество ссылок на изображения, которые хранятся в файловой системе. Проблема в том, что при бэкапе базы и последующем развертывании бэкапа надо еще позаботиться об архивировании и последующем разархивировании всезх изображений (ссылки на которые, как я говорил хранятся в базе). Вот тут и хотелось бы применить эту самую технологию FileTable чтобы сами изображения по-прежнему хранились в файловой системе, но при этом sql server «знал» бы о них через FileTable. В итоге я завел такую таблицу, добавил в нее свои файлы, но теперь не знаю, как к ним обращаться из веб-приложения, чтобы это выглядело так же, как обращение к обычным файлам. Я могу открыть место хранения этих файлов из интерфейса sql server management studio (правый клик на моей файловой таблице, выбираю пункт Explore FileTable Directory), вижу там все свои файлы, но не знаю, как их использовать. Допустим я хочу поместить изображение из этой папки на веб-странице. Что нужно писать в тег img? Раньше это было так:

<img src=’/images/mypicture.png’ />

сейчас я пробовал вот так:

<img src=’\\sql-server-name\mssqlserver\MyFiles\mypicture.png’ />

К сожалению это не дало рещзультата.

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

Зачем все это

Файловые группы! Секционирование таблиц и индексов! Размещение таблицы на нескольких дисках! Собственные индексы для таблиц и другие замечательные возможности СУБД, которые платформа 1С не поддерживает «из коробки».

Все это применяется во многих других системах, но не у нас, ведь мы используем более продвинутые технологии:

  • Нет смысла разделять базу, таблицы или индексы на отдельные файлы для распределения по дискам, ведь в век SSD это пустая трата времени.

  • База стала большой, неповоротливой и с множеством ошибок в данных? Начнем жизнь с чистого листа (ну или почти с чистого) — свертка базы все решает!

  • Ускорение бэкапирования за счет отказа от сохранения исторических данных в базе — тоже не про нас. Ведь бэкапировать один файл базы удобнее.

  • Проблемы блокировок и неактуальных статистик вообще к нам не относятся, потому что платформа 1С сама все оптимизирует.

Уже сейчас на просторах нашей Родины все чаще можно встретить внедрения информационных систем на базе 1С с активным количество пользователей более 1000, а размером баз более 1 ТБ. Я думаю (или очень сильно надеюсь), что именно при подобных внедрениях работа с базами 1С меняется, а это привносит новые требования как к самой платформе, так и к необходимым компетенциям администраторов и разработчиков. Одним из таких требований является иной подход к обслуживанию базы данных, который нельзя сделать стандартными средствами платформы 1С. Как вы могли догадаться, речь идет о секционировании таблиц и индексов.

Одним из самых важных требований к обслуживанию больших, высоконагруженных баз является эффективная структура индексов, которую нельзя полностью создать средствами платформы 1С. Но об этом мы уже говорили в предыдущей статье «Создаем свои индексы для баз 1С. Со своей структурой и настройками!», поэтому сейчас на этом заострять внимание не будем.

Ниже поговорим об использовании секционирования таблиц и индексов в базах 1С, плюсах и минусах, подводных камнях и целесообразности.

Все, что будет ниже, относится к клиент-серверному режиму работы и рассматривается в контексте Microsoft SQL Server. Но, фактически, может быть использовано и на PostgreSQL.

Вопросы лицензирования

В статье нет раскрытия вопроса лицензирования SQL Server и доступности функций в зависимости от редакции СУБД. Подробности Вы можете .

Принцип работы

Что же такое секционирование и для чего оно используется? В общих чертах, секционирование — это разбиение таблиц и индексов на некоторые блоки, в качестве которых может выступать файловая группа (логическое разделение) или файл (физическое разбиение). Блоки могут быть разных размеров, находиться на разных дисках и иметь различные специфичные для них настройки. Как обычно, вся самая подробная информация о секционировании SQL Server находится в официальной документации, мы же рассмотрим несколько примеров его использования с описанием плюсов и минусов этого подхода.

Для SQL Server создание секций выполняется в несколько этапов. Опустим этап проектирования и рассмотрим по шагам простой пример. У нас есть информационная база 1С «Partitioning», структура метаданных которой состоит из 2 документов, 4 регистров накопления и 4 справочников.

Структура метаданных дана просто для информации, все примеры будут на 1 или 2 таблицах. Как можно догадаться, примеры с секционированием будут выполнены на регистрах «Продажи_Секции» и «ТоварыНаСкладах_Секции». На стороне SQL Server эти объекты представлены несколькими таблицами. Нас интересуют только физические таблицы для упрощения примеров. Таблицы итогов и служебные таблицы секционировать не будем.

Метаданные Поле 1С Поле SQL
Имя таблицы
РегистрНакопления.ТоварыНаСкладах_Секции Период _Period
_AccumRg84 Регистратор _RecorderTRef
Регистратор _RecorderRRef
НомерСтроки _LineNo
Активность _Active
ВидДвижения _RecordKind
Склад _Fld85RRef
Номенклатура _Fld86RRef
Количество _Fld87
РегистрНакопления.Продажи_Секции Период _Period
_AccumRg69 Регистратор _RecorderRRef
НомерСтроки _LineNo
Активность _Active
Подразделение _Fld70RRef
Контрагент _Fld71RRef
Сумма _Fld72

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

Создание файловых групп

Для начала создадим логические блоки базы данных — файловые группы. Сделать это можно как с помощью SQL-скрипта, так и с помощью графического интерфейса в SQL Managment Studio (SSMS).

USE GO ALTER DATABASE ADD FILEGROUP GO ALTER DATABASE ADD FILEGROUP GO ALTER DATABASE ADD FILEGROUP GO

В результате, кроме основной файловой группы PRIMARY имеем три дополнительных: FG1, FG2, FG3.

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

Добавление файлов

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

USE GO ALTER DATABASE ADD FILE ( — Настройки размещения и автоувеличение файла NAME = N’Partitioning_FG1′, FILENAME = N’D:\DBs\Partitioning_FG1.ndf’ , SIZE = 1024KB , FILEGROWTH = 10%) — Принадлежность файла к файловой группе TO FILEGROUP GO ALTER DATABASE ADD FILE ( NAME = N’Partitioning_FG2′, FILENAME = N’D:\DBs\Partitioning_FG2.ndf’, SIZE = 1024KB, FILEGROWTH = 10%) TO FILEGROUP GO ALTER DATABASE ADD FILE ( NAME = N’Partitioning_FG3′, FILENAME = N’D:\DBs\Partitioning_FG3.ndf’, SIZE = 1024KB, FILEGROWTH = 10%) TO FILEGROUP GO

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

Определение функции и схемы секционирования

Тут начинается самое интересное. Нам необходимо определить как данные в таблице или индексах будут распределяться между секциями. Для этого используются функции секционирования. Как упоминалось выше, таблицы содержат данные с 2010 по 2019 год. Допустим, нам нужно распределить данные по годам между секциями по такому принципу:

Файловая группа Фильтр данных
FG1 до 2010 года включительно
FG2 с 2011 по 2014 год включительно
FG3 с 2015 по 2018 год включительно
PRIMARY с 2019 года по текущий момент

Создать функцию секционирования можно только с помощью SQL-скрипта. В нашем случае он будет выглядеть так.

USE GO CREATE PARTITION FUNCTION — Тип колонки исходной таблицы, по которой — будет выполняться секционирование (datetime2(0)) — Указание к какой области интервала значений — принадлежит аргумент в части «FOR VALUES» AS RANGE LEFT — Платформа 1С хранит даты с некоторым смещением, — которое обычно установлено в 2000 лет, чтобы — иметь возможность хранить пустую дату «01.01.0001» — из 1С в виде «01.01.2001» на стороне SQL Server. — Поэтому здесь все даты в 4-ом тысячелетии 🙂 FOR VALUES ( N’4010-12-31T23:59:59.000′, N’4014-12-31T23:59:59.000′, N’4018-12-31T23:59:59.000′ ) GO

Тип колонки секционирования соответствует типы поля «_Period» в таблице регистра. Через SSMS можно увидеть новый объект в разделе «Хранилище».

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

USE GO CREATE PARTITION SCHEME — Используемая функция секционирования AS PARTITION — Файловые группы указаны в том порядке, — в котором указаны значения фильтров — при создании функции секционирования TO (, , , ) GO

В списке объектов базы созданную схему можно также заменить в разделе «Хранилище».

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

Применяем секционирование

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

  • «ТоварыНаСкладах_Секции» (таблица «_AccumRg84»)
  • «Продажи_Секции» (таблица «_AccumRg69»)

Обе таблицы имеют кластерный индекс, поэтому будет достаточно применить схему секционирования к нему и всем некластеризованным индексам (которых у каждой таблицы по 1 для полей «Регистратор» + «НомерСтроки»). Для этого необходимо пересоздать индексы с явным указанием схемы секционирования. Вот полный скрипт для таблицы «_AccumRg84». Для «_AccumRg69» скрипт будет аналогичным, только имя таблицы и индексов нужно поменять.

USE GO CREATE UNIQUE CLUSTERED INDEX ON . ( ASC, ASC, ASC, ASC )WITH ( — Пересоздать индекс заново, если существует DROP_EXISTING = ON, — Включить инкрементальную статистику — Об этом в статье далее STATISTICS_INCREMENTAL = ON) — Указываем схему секционирования и колонку таблицы, — к которой эта схема применяется ON (_Period) GO CREATE UNIQUE NONCLUSTERED INDEX ON . ( ASC, ASC, ASC — Для секционирования в индексе должен присутствовать столбец секционирования — поэтому стандартный платформенный индекс приходится изменять ASC )WITH ( DROP_EXISTING = ON, STATISTICS_INCREMENTAL = ON) ON (_Period) GO

Для упрощения составления скрипта можно использовать возможности SSMS по генерации DDL-команд для существующих объектов (таблицы и индексы). Сформированные автоматически скрипты можно использовать как шаблоны. Результатом скрипта будет разбиение таблиц и ее индексов на секции. Проверим результат для таблицы «_AccumRg84» и ее кластерного индекса с помощью этого скрипта.

Номер секции Количество строк в секции
1 (FG1) 4111890
2 (FG2) 1059512
3 (FG3) 82034
4 (PRIMARY) 536

Итог: основные таблицы регистров разбиты на секции с учетом файловых групп базы данных. Но для чего мы все это делали и что делать дальше?

Примечание! Какое бывает секционирование и что такое сегментирование

Необходимо дать некоторое пояснение по поводу секционирования, а также связанного понятия — сегментирование. Выделяют три основных вида секционирования:

  • Горизонтальное (обычно его и называют сегментированием) — разбиение таблиц и индексов на части по выбранному ключу (полю).
  • Вертикальное — применяется для сокращения операций ввода-вывода за счет переноса редко используемых столбцов таблицы в отдельную сущность (так, например, делают при сохранении файлов в отдельный регистр сведений в конфигурациях 1С).
  • Функциональное — в этом случае данные разделяются по контексту, бизнес-области. Например, данные клиентов отделены от складских данных с помощью схем (платформа 1С такое не применяет).

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

Итак, поехали!

Какие проблемы решает

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

Гибкое управление данными

В нашем примере есть три секции + одна стандартная. Предположим, что первая секция, хранящая данные регистров до 2011 года, должна сохраняться в системе в качестве архивных данных, при этом нужно снизить затраты дискового пространства для нее.

Поскольку к этим данным выполняется редкое обращение, то можно использовать сжатие PAGE для таблицы и индексов на этой секции. Сэкономим место на архивных данных, при этом сохраним уровень производительности при работе с остальными секциями (использование сжатия требует доп. ресурсов CPU).

ALTER INDEX _AccumRg84_1 — При указании секции для сжатия обязательно — указывать перестроение всех секций (REBUILD PARTITION=ALL ) ON _AccumRg84 REBUILD PARTITION=ALL — При сжатии указываем номер секции WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1))

Проверим результат с помощью этого скрипта.

Таблица Объект Номер секции Сжатие
_AccumRg84 _AccumRg84_1 1 PAGE
_AccumRg69 _AccumRg69_1 1 PAGE

Кроме сжатия, для отдельных секций доступны:

  • Перенос данных, что может быть актуальным при переносе данных из OLTP в OLAP
  • Операции обслуживания
  • Операции бэкапирования
  • И др.

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

Повышение эффективности дисковой подсистемы

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

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

Часто даются рекомендации по переносу базы tempdb на отдельный диск для улучшения производительности, т.к. это позволяет снизить конкуренцию за дисковые ресурсы между основной базой данных и tempdb. Представьте какие возможности у Вас появятся для оптимизации операций ввода-вывода, если основную базу можно будет расположить на нескольких дисках.

Оптимизация стратегии бэкапирования

В этом случае все сводится к простому правилу — бэкапировать нужно лишь то, что меняется. Если файловая группа FG1 не меняется уже 6 лет, то зачем делать ее регулярный бэкап?

Вместо этого можно оптимизировать стратегию бэкапирования, делая резервную копию только «свежих» данных. В нашем случае для файловой группы FG1 можно установить режим «Только для чтения», чтобы в ней никто не смог поменять данные, в т.ч. и через 1С.

USE GO declare @readonly bit SELECT @readonly=convert(bit, (status & 0x08)) FROM sysfilegroups WHERE groupname=N’FG1′ if(@readonly=0) ALTER DATABASE MODIFY FILEGROUP READONLY GO

Теперь при попытке изменить данные в старом периоде через 1С появится ошибка на уровне СУБД. Это необходимо учитывать и делать проверки на уровне решения 1С.

Вернемся к формированию бэкапа. Допустим, изначально для базы использовалась полная модель бэкапирования, ежедневно ночью был настроен бэкап полный и лога транзакции каждые 30 минут. Появилась проблема, что из-за большого объема базы полный бэкап выполняется длительное время и мешает работе пользователей и регламентных заданий. Примерный скрипт для формирования полного бэкапа может быть таким.

BACKUP DATABASE TO DISK = N’D:\DBs\Backup\Partitioning.bak’ WITH NOFORMAT, NOINIT, NAME = N’Partitioning-Полная База данных Резервное копирование’, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM GO

Вместо этого сделаем резервное копирование только тех данных, что могут меняться, а файловую группу FG1 в режиме «Только для чтения» исключим из резервной копии. Предполагается, что резервная копия файловой группы FG1 уже есть и ее повторное создание не имеет смысла.

BACKUP DATABASE — Перечисляем файловые группы для создания резервной копии FILEGROUP = N’PRIMARY’, FILEGROUP = N’FG2′, FILEGROUP = N’FG3′ TO DISK = N’D:\DBs\Backup\Partitioning.bak’ WITH NOFORMAT, NOINIT, NAME = N’Partitioning-Полная База данных Резервное копирование’, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM GO

Конечно, работать с такими бэкапами нужно немного иначе, но особых проблем с восстановлением данных не будет. Например, если была повреждена архивная файловая группа FG1, то ее восстановить проще всего, т.к. установлен режим только для чтения.

RESTORE DATABASE FILE = N’Partitioning_FG1′ FROM DISK = N’D:\DBs\Backup\FG1.bak’ WITH FILE = 1, NOUNLOAD, STATS = 10 GO

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

USE — Создаем резервную копию заключительного фрагмента журнала транзакции — и устанавливаем состояние базы в «NORECOVERY» BACKUP LOG TO DISK = N’D:\DBs\Backup\Last_LogBackup.bak’ WITH NOFORMAT, NOINIT, NAME = N’Last_LogBackup’, NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5 — Восстанавливаем состояние базы на указанный момент времени (параметр STOPAT) RESTORE DATABASE FROM DISK = N’D:\DBs\Backup\WeeklyBackup.bak’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5 RESTORE LOG FROM DISK = N’D:\DBs\Backup\LogBackup1.trn’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5 RESTORE LOG FROM DISK = N’D:\DBs\Backup\LogBackup2.trn’ WITH FILE = 1, NOUNLOAD, STATS = 5, STOPAT = N’2019-02-08T16:15:13′ — Момент времени для восстановления GO

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

Что может быть лучше, чем быстрый бэкап 🙂

Улучшение процедур обслуживания

Для ускорения процедур обслуживания индексов и статистик можно выполнять операции на отдельных секциях.

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

USE GO ALTER INDEX ON . — Указание конкретной секции для перестроения — В обычных ситуациях выполняется перестроение всех — секций, что аналогично указанию «REBUILD PARTITION = ALL» REBUILD PARTITION = 4 GO

Окей, с индексами все понятно, но как же статистика? Иногда обслуживание всех статистик может занимать даже больше времени, чем обслуживание индексов. При этом гистограмма распределения значений по таблице / индексу, чем в принципе и является статистика, не рассчитывается для каждой отдельной секции. Но решение все же есть. Начиная с версии SQL Server 2014 появилась так называемая инкрементальная статистика, которая может пересчитываться по секциям.

По умолчанию объекты базы данных не поддерживают инкрементальную статистику, потому что платформа 1С не включает их явно. Включить данный вид статистики для индекса можно при создании / перестроении.

CREATE UNIQUE CLUSTERED INDEX ON . ( ASC, ASC, ASC — Включение инкрементальной статистики для индекса — Кстати, мы это уже делали в одном из предыдущих скриптов 🙂 )WITH (DROP_EXISTING = ON, STATISTICS_INCREMENTAL = ON) ON (_Period) GO

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

UPDATE STATISTICS .() — Указываем конкретную секцию для обновления статистики WITH RESAMPLE ON PARTITIONS(4);

Для подробной информации о работе инкрементальной статистики и ее «внутренней кухне» рекомендую изучить статью «SQL Server 2014 : New incremental statistics», а также на MSDN. В них есть подробное описание как работает инкрементальная статистика, в каких случаях ее стоит использовать, ограничения и др. Если у Вас в базе огромные таблицы, то инкрементальная статистика может быть настоящим спасением при оптимизации обслуживания.

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

Проблемы блокировок

С тех пор, как платформа использует свой «костыль» в виде менеджера управляемых блокировок и режим изоляции транзакций Read Commited Snapshot Isolation (RCSI), то проблемы блокировок на уровне SQL Server стало значительно меньше. Однако проблема эскалации блокировок все еще актуальна, т.к. она не решается использованием управляемых блокировок.

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

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

Отличное описание есть на сайте Вячеслава Гилева, за что ему большое спасибо.

Плюсы и минусы

Все имеет свои плюсы и минусы, и секционирование тут не исключение.

Плюсы:

  • Гибкое управление данными, за счет действий над отдельными секциями (сжатие, перенос на отдельный диск, перенос данных на другие инстансы, бэкапирование и др.)
  • Ускорение операций обслуживания (перестроение индексов и обновление статистик по секциям).
  • Повышение производительности запросов для некоторых ситуаций. Эту ситуацию мы не рассматривали, но происходит это за счет:
    • Исключение обращений к секциям, которые не соответствую фильтрам запроса.
    • За счет разнесения секций на отдельные диски.

Минусы:

  • Сложность администрирования и поддержки, т.к. требуются дополнительные компетенции.
  • Сложность при разработке баз данных, т.к. секционирование должно учитываться при модификации базы.
  • Как ни странно, секционирование может вызвать проблемы производительности в некоторых запросах. Например, из-за дополнительной операции соединения наборов данных из разных секций. Это стоит учитывать при планировании инфраструктуры и написания SQL-запросов.

Мы не будем отдельно останавливаться на каждом пункте, т.к. тогда статья станет очень большой и превратиться в книгу.. Более подробную информацию Вы всегда можете узнать на MSDN. Главное что нужно понять, что секционирование не является простым решением, поэтому перед его использованием нужно взвесить все плюсы и минусы. Особенно это важно в контексте платформы 1С, где нет полной власти над базой данных (она как бы есть, но ее как бы нет :)).

Проблемы в мире 1С

В контексте платформы 1С секционирование имеет свои особенности и подводные камни, а именно:

  • Лицензионное соглашение фирмы «1С» запрещает использовать недокументированные возможности. Только Вы ответственны за то, что делаете. Сам факт нарушения соглашения может как минимум вылиться в отказ в технической поддержки.
  • Проблемы при обновлении конфигурации, а именно реструктуризации таблиц.
    • Поскольку платформа 1С ничего не знает о секциях, то при реструктуризации все настройки таблиц и индексов будут сброшены на стандартные и секции будут «затерты».
    • При обновлении платформы 1С на новую версию или отказ от совместимости в конфигурации может привести к значительным изменениям на уровне базы, что может противоречить сделанными Вами изменениям. Например, ранее платформа хранила тип «Хранилище значений» с помощью SQL-типа «IMAGE». В одной из версий платформы этот тип был заменен на «VARBINARY». Если такие ситуации не обнаружить, то в лучшем случае реструктуризация прервется с ошибкой, а в худшем случится потеря данных.
  • Архитектура таблиц метаданных в большинстве решений противоречит основным требованиям секционирования.
    • Типовые конфигурации в большинстве таблиц имеют разделитель данных с типом «numeric», который включен во все индексы. Если Вы используете разделитель, то может понадобиться секционировать не просто по периоду, а по периоду с учетом разделителя. Проблема в том, что SQL Server поддерживает только указание одного поля секционирования. Решение тут — создавать виртуальное поле, о котором 1С ничего знать не будет, но этот подход мы сейчас не будем описывать. Если кому-то интересно — пишите в комментариях.
    • Не все типовые индексы можно просто так взять и секционировать, потому что не все они содержат поле секционирования, а это обязательное условие. Выше был пример, когда для включения секционирования пришлось добавлять поле «Период» в индекс по регистратору.
    • И многие другие специфические проблемы, с которыми можно столкнуться.
  • Топорное построение SQL-запросов платформой «1С» сводит на нет выигрыш в производительности для запросов по большим таблицам. Например, выше выполнено секционирование таблицы «_AccumRg84». Обслуживание ускорили, архивные данные сжимаем и поставили только для чтения, а бэкапы теперь выполняются гораздо быстрее. Но вот исключение обращений к архивным секциям в запросах не работает. Выполняя такой запрос из 1С мы ожидали, что будет прочитана только секция в файловой группе «PRIMARY». Вот текст запроса и план его выполнения.

exec sp_executesql N’ SELECT CAST(COUNT_BIG(T1._RecorderRRef) AS NUMERIC(12)) FROM dbo._AccumRg84 T1 WHERE ((T1._Period >= @P1) AND (T1._Period <= @P2)) ‘ — Все даты преобразуются к типу datetime2(3), — фактически период хранится с типом datetime2(0) ,N’@P1 datetime2(3),@P2 datetime2(3)’ ,’4019-01-01 00:00:00′,’4019-01-31 23:59:59′

Обратите внимание, что запрос секционированный и фактически обработано 4 секции, что не правильно. Все дело в том, что платформа по неведомой причине преобразовывает все параметры дат в SQL-запросах к типу «datetime(3)», хотя в таблицах даты хранятся с типом «datetime(0)». Для SQL Server это важно, т.к. происходит неявное преобразование типов и СУБД не может использовать секции. Если убрать преобразование дат и сразу поставить нужный тип «datetime(0)», то ситуация кардинально изменяется.

exec sp_executesql N’ SELECT CAST(COUNT_BIG(T1._RecorderRRef) AS NUMERIC(12)) FROM dbo._AccumRg84 T1 WHERE ((T1._Period >= @P1) AND (T1._Period <= @P2)) ‘ — Убираем преобразование типов к datetime2(3) ,N’@P1 datetime2(0),@P2 datetime2(0)’ ,’4019-01-01 00:00:00′,’4019-01-31 23:59:59′

Как можно заметить, запрос остается секционированным, но прочитана всего 1 секция. Это может быть критическим для больших операций чтения, например для Table Scan. Ведь прочитать одну секцию вместо 4 все же лучше.

Крик души

Вообще, платформа 1С имеет множество таких проблем, которые всплывают на стороне СУБД и не видны на небольших базах. Кроме случая с излишним преобразованием типов в запросах есть множество других проблем:

  • Неоптимальные запросы при наличии разделителя данных.
  • Тяжелые запросы в динамических списках, если в качестве основного источника используется регистр сведений.
  • Недостаточные индексы для метаданных.
  • Медленная запись больших наборов записей, даже с учетом оптимизаций в 8.3.12.
  • Решение с индексами для регистров бухгалтерии при наличии более 3 субконто или большого количества измерений.
  • И многое другое.

Безвыходных ситуаций не бывает, но иногда это очень мешает. 🙂

Первую проблему с лицензионным соглашением мы решить не в силах, можно с ней только жить и принять. Проблему с архитектурой таблиц метаданных и особенными запросами платформы решить можно, но это уже выходит за рамки статьи. Ниже лишь кратко продемонстрируем решение проблемы с реструктуризациями, чтобы в один прекрасный момент не потерять секции. Если Вам интересно как исправить запросы платформы 1С или архитектуру таблиц метаданных на стороне БД пишите в комментариях, может это будет стимул для новой статьи.

Костыли и палки

В статье «Создаем свои индексы для баз 1С. Со своей структурой и настройками!» мы говори про создание и поддержку неплатформенных индексов для баз 1С. Для решения проблем удаления собственных индексов при реструктуризации использовались глобальные триггеры, перехватывающие события создания таблиц и индексов платформой и добавляющие свои нужные действия (создание индексов, изменение параметров индексов и таблиц и др.).

Этот же подход подойдет и для сохранения настроек секционирования, но с некоторыми особенностями.

CREATE TRIGGER ON ALL SERVER AFTER CREATE_INDEX AS BEGIN SET NOCOUNT ON; — В случае возникновения ошибок продолжаем работу SET XACT_ABORT OFF; DECLARE @SchemaName SYSNAME, @TableName SYSNAME, @DatabaseName SYSNAME, @IndexName SYSNAME; SELECT @TableName = EVENTDATA().value(‘(/EVENT_INSTANCE/TargetObjectName)’,’SYSNAME’) SELECT @SchemaName = EVENTDATA().value(‘(/EVENT_INSTANCE/SchemaName)’,’SYSNAME’) SELECT @IndexName = EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectName)’,’SYSNAME’) SELECT @DatabaseName = EVENTDATA().value(‘(/EVENT_INSTANCE/DatabaseName)’,’SYSNAME’); — Здесь запускаем скрипт перестроения индекса с учетом схемы секционирования — Для индексов, которые не содержат поле секционирования, также выполняем — их перестроение с добавлением этого поля. Если необходимо, то — включаем инкрементальную статистику. — Дополнительно можно учитывать файловые группы только для чтения и отключать — эту настройку на время реструктуризации. — Возвращаем значение по умолчанию для ситуаций с ошибками в транзакции SET XACT_ABORT ON; END

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

Это конец

Вот и все. На самом деле ничего сложного, если понимать для чего это нужно.

Нужно ли это использовать на практике? Решать только Вам, но если хоть один из пунктов к Вам относится, то секционирование точно не для Вас:

  • Используется файловый режим работы информационной базы
  • Нет никаких проблем производительности и стабильности информационной системы
  • Считаете большой ошибкой выход за пределы экосистемы платформы 1С
  • Вы сотрудник фирмы «1С”

В случае если у Вас высоконагруженная база, то рассмотреть возможность секционирования стоит, но делать это должен либо эксперт, либо архитектор 1С вместе с DBA.

Другие ссылки

Тема секционирования не новая, на Инфостарт она уже рассматривалась и было бы правильно добавить ссылки на эти материалы.

  • «Давайте забудем о свертке БД? Файловые группы и секции таблиц SQL, сжатие таблиц SQL.»
  • Вопросы разработки, анализа производительности и оптимизации приложений 1С под управлением СУБД ORACLE
  • Управление индексами и секциями в 1С
  • Особенности работы платформы 1С с СУБД OracleDatabase
  • Уровни абстракции: Корреляции проектирования метаданных и проектирования СУБД. Тонкости механизмов СУБД на высоконагруженных системах на примере MSSQL