Главная › Телефон › Сжатие данных в SQL Server (COMPRESSION). II) "Технологические" решения проблем. Применить сжатие или нет — это большой вопрос

Сжатие данных в SQL Server (COMPRESSION). II) "Технологические" решения проблем. Применить сжатие или нет — это большой вопрос

Сжатие данных доступно только в SQL Server Enterprise Edition, но начиная с SQL Server 2016 SP1, его добавили во все редакции. Нет так просто ответить на вопрос «будет ли польза от использования сжатия данных в конкретном случае или нет». Предлагаю рассмотреть это более детально.

Плюсы

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

Наиболее полезным будет использовать сжатие данных на так называемых «холодных данных», которые редко используются и, обычно, не хранятся в памяти, а читаются с диска.

Минусы

Сжаты могут быть только данные, которые хранятся в строках (in-row). Это означает, что длинные строки (более 8 Кб) и LOB данные (большие) не могут быть сжаты, так как они хранятся не в строках, а помещаются в специальное хранилище, а в строке остаётся ссылка на них.

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

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

Принцип работы простыми словами

Сжатие на уровне строк: переводит фиксированный тип данных в переменный. Например, int, обычно занимает фиксированно 4 байта, но если вы храните 123, то это значение может поместиться в 1 байт + метаданные на хранение переменной длины.

Сжатие на уровне страницы: первым делом применяет сжатие на уровне строк, затем страница сравнивается с некоторым словарём, применяются алгоритмы, которые находят повторяющиеся последовательности. Это позволяет сжать данные значительно сильнее.

Применить сжатие или нет — это большой вопрос

Рассмотрите большие таблицы. Вам не следует рассматривать сотни маленьких таблиц, сжатие которых не принесёт пользы.
Убедитесь, что объекты не фрагментированы перед применение компрессии (используйте sys.dm_db_index_physical_stats ). Фрагментация может привести с сильным погрешностям сжатия и вы не получите достаточного выигрыша
Проверьте как много данных выбранного объекта находится в памяти (используйте sys.dm_os_buffer_descriptors ). Если большая часть объекта находится в памяти, то вы не получите выигрыша от применения сжатия, так как они могут быть горячими. Но если такие данные редко используются, то вы всё равно можете получить преимущество от сжатия, сжатые данные будут заниматься меньше объёма памяти.
Посмотреть как часто используются объекты памяти можно с помощью sys.dm_db_index_operational_stats . Чем чаще объекты используются, тем больше CPU ресурсов будет затрачено.
Проверьте возможный процент сжатия с помощью sys.sp_estimate_data_compression_savings . Если выигрыш незначительный, не применяйте сжатие, в противном случае вы потратите ваши CPU ресурсы в пустую. Выигрыш от сжатия на уровне строк должен быть хотя 15%, для сжатия на уровне страниц не менее 30%.
Рассмотрите использование колоночных индексов. Сжатие уровня страниц, обычно, даёт уровень сжатия 50%, но колоночные индексы сжимают значительно сильнее (сжатие доходит до 10х). Колоночные индексы существенно отличаются от обычных, поэтому не применяйте их без изучение принципа их работы.

Вывод

Начиная с SQL Server 2016 сжатие доступно во всех редакциях, что позволяет использовать его в любых приложениях. Кроме сжатия, в SQL Server 2016, стали доступны и другие функции Enterprise Edition — это замечательный подарок от Microsoft.

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

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

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

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

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

Разделить таблицы на разные файловые группы конечно хорошо... но вы скажете что есть тут парочка таблиц... которые тянутся с 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"

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

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

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

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

Cжатие данных в SQL Server 2008.

Недавно мне пришлось выполнять миграцию своего хранилища данных с SQL Server 2005 на SQL Server 2008. Как известно, одним из новшеств SQL Server 2008 является сжатие данных. Эта возможность призвана увеличить производительность базы данных за счет сжатия данных и индексов в таблицах и индексированных представлениях и, как следствие, уменьшения операций ввода-вывода. Также, благодаря сжатию, может существенно уменьшится размер базы, что облегчает администрирование и управление. Все это звучало заманчиво, и я решил использовать эту возможность.

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

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

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

Было решено исследовать эффект от сжатия данных и провести тестирование, чтобы ответить на вопрос о падении производительности.

Подготовка теста.

Итак, есть таблица, назовем ее ProductMMR содержащая некие факты в нескольких разрезах.

Вот ее структура:

Склад

Товар

Дата

Тип склада

Количество

Исходный размер таблицы - 16 ГБ, индексов - 18 ГБ (я воспользовался системной ХП sp_spaceused для определения размеров данных и индексов).

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

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

Вот тестовый запрос на выборку к данной таблице:

SET STATISTICS TIME ON -- для измерения времени выполнения запроса

SET STATISTICS IO ON -- для измерения логических и физических операций ввода-вывода

SELECT

fact.DateID,

fact.StockID,

SUM(fact.Qty) AS Qty

FROM fact.ProductMMR fact

WHERE (fact.DateID BETWEEN @DateIDBegin AND @DateIDEnd)

GROUP BY fact.DateID, fact.StockID

Был задан временной промежуток 30 дней (в таблице фактов это соответствует более 150 млн. записей).

Определение стратегии сжатия и его реализация.

Подробно про реализацию сжатия можно почитать в MSDN. Там же описана реализация сжатия для страниц и для строк . Эффект от сжатия зависит от данных в таблице - насколько много там повторяющихся значений и каков тип данных.

Теперь перейдем к реализации сжатия, предварительно определив его стратегию.

Sunil Agarwal в своем блоге приводит ряд рекомендаций по этому поводу, позволю себе их обобщить и привести здесь:

1. Не имеет смысла сжимать данные или индексы имеющие малый размер. Если таблица занимает 10 страниц, и будет сжата до одной, то это не принесет выгоды в данном случае. Необходимо помнить о том, что сжатые данные должны быть распакованы каждый раз, когда к ним осуществляется доступ. Перед применением сжатия, вы должны оценить текущий размер таблицы/индекса и прогнозируемый размер после сжатия.

2. Если таблица интенсивно используется операторами DML и SELECT, то в результате сжатия вы можете получить чрезмерную нагрузку на процессоры в результате распаковки при каждом обращении к этим данным. В этом случае необходимо особо тщательно подойти к вопросу целесообразности сжатия таблицы/индекса.

3. Если экономия от сжатия невысока, тогда сжатие проводить не рекомендуется. Бывают такие случаи, когда размер сжатых данных оказывается больше несжатых. Это говорит о том, что в таблице используются наиболее компактные типы данных.

4. Если у вас типичное OLTP-приложение, в общем случае вам следует выбирать сжатие типа ROW. Этот тип сжатия менее затратный с точки зрения распаковки данных. Однако, как правило, сжатие типа PAGE более эффективно, в плане потенциального свободного пространства.

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

В моем случае я получил такие результаты:

Таблица 1.

Эффективность сжатия данных.

Тип сжатия	Размер до сжатия	Размер после сжатия	% сжатия
ROW	33,4 ГБ	22,7 ГБ	32 %
PAGE	33,4 ГБ	18,3 ГБ	45 %

Как видно из таблицы, получить эффект от сжатия данных можно. Хотя в данном случае, это не самый хороший показатель, во многих случаях данные сжимаются на 70-80%.

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

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

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

ALTER TABLE . REBUILD PARTITION = ALL

WITH

(DATA_COMPRESSION = ROW

)

Применить сжатие типа PAGE можно, применив параметр DATA_COMPRESSION = PAGE.

Указав DATA_COMPRESSION = NONE можно отменить сжатие данных.

Я не буду приводить здесь синтаксис сжатия индексов и партиций, интересующийся без труда найдет их в BOL.

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

Результаты тестирования.

Итак, до и после сжатия по типу PAGE был выполнен тестовый запрос.

Вот его результаты, на «разогретом» кэше:

Таблица 2.

Результаты теста № 1*.

Тип сжатия	Время выполнения запроса(мс)	Операций логического чтения**	Затраченное процессорное время (мс)
Без сжатия	26 147	1 419 113	308 736
Сжатие PAGE	41 104	709 360	486 453

*Запрос выполнялся на сервере с 12 ядрами и 32 Гб ОЗУ, дисковая подсистема 10 RAID.

** Показаны только операции логического чтения, т.к. физического чтения не было - данные находились в кэше.

Увидев эти результаты, можно удивиться - ведь операций логического чтения на сжатых данных было произведено в два раза меньше, но время выполнения запроса оказалось на 36 % больше. А все дело видимо в том, что хоть операций чтения меньше и читается все из памяти, но велики накладные расходы на распаковку данных. Ведь распаковывается не страница целиком, а каждая запись по отдельности.

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

Поэтому было решено провести еще один цикл тестов, но уже на холодном кэше.

Был выполнен то же самый тестовый запрос, но предварительно был очищен кэш процедур и буфер, при помощи команд DBCC FREEPROCCACHE и DBCC DROPCLEANBUFFERS .

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

1 419 105

1 420 868

235 266

Сжатие данных PAGE

48 887

707 495

710 105

416 689

Вот эти результаты подтверждают ранее высказанное предположение. Как видно, время выполнения отличается на 12 %, вместо 36 % из первого теста.

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

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

Но самая главная причина того, что в моем случае упала производительность запросов - это относительно невысокий коэффициент сжатия, менее 50 %. Я провел еще несколько тестов и обнаружил, что на тех таблицах, которые сжимались на 60-75 %, производительность запросов увеличивалась по сравнению с несжатыми таблицами.

Очевидно, что чем выше процент сжатия, тем сильнее это скажется на приросте производительности.

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

Сергей Харыбин, MCTS SQL Server.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Поэтому появляются продукты вроде 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

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

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

Создаём функуцию секционирования по дате: 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"

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

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

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

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

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

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

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

Давайте забудем о свертке БД? Файловые группы и секции таблиц SQL, сжатие таблиц SQL. October 10th, 2011

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

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

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

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

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

После выполнения этого кода все таблицы в БД будут сжаты. Очевидно, что можно сжимать и таблицы по отдельности... это как бы на ваш выбор. Что даёт сжатие?
- Экономия дискового пространства
- Снижение нагрузки на дисковую подсистему
Что расходуется? - процессорное время.
Так что если у вас процессор загружен всё время на 70% и выше - сжатие вам использовать нельзя. Если 20-30% загрузка процессора, и при этом очередь к диску вырастает до 3-4... то сжатие таблиц - как раз "лекарство" для вас. Подробнее про сжатие таблиц БД - http://msdn.microsoft.com/ru-ru/library/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"

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

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