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