Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
 Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
Добрый день.

На MS SQL Server имеется БД. Ее размер 830ГБт. Появилось желание сжать ее и попробовать в работе. На тестовом сервере сжал исходя из этой статьи https://infostart.ru/1c/articles/692209/. По времени около 20 часов. После сжатия тестировали, в производительности выиграли сильно.

Исходя из то, что остановить работу на 20 часов не представляется возможным (объекты работают с утра до вечера 7 дней в неделю), вопрос: Как поэтапно, скажем за несколько ночей сжать базу? Имея ввиду еще то, что ночью запускается обслуживание БД (бекап, дефрагментация индексов, обновление статистики)

Пока на ум приходит алгоритм 1:
1. Получили список таблиц (в БД их порядка 5000)
2. Поделили на части (например по 500 таблиц), сохранили эти части где то в виде списков таблиц (получится 10 частей)
2. И каждую ночь прогоняем по одной части

Или алгоритм 2:
1. Каждую ночь получаем список таблиц и пробегаем по нему до наступления утра.
2. Если встречаем таблицу или индекс с параметром сжатия NONE, сжимаем его
По идее за несколько проходов вся база сожмется. Но я не нашел как получить параметр сжатия именно у индекса.

Или есть более грамотные решения?
22 ноя 21, 08:47    [22398887]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
архивариус
Member

Откуда:
Сообщений: 175
cad2206,

давно нашел скрипт (он для SAP изначально) может пригодится:

К сообщению приложен файл (sp_use_db_compression_242.sql - 46Kb) cкачать
22 ноя 21, 09:24    [22398892]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
архивариус,
посмотрел скрипт, мудрено. Но не понял в чем его особенность. Он так же долго будет сжимать базу. Или я что то проглядел?
22 ноя 21, 09:43    [22398897]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
aleks222
Member

Откуда:
Сообщений: 1846
cad2206
Добрый день.

На MS SQL Server имеется БД. Ее размер 830ГБт. Появилось желание сжать ее и попробовать в работе. На тестовом сервере сжал исходя из этой статьи https://infostart.ru/1c/articles/692209/. По времени около 20 часов. После сжатия тестировали, в производительности выиграли сильно.

Исходя из то, что остановить работу на 20 часов не представляется возможным (объекты работают с утра до вечера 7 дней в неделю), вопрос: Как поэтапно, скажем за несколько ночей сжать базу? Имея ввиду еще то, что ночью запускается обслуживание БД (бекап, дефрагментация индексов, обновление статистики)

Пока на ум приходит алгоритм 1:
1. Получили список таблиц (в БД их порядка 5000)
2. Поделили на части (например по 500 таблиц), сохранили эти части где то в виде списков таблиц (получится 10 частей)
2. И каждую ночь прогоняем по одной части

Или алгоритм 2:
1. Каждую ночь получаем список таблиц и пробегаем по нему до наступления утра.
2. Если встречаем таблицу или индекс с параметром сжатия NONE, сжимаем его
По идее за несколько проходов вся база сожмется. Но я не нашел как получить параметр сжатия именно у индекса.

Или есть более грамотные решения?


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

2. Ну хотите и не можете найти (хотя искали ли?) признак сжатия на таблице. Делаете списочек таблиц и по одной табличке из списка ночью сжимаете.

3. Займитесь более продуктивной деятельностью.
22 ноя 21, 10:37    [22398939]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
архивариус
Member

Откуда:
Сообщений: 175
cad2206,

никаких особенностей, он просто делает то что вам нужно. хотите свой пишите.
22 ноя 21, 10:39    [22398942]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
4es
Member

Откуда:
Сообщений: 32
cad2206
Но я не нашел как получить параметр сжатия именно у индекса.

select
   object_name(i.object_id) as ObjectName, i.name as IndexName, p.data_compression, p.data_compression_desc, p.partition_id
from sys.indexes i
inner join sys.partitions p
   on p.object_id = i.object_id and p.index_id = i.index_id
22 ноя 21, 10:42    [22398945]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
aleks222,

1. Сжал, отдал 1С программистам на тесты, 3 дня тестировали нагрузку и т.п. Показали результаты, прирост в производительности есть. Делали тестирование и исправление БД, все в норме. Чего ожидать можно еще?

2. Признак сжатия таблицы я могу найти, например так:
SELECT st.name,
       CAST(ROUND(((SUM(au.total_pages)*8)/1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
       CAST(ROUND(((SUM(au.used_pages)*8)/1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
       sp.index_id,
       sp.data_compression
FROM sys.tables st
LEFT JOIN  sys.partitions sp on st.object_id = sp.object_id
INNER JOIN sys.allocation_units au ON sp.partition_id = au.container_id

group by st.name, sp.index_id, sp.data_compression
order by UsedSpaceMB desc

Я не могу найти признак сжатия именно у индекса таблицы.
Сжимать по одной таблице за ночь 5000 таблиц, это как бы 13 лет)

3. "Займитесь более продуктивной деятельностью." - например? Я без всякого сарказма. Предполагаю, что чего то могу не понимать, поэтому и пишу тут.
22 ноя 21, 10:54    [22398956]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
архивариус,
спасибо. Но мне бы хотелось получить советы, как грамотно организовать алгоритм поэтапного сжатия, не останавливая работу.
22 ноя 21, 10:56    [22398960]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
4es,
спасибо
22 ноя 21, 11:05    [22398965]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35847
Блог
aleks222

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


Почему это чудеса?
Вполне реально, что производительность улучшилась просто за счет того, что теперь с диска меньше данных читается.
22 ноя 21, 12:16    [22399002]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
И вопрос, ответ на который поможет мне быстрее понять, как правильно сделать: команда ALTER TABLE 'TableName' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE) сжимает данные в таблице, или же только устанавливает флаг сжатия и сжимаются данные, которые пишутся в нее после установки флага?
Если только устанавливается флаг, то данные сжимаются командой DBCC SHRINKDATABASE?
22 ноя 21, 12:42    [22399026]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
aleks222
Member

Откуда:
Сообщений: 1846
cad2206
И вопрос, ответ на который поможет мне быстрее понять, как правильно сделать: команда ALTER TABLE 'TableName' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE) сжимает данные в таблице, или же только устанавливает флаг сжатия и сжимаются данные, которые пишутся в нее после установки флага?
Если только устанавливается флаг, то данные сжимаются командой DBCC SHRINKDATABASE?


Рано те "сжимать таблицы". Учиться надо.

ALTER TABLE 'TableName' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)

Толсто намекает. А время, потребное для оной операции, подтверждает толстый намек.
22 ноя 21, 13:30    [22399058]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
aleks222
Member

Откуда:
Сообщений: 1846
cad2206
aleks222,
1. Сжал, отдал 1С программистам на тесты, 3 дня тестировали нагрузку и т.п. Показали результаты, прирост в производительности есть. Делали тестирование и исправление БД, все в норме. Чего ожидать можно еще?


Ну я так понимаю, UPDATE STATISTICS with fullscan вы тоже делали?
И тоже тестировали?
22 ноя 21, 13:34    [22399069]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
.Евгений
Member

Откуда:
Сообщений: 707
Про сжатие онлайн автору темы кто-нибудь говорил, про версию его сервера спрашивали?
22 ноя 21, 13:55    [22399091]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
aleks222,
автор
Рано те "сжимать таблицы". Учиться надо.

Как раз я и учусь. Поэтому и вопросы задаю. А можно научиться не пробуя?

автор
ALTER TABLE 'TableName' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)

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

Да, уже убедился.


автор
Ну я так понимаю, UPDATE STATISTICS with fullscan вы тоже делали?

Нет, с параметром FULLSCAN не запускал, обновлял так:
use DBName
GO  
exe sp_updatestats

Стоит обновить с этим параметром?
22 ноя 21, 15:52    [22399155]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
.Евгений,
автор
Про сжатие онлайн автору темы кто-нибудь говорил

Пока не говорили, надеюсь Вы подскажете)

автор
про версию его сервера спрашивали?

Тестирую на 2014 и на 2019
22 ноя 21, 15:56    [22399159]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
.Евгений
Member

Откуда:
Сообщений: 707
cad2206,

сжатие возможно выполнять онлайн(не на всех версиях MS SQL). В большинстве случаев это позволяет не останавливать работу (но подтормаживать ее).
22 ноя 21, 16:20    [22399177]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9157
Надо еще понять, а какие действия именно ТС называет "сжатием базы".
22 ноя 21, 16:42    [22399183]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
.Евгений,
Понял, про что Вы.
alter index IndexName on TableName rebuild with (resumable = on, online = on)

Нужно экспериментировать.
22 ноя 21, 16:52    [22399194]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
Владислав Колосов,
Именно те действия, которые описаны в статье, ссылку на которую я указал в первом сообщении (https://infostart.ru/1c/articles/692209/) и затем возвратить свободное место на диск методом DBCC SHRINKDATABASE
22 ноя 21, 16:58    [22399201]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
msLex
Member

Откуда:
Сообщений: 9547
cad2206
.Евгений,
Понял, про что Вы.
alter index IndexName on TableName rebuild with (resumable = on, online = on)

Нужно экспериментировать.


C (resumable = on) осторожнее экспериментируйте.

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

Для полной отмены нужно выполнить alter index ... abort
22 ноя 21, 17:00    [22399207]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
SERG1257
Member

Откуда:
Сообщений: 2931
Критик
Вполне реально, что производительность улучшилась просто за счет того, что теперь с диска меньше данных читается.
Такое может получится только если CPU (лицензируемое) некуда девать, а дисковая система полный шлак.
Надувной матрас - классное решение для маленькой квартирки, но надувать его по вечерам и сдувать по утрам - так себе занятие.

cad2206
Или есть более грамотные решения?
С какого-то возраста появился вопрос «Зачем?» (с)
Мой хрустальный шар показывает зуд попробовать новую игрушку что для (объекты работают с утра до вечера 7 дней в неделю) может быть чреватым.
cad2206
"Займитесь более продуктивной деятельностью." - например? Я без всякого сарказма. Предполагаю, что чего то могу не понимать, поэтому и пишу тут.
ИМХО имеет смысл сжимать только партиции со старыми данными, которые никому особо не нужны, замерив выгоду для сжатия и решив стоит ли оно того.
Вместо сжатия ВСЕГО ПОДРЯД посмотрите на свои таблицы. Уверен что из 5000 большая часть это справочники которые сжимать нет смысла.
Выберите десяток другой больших таблиц (желательно секционированых по дате) и сжимайте только старые партиции в которых нет записи (а лучше и чтения). Их (монстров) можно сжимать и вручную написаным скриптом.
22 ноя 21, 17:03    [22399214]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
msLex
Member

Откуда:
Сообщений: 9547
SERG1257
Такое может получится только если CPU (лицензируемое) некуда девать, а дисковая система полный шлак.




Какой бы крутой не была дисковая подсистема, она все равно будет существенно уступать по скорости RAM.

При включении сжатия данных, в тот же объем RAM уместиться больше (иногда, значительно) данных, и это снизит (иногда, значительно) количество необходимых физических чтений.
22 ноя 21, 17:08    [22399220]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9157
cad2206,

Если после DBCC SHRINKDATABASE улучшилась производительность, то возможны проблемы с физическим носителем, начиная от фрагментации и заканчивая повторяющимися чтениями секторов диска. Либо диск как-то не так отформатирован. После сжатия производительность слегка ухудшается.
22 ноя 21, 17:09    [22399222]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
Владислав Колосов,
Хм, все СХД только SSD...
22 ноя 21, 17:18    [22399230]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить