Сжатие данных в SQL Server (COMPRESSION). Низкая производительность выполнения запросов. Сжимаем базу данных с помощью среды Management Studio

Многие администраторы 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 [ , target_percent ] [ , { NOTRUNCATE | TRUNCATEONLY } ]) [ WITH NO_INFOMSGS ]

Для того чтобы сжать только журнал транзакций можно использовать инструкцию 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 } { [ , EMPTYFILE ] | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ] }) [ WITH NO_INFOMSGS ]

Операция сжатия базы данных может вызвать фрагментацию индексов и замедлить работу БД. Поэтому слишком часто не рекомендуется выполнять сжатие базы данных;
Сжимать БД лучше до операции перестроения индексов, т.е. после сжатия запустите процедуру перестроения индексов;
Параметр базы данных 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;

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

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

Целью данной статьи является "отговорить" от выполнения свертки БД пользователей клиент-серверного варианта 1С, за счет использования несколько более "продвинутых" технологий.

1) Увеличение размера БД

2) Низкая производительность выполнения запросов

3) Большой объём "ненужных данных" которые мешают работе пользователей

II) "Технологические" решения проблем

в) Сжатие таблиц БД

г) Секционирование таблиц БД

В современных условиях очень странно бывает иногда слышать "нам нужно свернуть БД 1С - её объём превышает 50 ГБ". Если бы такое собирались сделать администраторы систем SAP R3 или Oracle e Business Suite или даже MS Dynamics Ax их бы наверное уволили. Тем не менее, для 1С это является "стандартной практикой".

Для файловых версий история тянется ещё с версии 1С 7.7 с ограничением в 2ГБ на размер базы. Сейчас ограничение 2ГБ уже только на размер таблицы, размер файла уже может получиться очень и очень не маленьким. Правда если база у вас выросла до такого размера, то наверное туда активно вносились данные - может нужно задуматься о клиент-сервере?

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

I) Проблемы, которые мы пытаемся решать сверткой БД

1) Увеличение размера БД

Собственно главный вопрос: а для чего уменьшать размер БД?

Давайте приложим немного математики:

Серверный жесткий диск на 500 ГБ стоит около 10 т.р. Объединить в RAID 1 для надежности будет 20 т.р.

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

А покупка внешнего дискового массива уже обойдётся не так дешево. Что же делать?

Да всё просто - разместить файлы БД на сетевом диске, а как? Ну об этом статье далее.

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

Кроме того, если база уже размером не 60 а, к примеру, 120 ГБ... малейшая ошибка в коде 1С при свертке и всё... процедура заканчивается не удачно. А ошибки точно будут. Как "недостаточно памяти" при работе с ТЗ, так и ошибки вроде

http://img180.imageshack.us/img180/656/1c3y.jpg

Итоговая цифра получается 30-40 т. минимум против 20-25 в случае покупки жесткого диска, и получения 500 ГБ дополнительного места

Поэтому появляются продукты вроде http://infostart.ru:8080/public/78934/

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

И даже после этого найдутся пользователи которым "вдруг неожиданно понадобились" стертые данные, которые "как раз хотели поправить" каку-то циферку, которая "не влияет на последовательнсти" в документе закрытого периода. А хуже если выяснится что кто-то эти документы смотрел постоянно для каких-то только ему ведомых целей. Конечно это всё лишь ошибки в методике работы, но тем не менее недовольство пользователей будет.

2) Низкая производительность выполнения запросов

Ну кто же сказал что "чем меньше тем быстрее"? Для корректно разработанной ИС это утверждение не верно.

На рисунке ниже кратко и "на птичьем языке" приведен простейший пример выборки по индексу типа B-Дерева записи в таблице адресов:

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

Аналогия - записная книжка: Каждая страница начинается со своей буквы, только вот на каждой странице ещё такая же записная книжка в которой вы можете выбрать вторую букву в слове, и так до тех пор, пока не встретите ту страницу, на которой будет одна или несколько записей. Удобно? Конечно удобно, в случае если у вас больше нескольких сотен контактов. А если у вас их всего десять? Не проще ли их просто записать на один листочек, который можно просмотреть глазами? Вот и в случае индексов так же. Он эффективен если в таблице несколько тысяч записей, а вот если одна единственная - не очень. Благо СУБД научились самостоятельно выбирать "план запроса" и решать использовать или не использовать тот или иной индекс. Вот только в случае "перебора" всех строк таблицы без индекса СУБД очень часто блокирует всю эту таблицу, и вы наблюдаете "непонятно откуда взявшиеся блокировки" после свертки ИБ.

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

3) Большой объём "ненужных данных" которые мешают работе пользователей

Об этом вы пользователям сперва сделайте рассылку. И получите кучу сообщений что "данные ненужными не бывают". Тем не менее многим не нравится "видеть документы за прошлые периоды" и "архивные данные", с ними нельзя не согласиться. Но решает ли сверка эти проблемы? Убирает ли она ненужные номенклатуры из номенклатурного справочника? Контрагентов с которыми больше не будет вестись работа? А как показывает практика большинство проблем именно в этом. II) "Технологические" решения проблем

а) Разделение БД на файловые группы

Открываем Management Studio в списке баз выбираем нужную, открываем её свойства.

Переходим на вкладку "Файловые группы" как показано на рисунке, и добавляем ещё одну файловую группу (на примере она названа SECONDARY)

Переходим на вкладку "Файлы" и добавляем новый файл, для которого выбираем созданную файловую группу. Этот файл МОЖНО РАСПОЛОЖИТЬ НА ДРУГОМ ДИСКЕ

Теперь используя обработку к примеру:http://infostart.ru/public/78049/ определяем какие таблицы мы можем смело "пожертвовать" на более медленный (ну или наоборот всё на медленный, остальные - на более быстрый) носитель. Правило 80/20 здесь действует. 80% операций проводятся с 20% данными, так что думайте какие таблички вам нужны оперативно, а какие не очень. "Хранилище дополнительной информации", документы ввода начальных остатков, документы которые уже не используете сразу определяйте как те которые можно перенести в "медленную" файловую группу.

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

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

б) Размещение БД или её части на сетевом диске

DBCC TRACEON (1807)

Но прошу быть очень внимательными при использовании данной команды в случае если у вас пропадёт сеть при работе с БД вся БД на время её отсутствия будет не доступной. Microsoft не зря закрыли эту возможность для массового использования. Вообще эта возможность предполагается для создания баз на NAS хранилищах, что и настоятельно рекомендую. Подойдёт так же стабильный и надежный файловый сервер, имеющий прямое подключение к серверу на котором запущен MS SQL СУБД.

Подробнее про другие флаги трассировки можно прочитать в статье http://msdn.microsoft.com/ru-ru/lipary/ms188396.aspx

в) Сжатие таблиц БД

EXEC sp_MSforeachtable "ALTER INDEX ALL ON ? REBUILD WITH (DATA_COMPRESSION = PAGE)" GO

После выполнения этого кода все таблицы в БД будут сжаты. Очевидно, что можно сжимать и таблицы по отдельности... это как бы на ваш выбор. Что даёт сжатие?

Экономия дискового пространства

Снижение нагрузки на дисковую подсистему

Что расходуется? - процессорное время.

Так что если у вас процессор загружен всё время на 70% и выше - сжатие вам использовать нельзя. Если 20-30% загрузка процессора, и при этом очередь к диску вырастает до 3-4... то сжатие таблиц - как раз "лекарство" для вас. Подробнее про сжатие таблиц БД -http://msdn.microsoft.com/ru-ru/lipary/cc280449(v=sql.100).aspx

функция сжатия таблиц доступна только для обладателей версии Enterprise SQL Server

г) Секционирование таблиц БД

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

Вы не поверите, но и это тоже возможно, хотя и не очень просто:

Создаём функуцию секционирования по дате: create partition function YearSection(datetime)

as range right for values ("20110101");

Всё что до 2011 года будет попадать в одну секцию, всё что после - в другую.

Создаём схему секционирования

create partition scheme YearScheme

as partition YearSection to (SECONDARY, PRIMARY);

Этим говорим, что все данные до 11 года будут попадать в файловую группу "Secondary" а после - в "Primary"

На рисунке вы видите что выбор не доступен - всё правильно, секционирование таблиц возможно только в версии Enterprise MS SQL Server . Кластерный индекс отличить легко - картинка с круглыми скобками. Для РН и всех объектов 1С он создаётся. Для РН кластерный индекс по периоду есть всегда. Для документов и справочников хорошо бы конечно создать другой, который включает реквизит по которому будет секционирование... но это уже будет являться нарушением лицензионного соглашения.

2) Проблема низкой производительности запросов

Все действия, описанные выше не должны повлиять на скорость выполнения основных запросов. Более того, использование файловых групп и секций таблиц позволит вам разместить наиболее часто используемые данные на быстрых дисковых массивах, позволит поменять конфигурацию дисковых массивов, использовать небольшие по размеру i/o accelerator. Таким образом скорость выполнение запросов только повысится. А сжатие таблиц позволит вам дополнительно разгрузить дисковую подсистему, если она являлась узким местом. А вообще если говорить о скорости выполнения запросов, то анализ их планов выполнения, оптимизация запросов для грамотного использования индексов даст намного более существенный прирост производительности, чем все "ухищрения" на уровне MS SQL.

3) Проблема большого объёма "ненужных данных", которые мешают работе пользователей

Но для этого нужно не сворачивать базу, а проделать следующее:

а) Объяснить всем как пользоваться отборами, как они сохраняются, как пользоваться интервалами журнала, как они сохраняются

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

в) Настроить другие полезные "отборы по умолчанию" - например чтобы каждый менеджер по умолчанию видел только свои документы. А если хочет посмотреть документы "товарища" - нужно отключать отбор.

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

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

- Открываем Management Studio в списке баз выбираем нужную, открываем её свойства.
- Переходим на вкладку "Файловые группы" как показано на рисунке, и добавляем ещё одну файловую группу (на примере она названа SECONDARY)

- Переходим на вкладку "Файлы" и добавляем новый файл, для которого выбираем созданную файловую группу. Этот файл МОЖНО РАСПОЛОЖИТЬ НА ДРУГОМ ДИСКЕ

- Теперь используя обработку к примеру: определяем какие таблицы мы можем смело "пожертвовать" на более медленный (ну или наоборот всё на медленный, остальные - на более быстрый) носитель. Правило 80/20 здесь действует. 80% операций проводятся с 20% данными, так что думайте какие таблички вам нужны оперативно, а какие не очень. "Хранилище дополнительной информации", документы ввода начальных остатков, документы которые уже не используете сразу определяйте как те которые можно перенести в "медленную" файловую группу.

Выбираем таблицу которую нужно перенести в другую файловую группу - выбираем меню изменения таблицы (проект) и в свойствах меняем файловую группу:

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

DBCC TRACEON (1807)

Пишем данную команду в Management Studio, выполняем и можем успешно создавать базы по сети. Само собой при этом экземпляр SQL Server-а должен быть запущен от имени доменной учетной записи, и у этой записи должны быть права на нужную сетевую папку.
Но прошу быть очень внимательными при использовании данной команды в случае если у вас пропадёт сеть при работе с БД вся БД на время её отсутствия будет не доступной. Microsoft не зря закрыли эту возможность для массового использования. Вообще эта возможность предполагается для создания баз на NAS хранилищах, что и настоятельно рекомендую. Подойдёт так же стабильный и надежный файловый сервер, имеющий прямое подключение к серверу на котором запущен MS SQL СУБД.
Подробнее про другие флаги трассировки можно прочитать в статье http://msdn.microsoft.com/ru-ru/library/ms188396.aspx
Т.е. часть файловую группу можно вообще хранить в сети, а уж там дисковое пространство расширяется без проблем.

Создаём функуцию секционирования по дате:

create partition function YearSection(datetime)
as range right for values ("20110101");

Всё что до 2011 года будет попадать в одну секцию, всё что после - в другую.

Создаём схему секционирования

create partition scheme YearScheme
as partition YearSection to (SECONDARY, PRIMARY);

Этим говорим, что все данные до 11 года будут попадать в файловую группу "Secondary" а после - в "Primary"

Теперь осталось таблицу перестроить с разделением на секции. Для этого проще всего воспользоваться уже management studio, потому как процесс не простой. Вам нужно перестроить кластерный индекс для таблицы (который по сути и является самой таблицей), выбрав для индекса созданную схему секционирования:

Но для этого нужно не сворачивать базу, а проделать следующее:
а) Объяснить всем как пользоваться отборами, как они сохраняются, как пользоваться интервалами журнала, как они сохраняются
б) Пометить на удаление ненужные данные если они не несут никакой смысловой нагрузки (контрагентов и номенклатуру, с которыми больш не работаете) - этим вы принесёте пользователям больше пользы чем сверткой. В случае наличия ресурсов настроить автоматическую пометку на удаление неиспользуемых объектов и сделать отбор по умолчанию в программном коде для того чтобы не отображались по умолчанию не нужные пользователям объекты - помеченные на удаление
в) Настроить другие полезные "отборы по умолчанию" - например чтобы каждый менеджер по умолчанию видел только свои документы. А если хочет посмотреть документы "товарища" - нужно отключать отбор.

I) Проблемы, которые мы пытаемся решать сверткой БД
1) Увеличение размера БД
2) Низкая производительность выполнения запросов
3) Большой объём "ненужных данных" которые мешают работе пользователей

а) Разделение БД на файловые группы
б) Размещение БД или её части на сетевом диске
в) Сжатие таблиц БД
г) Секционирование таблиц БД
2) Проблема низкой производительности запросов
3) Проблема большого объёма "ненужных данных", которые мешают работе пользователей

I) Проблемы, которые мы пытаемся решать сверткой БД

1) Увеличение размера БД

Собственно главный вопрос: а для чего уменьшать размер БД?
Давайте приложим немного математики:
Серверный жесткий диск на 500 ГБ стоит около 10 т.р. Объединить в RAID 1 для надежности будет 20 т.р.
Естественно могут быть проблемы отсутствия места под новые жесткие диски в самом сервере...
А покупка внешнего дискового массива уже обойдётся не так дешево. Что же делать?
Да всё просто - разместить файлы БД на сетевом диске, а как? Ну об этом статье далее.

Увеличение доступного для БД дискового пространства обойдётся нам в 20 т.р. + 10 минут работы специалиста. Сколько часов работы специалиста потребует свертка БД? А сколько времени простоя может получиться? По самым скромным оценкам за свертку УПП объемом гигабайт в 60, со средним количеством ошибок, партионным учетом с проверкой результатов свертки, выправления этого же партионного учета возьмутся тысяч за 30-40.
Универсальной обработкой всё и сразу вряд ли свернётся, особенно если у вас база практически "никогда не останавливается". Партионный учет в любом случае выправлять. Вообщем много там работы. А самое главное, что итоговая проверка должна быть очень тщательной, и всё равно останутся ошибки...

Поэтому появляются продукты вроде [необходимо зарегистрироваться для просмотра ссылки]

2) Низкая производительность выполнения запросов

Ну кто же вам сказал что "чем меньше тем быстрее"? Для корректно разработанной ИС это утверждение не верно.
На рисунке ниже кратко и "на птичьем языке" приведен простейший пример выборки по индексу типа B-Дерева записи в таблице адресов:

II) "Технологические" решения проблем

1) Проблема увеличения размера БД
а) разделяем БД на файловые группы

Открываем Management Studio в списке баз выбираем нужную, открываем её свойства.
- Переходим на вкладку "Файловые группы" как показано на рисунке, и добавляем ещё одну файловую группу (на примере она названа SECONDARY

Переходим на вкладку "Файлы" и добавляем новый файл, для которого выбираем созданную файловую группу. Этот файл МОЖНО РАСПОЛОЖИТЬ НА ДРУГОМ ДИСКЕ

Выбираем таблицу которую нужно перенести в другую файловую группу - выбираем меню изменения таблицы (проект) и в свойствах меняем файловую группу:

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

б) Сохраняем БД на сетевом диске

DBCC TRACEON (1807)

Пишем данную команду в Management Studio, выполняем и можем успешно создавать базы по сети. Само собой при этом экземпляр SQL Server-а должен быть запущен от имени доменной учетной записи, и у этой записи должны быть права на нужную сетевую папку.
Но прошу быть очень внимательными при использовании данной команды в случае если у вас пропадёт сеть при работе с БД вся БД на время её отсутствия будет не доступной. Microsoft не зря закрыли эту возможность для массового использования. Вообще эта возможность предполагается для создания баз на NAS хранилищах, что и настоятельно рекомендую. Подойдёт так же стабильный и надежный файловый сервер, имеющий прямое подключение к серверу на котором запущен MS SQL СУБД.
Подробнее про другие флаги трассировки можно прочитать в статье [необходимо зарегистрироваться для просмотра ссылки]
Т.е. часть файловую группу можно вообще хранить в сети, а уж там дисковое пространство расширяется без проблем.

в) Сжатие таблиц базы данных

EXEC sp_MSforeachtable "ALTER INDEX ALL ON ? REBUILD WITH (DATA_COMPRESSION = PAGE)" GO

После выполнения этого кода все таблицы в БД будут сжаты. Очевидно, что можно сжимать и таблицы по отдельности... это как бы на ваш выбор. Что даёт сжатие?
- Экономия дискового пространства
- Снижение нагрузки на дисковую подсистему
Что расходуется? - процессорное время.
Так что если у вас процессор загружен всё время на 70% и выше - сжатие вам использовать нельзя. Если 20-30% загрузка процессора, и при этом очередь к диску вырастает до 3-4... то сжатие таблиц - как раз "лекарство" для вас. Подробнее про сжатие таблиц БД - [необходимо зарегистрироваться для просмотра ссылки]
Важное замечание - функция сжатия таблиц доступна только для обладателей версии Enterprise SQL Server

г) Секционирование таблиц БД

Создаём функуцию секционирования по дате:

create partition function YearSection(datetime)
as range right for values ("20110101");

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

create partition scheme YearScheme
as partition YearSection to (SECONDARY, PRIMARY);

Этим говорим, что все данные до 11 года будут попадать в файловую группу "Secondary" а после - в "Primary"

На рисунке вы видите что выбор не доступен - всё правильно, секционирование таблиц возможно только в версии Enterprise MS SQL Server. Кластерный индекс отличить легко - картинка с круглыми скобками. Для РН и всех объектов 1С он создаётся. Для РН кластерный индекс по периоду есть всегда. Для документов и справочников хорошо бы конечно создать другой, который включает реквизит по которому будет секционирование... но это уже будет являться нарушением лицензионного соглашения.

2) Низкая производительность выполнения запросов.

3) Большой объём "ненужных данных" которые мешают работе пользователей

Tags: 1С, 1С Оптимизация, sql, Администрирование 1С, Обучение, Программирование, Технологии

[необходимо зарегистрироваться для просмотра ссылки]