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

Откуда:
Сообщений: 1840
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

Откуда:
Сообщений: 31
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

Откуда: Москва / Калуга
Сообщений: 35836
Блог
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

Откуда:
Сообщений: 1840
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

Откуда:
Сообщений: 1840
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

Откуда:
Сообщений: 9540
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

Откуда:
Сообщений: 9540
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]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
SERG1257
Member

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

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




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

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


Похоже мифы сжатия зохавали мир.

Даже мелкософт толкует только о "экономии дискового пр-ва".
Да оно и понятно, при считывании сжатой страницы в память она снова "разжимается".
Иначе данные с нее невозможно использовать.
22 ноя 21, 17:28    [22399235]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
msLex
Member

Откуда:
Сообщений: 9540
SERG1257
msLex
При включении сжатия данных, в тот же объем RAM уместиться больше (иногда, значительно) данных
Уверен, что в памяти как раз все блоки разжаты. разжатие/сжатие идет при чтении/записи (каждый раз).
Возможно что для ТС больше подойдет ROW сжатие (гораздо дешевле по ресурсам)

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

Откуда:
Сообщений: 1840
msLex
SERG1257
пропущено...
Уверен, что в памяти как раз все блоки разжаты. разжатие/сжатие идет при чтении/записи (каждый раз).
Возможно что для ТС больше подойдет ROW сжатие (гораздо дешевле по ресурсам)

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

Ваши доказательства? (c) Шварценеггер.
22 ноя 21, 17:32    [22399238]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
msLex
Member

Откуда:
Сообщений: 9540
aleks222
msLex
пропущено...




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

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


Похоже мифы сжатия зохавали мир.

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



Данные на диске в файле лежат 8Кб страницами. Не больше не меньше.

Все изменения с данными происходят только после поднятие этой страницы в память.
Как, скажите, отслеживать момент окончания страницы, если ее сжатый размер будет известен только при записи на диск?
22 ноя 21, 17:33    [22399239]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
aleks222
Member

Откуда:
Сообщений: 1840
msLex

Данные на диске в файле лежат 8Кб страницами. Не больше не меньше.

Свежо преданье.

msLex

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

Ты так и не ответил, как из "архива" достать строку, не распаковывая архив?
22 ноя 21, 17:35    [22399241]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
msLex
Member

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

Данные на диске в файле лежат 8Кб страницами. Не больше не меньше.

Свежо преданье.

Т.е. по вашему это не так?


aleks222

msLex

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

Ты так и не ответил, как из "архива" достать строку, не распаковывая архив?



Вот вам, почитайте как происходит сжатие данных при PAGE компрессии в SQL Server


https://docs.microsoft.com/ru-ru/sql/relational-databases/data-compression/page-compression-implementation?view=sql-server-ver15

Советую особенно обратить вот на эту часть


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


И подумать, может ли это быть совместимо с вашим утверждением "сжатие только при записи на диск"
22 ноя 21, 17:43    [22399248]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
aleks222
Member

Откуда:
Сообщений: 1840
Советую не читать савецких газет перед обедом.
Ну... или читать "в подлиннике".
22 ноя 21, 17:49    [22399251]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
msLex
Member

Откуда:
Сообщений: 9540
aleks222
Советую не читать савецких газет перед обедом.
Ну... или читать "в подлиннике".


Там так же черным по белому написано, что вы балабол

https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/page-compression-implementation?view=sql-server-ver15

When the page is full, the next row to be added initiates the page compression operation.


Сообщение было отредактировано: 22 ноя 21, 17:52
22 ноя 21, 17:51    [22399252]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
aleks222
Member

Откуда:
Сообщений: 1840
msLex


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



"После того как страница заполнена" => ДО момента сжатия страница НЕ сжата.

Очевидный факт.
Волга впадает в Каспийское море.
22 ноя 21, 17:53    [22399253]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

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

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


Большие таблицы выбрал так:
SELECT
  t.Name                                          AS TableName,
  s.Name                                          AS SchemaName,
  p.Rows                                          AS RowCounts,
  SUM(a.total_pages)*8/1024                       AS TotalSpace_MB,
  SUM(a.used_pages)*8/1024                        AS UsedSpace_MB,
  (SUM(a.total_pages) - SUM(a.used_pages))*8/1028 AS UnusedSpace_MB,
  t.create_date,
  t.modify_date
FROM
  sys.tables t
  INNER JOIN sys.indexes i ON t.object_id = i.object_id
  INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
  INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
  LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE
  t.Name NOT LIKE 'dt%'
  AND t.is_ms_shipped = 0
  AND i.object_id > 255
GROUP BY
  t.Name, s.Name, p.Rows
ORDER BY
  TotalSpace_MB desc;
GO


Буду признателен, если укажете где взять данные "только старые партиции в которых нет записи (а лучше и чтения)".
22 ноя 21, 17:57    [22399257]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
.Евгений
Member

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

интересно, не могут ли в памяти жить как сжатые, так и не сжатые страницы?
22 ноя 21, 17:59    [22399260]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
msLex
Member

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


пропущено...



"После того как страница заполнена" => ДО момента сжатия страница НЕ сжата.

Очевидный факт.
Волга впадает в Каспийское море.


Вы бы полностью прочитали, хоть


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

Как только данные без сжатия прекращают влезать, следующая же вставленная строка инициализирует процесс сжатия.
22 ноя 21, 17:59    [22399261]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
aleks222
Member

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

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


Большие таблицы выбрал так:
SELECT
  t.Name                                          AS TableName,
  s.Name                                          AS SchemaName,
  p.Rows                                          AS RowCounts,
  SUM(a.total_pages)*8/1024                       AS TotalSpace_MB,
  SUM(a.used_pages)*8/1024                        AS UsedSpace_MB,
  (SUM(a.total_pages) - SUM(a.used_pages))*8/1028 AS UnusedSpace_MB,
  t.create_date,
  t.modify_date
FROM
  sys.tables t
  INNER JOIN sys.indexes i ON t.object_id = i.object_id
  INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
  INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
  LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE
  t.Name NOT LIKE 'dt%'
  AND t.is_ms_shipped = 0
  AND i.object_id > 255
GROUP BY
  t.Name, s.Name, p.Rows
ORDER BY
  TotalSpace_MB desc;
GO


Буду признателен, если укажете где взять данные "только старые партиции в которых нет записи (а лучше и чтения)".


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

Откуда:
Сообщений: 1840
.Евгений
aleks222, msLex

интересно, не могут ли в памяти жить как сжатые, так и не сжатые страницы?

Это такой метод "экономии памяти"?
22 ноя 21, 18:00    [22399264]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
msLex
Member

Откуда:
Сообщений: 9540
.Евгений
интересно, не могут ли в памяти жить как сжатые, так и не сжатые страницы?


Т.е. каждая страница живет в двух экземплярах?
Нет, конечно, это уменьшит вместимость buffer pool почти в 2 раза
22 ноя 21, 18:00    [22399267]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
aleks222
Member

Откуда:
Сообщений: 1840
msLex
aleks222
пропущено...


"После того как страница заполнена" => ДО момента сжатия страница НЕ сжата.

Очевидный факт.
Волга впадает в Каспийское море.


Вы бы полностью прочитали, хоть


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

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


Ты не сыпь цитатами из Мао - ты на пальцах покажи "как достать что-то из архива, не распаковывая архив?"

ЗЫ. Если ты не курсе, операции сервера не ограничиваются тупым "заполнением страниц". Иногда надо что-то с них достать...
22 ноя 21, 18:02    [22399272]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
.Евгений
Member

Откуда:
Сообщений: 707
msLex
.Евгений
интересно, не могут ли в памяти жить как сжатые, так и не сжатые страницы?


Т.е. каждая страница живет в двух экземплярах?
Нет, конечно, это уменьшит вместимость buffer pool почти в 2 раза

Нет. Сжатые и несжатые, аналогично тому, как они будут жить на диске (если бы были в этот момент записаны).
aleks222
.Евгений
aleks222, msLex

интересно, не могут ли в памяти жить как сжатые, так и не сжатые страницы?

Это такой метод "экономии памяти"?

Кто у нас отвечает вопросом на вопрос?
22 ноя 21, 18:02    [22399274]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
msLex
Member

Откуда:
Сообщений: 9540
.Евгений
msLex
пропущено...


Т.е. каждая страница живет в двух экземплярах?
Нет, конечно, это уменьшит вместимость buffer pool почти в 2 раза

Нет. Сжатые и несжатые, аналогично тому, как они будут жить на диске (если бы были в этот момент записаны).


Так и есть, станицы в памяти те же, что и на диске. За исключением dirty pages, что еще не скинуты на диск.
22 ноя 21, 18:08    [22399279]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
msLex
Member

Откуда:
Сообщений: 9540
aleks222
"как достать что-то из архива, не распаковывая архив?"

Распокавать на лету, конечно.

SQL Server для сжатия данных использует lightweight алгоритмы сжатия, позволяющие применять декомпрессию на потоке.

Вы почитайте,

Там все достаточно просто.
Основное :
Row Compression - урезание неиспользуемых байт из типов фиксированной длины
Page Compression - в добавлении к Row Compression постраничные справочники.
22 ноя 21, 18:21    [22399293]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
SERG1257
Member

Откуда:
Сообщений: 2931
msLex
Распокавать на лету, конечно.
Каждый раз когда блок понадобится из буфер кэша.
Что совой об пень, что пнем об сову.
cad2206, а ты выгоду-то посчитал? Сколько гигабайт экономии получил?
22 ноя 21, 19:33    [22399327]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
Владислав Колосов
Member

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

автор
Иначе данные с нее невозможно использовать.


Там же данные не зипом пожаты, там простая табличная подстановка словарь - метасимволы. Эти данные можно прекрасно читать и преобразовывать "на лету".
22 ноя 21, 19:46    [22399338]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
Владислав Колосов
Member

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

с чего вы взяли, что cad2206 использовал сжатие страниц? Он нигде об этом не писал.
22 ноя 21, 19:48    [22399339]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
SERG1257
Member

Откуда:
Сообщений: 2931
Владислав Колосов
с чего вы взяли, что cad2206 использовал сжатие страниц? Он нигде об этом не писал.

22399026
22 ноя 21, 20:58    [22399410]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
msLex
Member

Откуда:
Сообщений: 9540
SERG1257
msLex
Распокавать на лету, конечно.
Каждый раз когда блок понадобится из буфер кэша.


Именно так

Как я уже писал выше, там очень легковесное разжатие

В случае с Row так вообще, просто превращение всех типов данных в типы с переменой длиной (varint, vardecimal и т.д.), что, фактически, эквивалентно реализации varchar в несжатых страницах.

подробности тут

В случае с Page добавляются префиксы и справочники.
подробности тут

Ни одно из этих преобразований не требует "разжимать" всю страницы целиком, при чтении конкретной записи. Все разбирается при последовательном чтении данных.
22 ноя 21, 21:18    [22399427]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
alexeyvg
Member

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

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

Только ещё нужно заметить, что балк инсёрт перестаёт работать на сжатых таблицах (то есть скорость деградирует в сотни-тысячи и более раз), и приходится простую загрузку заменять на сложную (например, лить в кучу, потом строить на ней сжатый кластерный индекс, и присоединять как секцию к сжатой секционированной таблице)
22 ноя 21, 21:41    [22399442]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
aleks222
Member

Откуда:
Сообщений: 1840
Владислав Колосов
aleks222,

автор
Иначе данные с нее невозможно использовать.


Там же данные не зипом пожаты, там простая табличная подстановка словарь - метасимволы. Эти данные можно прекрасно читать и преобразовывать "на лету".

Это "алгоритм Лемпеля-Зива" называется.
ZIP, промежду прочим, его использует. Как вариант.
23 ноя 21, 07:02    [22399499]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
SERG1257,
автор
cad2206, а ты выгоду-то посчитал? Сколько гигабайт экономии получил?

с 830ГБт после процедуры из статьи https://infostart.ru/1c/articles/692209/ размер файла БД составил 250ГБт
23 ноя 21, 09:01    [22399514]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
Провел такой эксперимент:
1. Взял самую большую таблицу (138ГБт), сжал ее:
ALTER TABLE TableName REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
и ее индексы:
ALTER INDEX IndexName ON Table REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
2. В БД размер таблицы уменьшился до 20ГБт
3. Сделал DBCC SHRINKDATABASE('''+ DataBase() + ''')
4. Размер файла БД уменьшился с 830ГБт до 675ГБт
5. При попытке выполнить дефрагментацию журнал транзакций вырос до 500ГБт, занял все свободное место и дефрагментация вылетела в ошибку
Буду признателен, если объясните, почему так произошло?
23 ноя 21, 09:13    [22399518]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
aleks222
Member

Откуда:
Сообщений: 1840
cad2206
Провел такой эксперимент:
1. Взял самую большую таблицу (138ГБт), сжал ее:
ALTER TABLE TableName REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
и ее индексы:
ALTER INDEX IndexName ON Table REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
2. В БД размер таблицы уменьшился до 20ГБт
3. Сделал DBCC SHRINKDATABASE('''+ DataBase() + ''')
4. Размер файла БД уменьшился с 830ГБт до 675ГБт
5. При попытке выполнить дефрагментацию журнал транзакций вырос до 500ГБт, занял все свободное место и дефрагментация вылетела в ошибку
Буду признателен, если объясните, почему так произошло?


Ты не нажимай на кнопки, смысла которых не понимаешь.
И будет тебе щастье.

ЗЫ. Дефрагментация = перемещение страниц = журналируемая операция.
23 ноя 21, 10:31    [22399545]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
aleks222,
ну ты серьезный конечно.

автор
Ты не нажимай на кнопки, смысла которых не понимаешь.

В основном пока нажимаю только одну "Выполнить скрипт"

автор
Дефрагментация = перемещение страниц = журналируемая операция

Это я понимаю. Мне непонятно почему тогда на боевой базе (размер диска для журнала транзакций такой же как и на тестовом сервере) ежедневная дефрагментация не увеличивает так журнал?

Журнал увеличился во время дефрагментации после действий:
1. применение к одной таблице и ее индексам DATA_COMPRESSION = PAGE
2. SHRINKDATABASE

Причем для этой таблицы дефрагментация выполнялась командой ALTER INDEX IndexName REORGANIZE
23 ноя 21, 10:55    [22399556]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9157
[deleted]

Сообщение было отредактировано: 23 ноя 21, 11:20
23 ноя 21, 11:18    [22399568]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
shrinkdatabase (без параметров) в принципе "ломает все индексы" и при их дефрагментации журнал базы данных естественно растет больше, так как дефрагментируются абсолютно все индексы. Так?
23 ноя 21, 16:39    [22399769]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
SERG1257
Member

Откуда:
Сообщений: 2931
cad2206
Мне непонятно почему тогда на боевой базе (размер диска для журнала транзакций такой же как и на тестовом сервере) ежедневная дефрагментация не увеличивает так журнал?
Два варианта
Кто-то настроил регулярный (раз в минуту, раз в 15 минут, и тд) лог бакап
Кто-то НЕ ДЕЛАЕТ ежедневную дефрагментацию (статья от АГ https://habr.com/ru/post/576882/ )

Теперь по пунктам. Итак выигрыш по месту у вас в разы - это хорошо, это заметно.
Теперь поинтересуйся у старших товарищей -
сколько стоит полтерабайта места на диске (добавь все тестовые экземпляры, DR и HA буде таковые существуют)
сколько стоит память. (у стандарта есть ограничение в 128Гб на буферный кэш)
сколько стоит время админа (бесплатно - плохой ответ)
сколько стоит лицензия на лишний камень для вашей редакции, а также SA на нее. (если вы пиратите то бесплатно)

Уверен что после подсчетов, в прешбывалые временя твои действия потянули бы на вредительство.

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

Если очень хочется принести максимум пользы (с мимимумом побочных эффектов) то советую провести анализ нагрузки (кто/что/когда делает с какими таблицами)
В этом отношении поможет Query Store (если версия 2016+), extended events или просто поговорить с пользователями чтобы настраивать процесс который болит.
Короче семь раз отмерь, один отрежь
23 ноя 21, 17:10    [22399794]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
SERG1257,
автор
Кто-то настроил регулярный (раз в минуту, раз в 15 минут, и тд) лог бакап

и на боевом и на тестовом серверах настроен бекап журнала раз в 15 минут. Но разве бекап журнала уменьшает файл журнала?

автор
Кто-то НЕ ДЕЛАЕТ ежедневную дефрагментацию

делается каждый день (вернее ночь)
23 ноя 21, 17:21    [22399805]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
msLex
Member

Откуда:
Сообщений: 9540
SERG1257
cad2206
Мне непонятно почему тогда на боевой базе (размер диска для журнала транзакций такой же как и на тестовом сервере) ежедневная дефрагментация не увеличивает так журнал?
Два варианта
Кто-то настроил регулярный (раз в минуту, раз в 15 минут, и тд) лог бакап
Кто-то НЕ ДЕЛАЕТ ежедневную дефрагментацию (статья от АГ https://habr.com/ru/post/576882/ )


Либо разные модели восстановления

На тестовом Full, на проде SIMPLE
как бы это странно не звучало.
23 ноя 21, 17:21    [22399806]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
msLex,
на обоих серверах режим восстановления "Полное"
23 ноя 21, 17:22    [22399809]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
msLex
Member

Откуда:
Сообщений: 9540
cad2206
msLex,
на обоих серверах режим восстановления "Полное"


А бекапы лога на тестовом сервер делаются?

покажите (ну или хотя бы сами посмотрите) результат на обоих серверах


select top 10 
	s.backup_start_date
	, s.is_copy_only
from msdb.dbo.backupset s 
where 
	type = 'L'
	and database_name = 'ИМЯ ВАШЕЙ БД'
order by 
	s.backup_start_date desc
23 ноя 21, 17:27    [22399812]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
msLex,
результат с тестового сервера
backup_start_date is_copy_only
2021-11-23 17:30:00.000 0
2021-11-23 17:15:01.000 0
2021-11-23 17:00:01.000 0
2021-11-23 16:45:01.000 0
2021-11-23 16:30:01.000 0
2021-11-23 16:15:00.000 0
2021-11-23 16:00:01.000 0
2021-11-23 15:45:01.000 0
2021-11-23 15:30:01.000 0
2021-11-23 15:15:00.000 0
23 ноя 21, 17:35    [22399821]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
msLex
Member

Откуда:
Сообщений: 9540
cad2206
П
3. Сделал DBCC SHRINKDATABASE('''+ DataBase() + ''')
...
5. При попытке выполнить дефрагментацию журнал транзакций вырос до 500ГБт, занял все свободное место и дефрагментация вылетела в ошибку
Буду признателен, если объясните, почему так произошло?


А так тут все просто

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


Нужно еще глянуть, что там у вас за "дефрагментацию"
Если там есть Rebuild (возможно по условию), то это вообще транзакционная операция (если он не resumable), и в логе будут храниться все изменения ВСЕХ СЕАНОСВ, произошедших с момента начала операции alter index
23 ноя 21, 17:47    [22399834]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
msLex,
скрипт дефрагментации из статьи https://info-comp.ru/obucheniest/581-rebuilding-of-indexes-in-ms-sql-server.html
23 ноя 21, 17:54    [22399836]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
SERG1257,
спасибо за советы
23 ноя 21, 17:57    [22399840]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
msLex
Member

Откуда:
Сообщений: 9540
cad2206
msLex,
скрипт дефрагментации из статьи https://info-comp.ru/obucheniest/581-rebuilding-of-indexes-in-ms-sql-server.html



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

Вы сильно фрагментировали данные и сработал этот пункт

Если степень фрагментации более 30%, лучше выполнять перестроение индекса.
23 ноя 21, 18:18    [22399859]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
msLex,
спасибо, теперь понимаю. Хочется все таки понять правильный алгоритм. Вопрос сжимать или не сжимать не стоит. Нужно сжимать. Сжимать буду все равно на тестовом сервере и отдавать базу в работу. Вопрос по секционированию таблиц и сжатию только тех, что редко используются, очень интересен. Но пока буду сжимать все и тестировать. Еще раз прошу подсказки, какую последовательность правильно выбрать:
1. Каждую ночь сжимать заранее подготовленный список таблиц и их индексов (так чтобы успеть перебрать его до начала работы с базой) методами:
ALTER TABLE TableName REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
и ее индексы:
ALTER INDEX IndexName ON Table REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)

2. Спустя несколько ночей, когда все таблицы будут сжаты, сделать SHRINKDATABASE (порядка 5 часов, одна ночь)
3. Произвести дефрагментацию индексов (после SHRINKDATABASE переиндексация заняла 5 часов, одна ночь). Тут два варианта:
- либо увеличить размер диска под журнал БД до размера самой БД (что бы он не переполнился),
- либо перевести БД в режим восстановления Simple, дефрагментировать и вернуть в режим Full.
Верно, нет?
Может дефрагментацию стоит делать до SHRINKDATABASE, а после запустить SHRINKDATABASE с параметром TRUNCATEONLY?
Прошу помощи у Вас, профессионалы, но не советов, мол не лезь туда, чего не знаешь. Не лезть туда, значит ничего не узнать. Вы сами это проходили, уверен.
24 ноя 21, 09:10    [22400044]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
msLex,
автор
Ну как и ожидалось (с минимальными изменениями это стандартный скрипт, когда не хочется разбираться детальнее какие таблицы нужно трогать)

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

use BDName
Go
declare @DbId smallint = DB_ID('BDName')

declare @IndexTmpTable table (Id INT IDENTITY(1,1) primary key,
                              SchemaName SYSNAME, 
                              TableName SYSNAME, 
                              IndexName SYSNAME, 
                              PageCount INT,
                              AvgFrag FLOAT)

declare @RowNumber INT = 1, @CntRows INT, @CntReorganize INT = 0, @CntRebuild INT = 0
declare @SchemaName SYSNAME, @TableName SYSNAME, @IndexName SYSNAME, @AvgFrag FLOAT
declare @Command VARCHAR(8000)
declare @FileName VARCHAR(100) = 'stop_defrag.txt'

declare @profile_name as nvarchar(100) = 'profile'
declare @recipients as nvarchar(500) = 'address@domen.ru'
declare @subject as NVARCHAR(100) = ''
declare @massage as NVARCHAR(1000) = ''

begin;
    
insert into @IndexTmpTable
                select Sch.name as SchemaName, 
                           Obj.name as TableName,
                           Inx.name as IndexName,
                           Page_count as  Pagecount,
                           round(AvgFrag.avg_fragmentation_in_percent,0) as Fragmentation 
                from sys.dm_db_index_physical_stats (@DbId, NULL, NULL, NULL, NULL) as AvgFrag
                LEFT JOIN sys.indexes as Inx on AvgFrag.object_id = Inx.object_id AND AvgFrag.index_id = Inx.index_id
                LEFT JOIN sys.objects as Obj on AvgFrag.object_id = Obj.object_id 
                LEFT JOIN sys.schemas as Sch on Obj.schema_id = Sch.schema_id
                where AvgFrag.index_id > 0 AND AvgFrag.avg_fragmentation_in_percent > 5 AND AvgFrag.page_count > 8
                order by Pagecount desc

select @CntRows = COUNT(*)
from @IndexTmpTable

while @RowNumber <= @CntRows
 begin

  --Получаем названия объектов, а также степень фрагментации текущего индекса
  select @SchemaName = SchemaName, @TableName = TableName, @IndexName = IndexName, @AvgFrag = AvgFrag
  from @IndexTmpTable
  where Id = @RowNumber

  --Если текущее время больше заданного, прекращаем дефрагментацию
  if @@FETCH_STATUS < 0 OR (datepart(hour, GETDATE()) >= 8 AND datepart(minute, GETDATE()) >= 0) 
   begin
    --Запись индекса на котором остановилась дефрагментация для информирования в сообщении
    set @command='echo ' + @TableName + '.' + @IndexName + ' > ' + @FileName
    exec master..xp_cmdshell @command
    
   --Отправка email
    set @subject = @profile_name + ': Стоп дефрагментации по времени'
    set @massage ='Стоп дефрагментации по времени на индексе ' + @TableName + '.' + @IndexName + CHAR(10) + CHAR(13) +
	              'Дефрагментировано ' + CAST(@RowNumber AS VARCHAR(100)) + ' из ' + CAST(@CntRows as VARCHAR(100)) + ' индексов'
    exec msdb.dbo.sp_send_dbmail
    	@profile_name = @profile_name,
    	@recipients = @recipients,
    	@body = @massage,
    	@subject = @subject;
    
    BREAK
   end;
                        
  if @AvgFrag < 30
   begin
    select @Command = 'ALTER INDEX [' + @IndexName + '] ON ' + '[' + @SchemaName + ']' 
                               + '.[' + @TableName + '] REORGANIZE';
    exec (@Command);
    set @CntReorganize = @CntReorganize + 1;
   end 
                        
  if @AvgFrag >= 30
   begin
    select @Command = 'ALTER INDEX [' + @IndexName + '] ON ' + '[' + @SchemaName + ']' 
                               + '.[' + @TableName + '] REBUILD';
    exec (@Command);
    set @CntRebuild = @CntRebuild + 1;
   end
                        
  print 'Выполнена инструкция ' + @Command;
  set @RowNumber = @RowNumber + 1
 end

end;


Дефрагментирую только те индексы, которые занимают более 8 страниц
24 ноя 21, 09:45    [22400055]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
baracs
Member

Откуда: Москва
Сообщений: 7219
cad2206
я привел в пример статью...

А эту статью: Отказ от ежедневной дефрагментации читали?
24 ноя 21, 10:56    [22400085]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
aleks222
Member

Откуда:
Сообщений: 1840
cad2206
msLex,
спасибо, теперь понимаю. Хочется все таки понять правильный алгоритм. Вопрос сжимать или не сжимать не стоит. Нужно сжимать. Сжимать буду все равно на тестовом сервере и отдавать базу в работу. Вопрос по секционированию таблиц и сжатию только тех, что редко используются, очень интересен. Но пока буду сжимать все и тестировать. Еще раз прошу подсказки, какую последовательность правильно выбрать:
1. Каждую ночь сжимать заранее подготовленный список таблиц и их индексов (так чтобы успеть перебрать его до начала работы с базой) методами:
ALTER TABLE TableName REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
и ее индексы:
ALTER INDEX IndexName ON Table REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)

2. Спустя несколько ночей, когда все таблицы будут сжаты, сделать SHRINKDATABASE (порядка 5 часов, одна ночь)
3. Произвести дефрагментацию индексов (после SHRINKDATABASE переиндексация заняла 5 часов, одна ночь). Тут два варианта:
- либо увеличить размер диска под журнал БД до размера самой БД (что бы он не переполнился),
- либо перевести БД в режим восстановления Simple, дефрагментировать и вернуть в режим Full.
Верно, нет?
Может дефрагментацию стоит делать до SHRINKDATABASE, а после запустить SHRINKDATABASE с параметром TRUNCATEONLY?
Прошу помощи у Вас, профессионалы, но не советов, мол не лезь туда, чего не знаешь. Не лезть туда, значит ничего не узнать. Вы сами это проходили, уверен.


Образцово-показательная каша в голове.

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

2. Дефрагментация делается "объективным по-показаниям", а не ради "дефрагментируем фсе на фсякий случай".

3. Научитесь уже статистику обновлять.

4. Вот так они создают имитацию бурной деятельности "Произвести дефрагментацию индексов (после SHRINKDATABASE переиндексация заняла 5 часов, одна ночь)". Шринкаем-дефрагментируем-Шринкаем-дефрагментируем.
А ваще-то
ALTER TABLE TableName REBUILD PARTITION
полностью "дефрагментирует" индекс.
24 ноя 21, 11:48    [22400112]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
aleks222,
автор
Образцово-показательная каша в голове

совершенно верно, пока..

1. SHRINKDATABASE необходимо будет произвести после
автор
великой чистки базы
и сжатия.
2.
автор
Дефрагментация делается "объективным по-показаниям"
- ну намекните на эти показатели
3.
автор
Научитесь уже статистику обновлять
- разбираюсь
4.
автор
Вот так они создают имитацию бурной деятельности
- так подскажите как правильно сделать
24 ноя 21, 12:08    [22400121]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
baracs,
Читал, интересно. Но нужно экспериментировать.
24 ноя 21, 12:11    [22400123]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
SERG1257
Member

Откуда:
Сообщений: 2931
cad2206
Вопрос сжимать или не сжимать не стоит. Нужно сжимать. Сжимать буду все равно
Безумству храбрых поем мы песню
cad2206
на тестовом сервере и отдавать базу в работу
Не понял насчет тестового сервера.
cad2206
какую последовательность правильно выбрать
Сжимаете таблицу (торопится не надо).
Не делаете shrink.
Не делаете дефрагментацию. Я у себя отменил этот еженедельный джоб и никто не заметил разницы.

Если обнаружите что стало хуже, разжимаете таблицу.
ALTER TABLE TableName REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = none)


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

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

автор
Не понял насчет тестового сервера.

не корректно выразился. На новом сервере, где установлен MS SQL Server 2019. База переедет со старого (на MS SQL Server 2012).

автор
Сжимаете таблицу (торопится не надо).

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

Откуда:
Сообщений: 2931
Для больших таблиц прогоните sp_estimate_data_compression_savings
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-estimate-data-compression-savings-transact-sql?view=sql-server-ver15
Посмотрите на разные типы сжатия. Может ROW будет ненамного хуже (и точно дешевле)
У меня было пару случаев когда это имело смысл (большая таблицы с пустыми полями типа int и большая таблица с типом char(200))

Маленькие таблицы даже не рассматривайте - визгу много шерсти мало
24 ноя 21, 17:14    [22400302]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
SERG1257,
при размере файла БД 830 ГБт, какие таблицы считать большими для исследования их функцией sp_estimate_data_compression_savings?
25 ноя 21, 09:16    [22400499]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
cad2206
Member

Откуда:
Сообщений: 28
SERG1257,
автор
Сжимаете таблицу (торопится не надо).
Не делаете shrink.
Не делаете дефрагментацию. Я у себя отменил этот еженедельный джоб и никто не заметил разницы.

Я правильно понял, сжимать по несколько таблиц за ночь без shrink'а (это я уже понял, что shrink крайняя мера) и без дефрагментации. И смотреть на работу пользователей?
25 ноя 21, 09:19    [22400500]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
selis76
Member

Откуда:
Сообщений: 406
У меня нагруженная база 1С на 3 терабайта, по опыту пространство больше расходуется на фрагментацию поскольку кластерный (системный) код 1С много удаляет и вставляет заново особенно в итогах по регистрам.
Перестроение кластерных индексов и обычных индексов помогает гораздо эффективнее. Освобожденное место можно использовать для новых данных. Ну а вообще в решении должна быть заложена процедура обрезания старых данных и свертки остатков. База не должна расти бесконечно
25 ноя 21, 12:40    [22400636]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
SERG1257
Member

Откуда:
Сообщений: 2931
cad2206
какие таблицы считать большими для исследования их функцией sp_estimate_data_compression_savings?
Исследовать можно хоть все (это просто посмотреть, это бесплатно)
Результаты (для PAGE и для ROW) в эксельку, сортируя по разнице между sample_size_with_current_compression_setting и sample_size_with_requested_compression_setting
Уверен, что кандидатов будет не больше десятка. А дальше нужно будет принимать решение кто достоин, а кто нет.

selis76, я человек простой, а вопрос сложный. Можешь пояснить свою мысль языком ЖЭКа. (или как говорят буржуи ELI5)
Правильно ли я понял, что ты хочешь сказать что для твоей базы перестроение индексов действительно высвобождает место.
Однако следом ты утверждаешь что плотно упакованный индекс, будет снова переразбит следующей операцией (особенно в итогах по регистрам.)
Так может и не трогать эти итоги по регистрам. Ну будут некоторые страницы наполовину пусты. Стоит ли овчинка выделки? И как это относится к сабжу топика (сжатие aka компрессия данных).
По поводу удаления старых данных это тоже оффтопик. Люди годами не могут почистить балкон (гараж, подвал), а тут данные удалить. А если понадобятся?
25 ноя 21, 17:19    [22400837]     Ответить | Цитировать Сообщить модератору
 Re: Поэтапное сжатие БД MS SQL Server  [new]
selis76
Member

Откуда:
Сообщений: 406
to SERG1257,
1C это генератор таблиц и запросов на основе финансовых метаданных . Поэтому большинство информации имеет период и итоги тоже периодичны (по умолчанию месяц) . Даже в регистрах накопления агрегаты тоже порождают много операций удаления и вставки
А это прямой путь к фрагментации. Конечно ребилд оптимизирует данные в прошлых периодах, но в новых периодах все начинается сначала. Играться с наполнением экстентов можно уже после того как все хорошо с фрагментацией.
Пример как 1С генерит SQL код можно увидеть тут https://infostart.ru/1c/articles/184361/
Структура таблиц и индексов 1С официально выложена https://its.1c.ru/db/metod8dev/content/1798/hdoc https://its.1c.ru/db/metod8dev/content/1590/hdoc
25 ноя 21, 20:08    [22400920]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2 3 4      [все]
Все форумы / Microsoft SQL Server Ответить