0. YPermitin 2615 17.04.19 08:09 Сейчас в теме

Самый быстрый шринк на Диком Западе

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

Перейти к публикации

Комментарии
Избранное Подписка Сортировка: Древо
1. Hekeus 17.04.19 09:33 Сейчас в теме
Поставил плюс. Вот только мой шринк быстрее и эффектнее: drop database!
succub1_5; YPermitin; +2 Ответить
2. YPermitin 2615 17.04.19 09:34 Сейчас в теме
3. asved.ru 36 17.04.19 09:50 Сейчас в теме
(1) Для внедрений, хранящих БД на механике, существуют устройства, реализующие аппаратный шринк. Вот например.
4. bykrash 17.04.19 09:55 Сейчас в теме
По ссылке описание и скрипт поэтапного сжатия файла бд https://www.sqlshack.com/shrinking-your-database-using-dbcc-shrinkfile/. Немного доработал, так как попытки сжатия идут даже после отсутствия свободного пространства.
5. bykrash 17.04.19 10:00 Сейчас в теме
Код и описание скрипта пошагового сжатия файла БД https://www.sqlshack.com/shrinking-your-database-using-dbcc-shrinkfile/. Немного доработал, так как попытки сжатия не прекращаются при отсутствии свободного места.
USE [DBname]
DECLARE @FileName sysname = N'LogicalFileName';
DECLARE @TargetSize INT = (SEL ECT 1 + size*8./1024 FR OM sys.database_files WHERE name = @FileName);
DECLARE @Factor FLOAT = .99;
DECLARE @ActualSize INT = 0;
 
WHILE @TargetSize > @ActualSize
BEGIN
    SET @TargetSize *= @Factor;
    DBCC SHRINKFILE(@FileName, @TargetSize);
	SET @ActualSize = (SELECT 1 + size*8./1024 FR OM sys.database_files  Wh ere name = @FileName) * 0.99; 
    DECLARE @msg VARCHAR(200) = CONCAT('Shrink file completed. Target Size: ', 
         @TargetSize, ' MB. Actual size: ', @ActualSize, 'MB. Timestamp: ', CURRENT_TIMESTAMP);
    RAISERROR(@msg, 1, 1) WITH NOWAIT;
     WAITFOR DELAY '00:00:01';
END;
Показать
artichoke; YPermitin; +2 Ответить
6. YPermitin 2615 17.04.19 10:02 Сейчас в теме
(5) шринкуете на постоянной основе? :)
7. bykrash 17.04.19 10:06 Сейчас в теме
(6) Да, сжатие как этап при переносе с прода на тест.
YPermitin; +1 Ответить
13. user-z99999 18 17.04.19 10:28 Сейчас в теме
(7)
Сжатие - это архивирование т.е. базе будет нужно каждый раз, при обращении к данных, распаковывать данные.
Будет тратится время ...

Либо на тесте - использовать полную копию рабочей базы (не нужно сжимать),
либо - обрезать таблицы за предыдущие года, оставлять последний год (месяц).
15. bykrash 17.04.19 10:45 Сейчас в теме
(13) В данном случае архивирование - это то, что нужно, т.к. процессорных мощностей в избытке, а вот дисковое пространство нет (тестовая среда работает на ssd). Полная копия рабочей базы (одной из) занимает ~600 гб и пока от предыдущих данных избавиться нельзя.
8. triviumfan 10 17.04.19 10:08 Сейчас в теме
SQL 2014
В плане обслуживания присутствует "Сжатие БД".
Никакого "Значительного снижения эффективности работы индексов" не замечено.
Проверим статистику индексов самых популярных таблиц.
Прикрепленные файлы:
maksa2005; YPermitin; +2 Ответить
9. YPermitin 2615 17.04.19 10:10 Сейчас в теме
(8) утверждаете, что все что в стать "от лукавого"?
10. triviumfan 10 17.04.19 10:13 Сейчас в теме
(9) У меня нет глубоких знаний и опыта администрирования БД. Просто факт. Но похоже, что "Сатана тут точно замешан".
YPermitin; +1 Ответить
11. YPermitin 2615 17.04.19 10:17 Сейчас в теме
(10) тут на самом деле два варианта:
1. вам повезло и при шринке данные индексов не были перемещены.
2. Вы делали шринк без перемещения данных, который только освобождает место в конце файла. При этом пустое место внутри файла не затрагивается.

Подробнее посмотрите статью Brent Ozar по ссылке в публикации. Она хоть и на английском, но все хорошо показано о влиянии на индексы.
14. triviumfan 10 17.04.19 10:43 Сейчас в теме
(11)
Brent Ozar

Натыкался я на него однажды, ютуб канал ведёт. Непутёвый он.
(12) После. В понедельник выполнялся, 2 дня прошло. Этот план выполняется только по понедельникам.
Прикрепленные файлы:
maksa2005; YPermitin; +2 Ответить
16. YPermitin 2615 17.04.19 10:57 Сейчас в теме
36. Дмитрий74Чел 125 18.04.19 17:48 Сейчас в теме
(11) да у него база 51Мб, плюс в базе оставляет 10% места. И статистику проверил лишь по 3м объектам.
Я читал товарища Брантозавра. И тест выполнил на рабочей базе размером более 200Гб. После этого пришел к админам и смог на цифрах подтвердить - почему я и раньше был против регулярного шринка базы.
12. bykrash 17.04.19 10:26 Сейчас в теме
(10) Статистика собрана после выполнения плана в котором отработало сжатие? Если план ежедневный, на следующий день после сжатия у вас произойдет обсуживание индексов и статитика по идексам будет в норме. Кстати, возможно вы забыли в плане обновление статистики и очистку кэша?
18. nicxxx 227 17.04.19 12:35 Сейчас в теме
(8) Сколько строк в таблицах?
22. triviumfan 10 17.04.19 15:37 Сейчас в теме
(18) достаточно: документов 100к и цен 10кк. Базе более 10 лет.
17. ids79 2088 17.04.19 11:50 Сейчас в теме
Спасибо, хорошая статья.
YPermitin; +1 Ответить
19. YPermitin 2615 17.04.19 14:12 Сейчас в теме
20. nvv1970 17.04.19 14:19 Сейчас в теме
Прочитал по диагонали в надежде уловить, какую задачу вы решаете?
Чем база со свободным местом "хуже", чем без него?

Предполагая, что база наполняется данными, а не пребывает в RO состоянии, что реиндексация способна генерировать большое расширение пространства базы, делаю вывод, что шринк файла данных не нужен чаще, чем "никогда".
А превентивное выделение пространства? А fillfactor? А 24/7 и отсутствие "техокон"? А.......

В чем тогда смысл шринка? Давайте холиварить )))
Лишние (свободные) 50 Гб заполнятся гораздо быстрее, чем DBA потратит время на борьбу за экономию места

PS: а статья да... хорошая ))

PPS_UPD: все нашел строки про "смысл" )))) согласен
YPermitin; +1 Ответить
21. YPermitin 2615 17.04.19 14:22 Сейчас в теме
(20) холивар можно быстро закончить.

Вот удалили Вы из базы файлы размером 300 ГБ, и чтобы место на серверном SSD освободить под некоторую другую задачу. - нужно сделать шринк за минимальное время.

Никогда не говори никогда! =D
23. 3vs 17.04.19 16:57 Сейчас в теме
"Главное помнить - шринку нет места в продакшене! Хватит шринковать! :)"
Да, вот товарищ тоже с этим согласен:
"Почему вы не должны сжимать ваши файлы данных"
habr.com/ru/post/330492/

По сути шринк, получается, та же дефрагментация диска.
Лишний напряг железа - износ механики и нагрева электроники жёстких дисков, что снижает
надёжность.
Один штатовский товарищ считает по тому же поводу, что любой RAID кроме RAID0 - зло
в плане долгожительства жёстких дисков.

Кстати, сегодня была новость, что импортные медики выявили закономерность внезапной смерти человека от его пульса, человек, у которого пульс в покое 55 ударов в минуту, даже если пьёт и курит и т.п. имеет шансов дожить до глубокой старости больше, чем тот, у кого в покое пульс 75 ударов в минуту.
Сердечко, видимо, тоже имеет свой ресурс по количеству сокращений...

Юрий, а нет более радикального решения избавления от фрагментации - иметь, к примеру,
два идентичных дисковых массива, один рабочий а другой пустой, на который периодически
сливаются данные с первого массива, но так, чтобы при записи на пустой массив происходила уже дефрагментация, после чего в работу запускается бывший пустым массив с дефрагментированными данными, а первый, рабочий массив, очищается и ждёт своей очереди по включению в работу?
Да, затратно, но в плане долговечности дисков, возможно выигрыш.
YPermitin; +1 Ответить
24. TODD22 17 17.04.19 17:03 Сейчас в теме
(23)
Да, затратно, но в плане долговечности дисков, возможно выигрыш.

Диски в серверах уже давно расходный материал.
YPermitin; +1 Ответить
25. 3vs 17.04.19 17:04 Сейчас в теме
(24)Это у кого как, к сожалению...
YPermitin; +1 Ответить
26. TODD22 17 17.04.19 17:09 Сейчас в теме
(25)
Это у кого как, к сожалению...

У тех у кого объёмы данных диски до дыр затирают, остальным смысла нет беспокоится.

У меня серверные диски выходили из строя на сервере на котором 4 буха иногда считали ЗП и вели бух учёт примерно два раза в неделю. И база была что то под 500Мб. Однако пара серверных дисков за пару лет вышли из строя.
YPermitin; +1 Ответить
27. 3vs 17.04.19 17:26 Сейчас в теме
"Однако пара серверных дисков за пару лет вышли из строя."
От брака никто на застрахован!
Мы вот тоже наскребли немного денег на два серверных SSD,
так вот один был в плёнке в упаковке, другой без плёнки.
Который был в плёнке, работает третий год.
Который был без плёнки, был неисправен, когда даёшь нагрузку, он вообще пропадал
из системы и биоса, выключаешь сервер, отключаешь питание, снова появляется...
Заменили, правда прошло пара месяцев и на другой тип, так как замены уже не было.
Хотя в фирме, где диски покупали, сказали, что тестировали диск несколько часов, ничего не отваливалось.
По моему настоянию диск, таки отправили на завод, откуда сообщили, что да, брак...
YPermitin; +1 Ответить
28. YPermitin 2615 17.04.19 20:01 Сейчас в теме
(23)
Юрий, а нет более радикального решения избавления от фрагментации - иметь, к примеру,
два идентичных дисковых массива, один рабочий а другой пустой, на который периодически
сливаются данные с первого массива, но так, чтобы при записи на пустой массив происходила уже дефрагментация, после чего в работу запускается бывший пустым массив с дефрагментированными данными, а первый, рабочий массив, очищается и ждёт своей очереди по включению в работу?
Да, затратно, но в плане долговечности дисков, возможно выигрыш.


На мой взгляд это сильно зависит от назначения этой базы, интенсивности изменения в ней данных, особенностей планов обслуживания.

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

Но возможно и есть какое-то решение по Вашей схеме, надо думать и экспериментировать :)
29. 3vs 17.04.19 20:12 Сейчас в теме
"Но возможно и есть какое-то решение по Вашей схеме, надо думать и экспериментировать :)"
Надо экспериментировать! :-)
Я в Вас верю, всё получится! :-)

Давайте беречь жёсткие и твердотельные диски! :-)
В принципе, есть и дополнительный стимул - пока данные на грязном
дисковом массиве не грохнули, он вам будет копией базы, разумеется без
новых данных, но всё таки...
YPermitin; +1 Ответить
30. YPermitin 2615 17.04.19 20:16 Сейчас в теме
(29)
массиве не грохнули, он вам будет копией базы, разумеется без
новых данных, но всё таки...


Ну вот, весь энтузиазм убили :D
31. 3vs 17.04.19 20:20 Сейчас в теме
(30)Не, это побочный эффект! :-)

Можно, даже, для экономии электроэнергии пустой массив отключать!
Хотя, некоторые говорят, что лучше не выключать - установившийся режим лучшее для жёстких дисков, а старт/стоп могут приводить к неисправностям.
Был давно у нас такой случай, сервер работа себе и работал, решили пропылесосить, всё, жёсткий диск больше не включился...
32. genayo 18.04.19 06:22 Сейчас в теме
Что плохого в том, чтобы делать шринк например раз в месяц в технологическое окно? SSD не резиновые так-то :))
33. nicxxx 227 18.04.19 07:32 Сейчас в теме
(32) Написано же, фрагментация 99.9%. Если с highload-ом не сталкивались, то это не очень страшно. А вот когда к диску пойдет 10000 запросов в секунду, а строк в таблицах будет по миллиарду.....
34. genayo 18.04.19 08:59 Сейчас в теме
(33) Ну то есть это не про 1С :))
35. nicxxx 227 18.04.19 09:01 Сейчас в теме
(34) Про 1С, но таких компаний немного. Спроси у брокеров, если есть знакомые, например, БКС или Открытие, как они свои базы 1С обслуживают.
37. YPermitin 2615 18.04.19 20:54 Сейчас в теме
(35) есть еще на просторах родины Highload в 1С =)
38. DonAlPatino 53 23.04.19 16:03 Сейчас в теме
А можно вопросик почти в тему? Что shrink (обрезание) файла для фрагментации индексов очень плохо - это понятно. А как повлияет backup transaction log при full модели восстановления на фрагментацию? Ведь по логике тоже самое происходит... Может кто-нибудь ткнуть носом в хорошую статью по этой теме.
YPermitin; +1 Ответить
39. YPermitin 2615 23.04.19 20:16 Сейчас в теме
(38) нет, шринк лога транзакций не влияет на фрагментацию в файле данных, также не практически не влияет на фрагментацию в самом файле журнала. Но есть нюансы.

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

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a538cb11-634d-4f5b-91d6-b2257d903783/shrinking-the-log-file-and-fragmentation?forum=sqltools

Но шринк лога транзакций не должен быть регулярной операцией. Зачем? Если это полная модель восстановления, то достаточно делать бэкап лога, тогда данные в нем будут перезаписываться, если так можно выразиться.
40. DonAlPatino 53 23.04.19 20:40 Сейчас в теме
(39) Дык я и не говорил про шринк лога транзакций. Я спрашивал про backup transaction log и его влияние на фрагментацию индексов.
Скажем в лоб - делаем rebuld index, а потом backup transaction log. Соответственно все данные из transaction log переносятся в файл с данными. И что происходит с индексами в этот момент?
YPermitin; +1 Ответить
41. YPermitin 2615 23.04.19 23:18 Сейчас в теме
(40) понял.

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

На производительность это может влиять и есть рекомендации по этому поводу.

Тут дальше можно отдельную статью написать :)
Пару лет назад смотрел вот это: https://www.youtube.com/watch?v=WnLupnOoPXw

Там где-то в видео было подробное описание про влияние бэкапирования на лог транзакций и как с этим бороться. Рекомендую посмотреть.
42. DonAlPatino 53 24.04.19 11:36 Сейчас в теме
(41) Провел тест с утра... Выгрузил базу в бэкап, отребилдил индексы скриптом с https://gallery.technet.microsoft.com/scriptcenter/Script-for-rebuilding-all-8d079754, посмотрел степень фрагментации, сделал backup transaction log, посмотрел степень фрагментации ... один в один до backup. В общем похоже backup transaction log на фрагментацию индексов никак не влияет.
Вот что интересно.. в процессе обнаружил, что ребилд скриптом работает значительно лучше чем rebuild index task из Maintance Plan... ибо на обработанной rebuild index task базе снизил фрагментацию раза в два.
43. YPermitin 2615 24.04.19 12:51 Сейчас в теме
(42) мы говорим на разных языках похоже.

Я говорю про фрагменьацию в файле логов.

В файле данных на фрагментацию не влияет, это я еще в прошлом комментарии написал.

Посмотрите видео, там ответы на все ваши вопросы.
44. СергейК 51 24.04.19 23:01 Сейчас в теме
Спасибо за статью.
Натолкнуло на интересную идею:
Для создания тестовой базы минимально возможного размера использовать метод копирования из рабочей в тестовую базу, предварительно в тестовой создав таблицы и применив к ним сжатие, а только потом заливка данными.
Идеально если применительно к своей конфигурации можно было бы исключать некоторые таблицы от копирования, а некоторые ограничить фильтром, например по дате.

Если у кого есть (полу)готовые наработки, поделитесь плз.

p.s. В виду того что тестовая база на другом SQL сервере и соединение между ними 1ГБит, требует проверки скорость такого копирования для базы 400ГБайт...
Оставьте свое сообщение
Новые вопросы с вознаграждением
Автор темы объявил вознаграждение за найденный ответ, его получит тот, кто первый поможет автору.

Вакансии

Консультант ERP-систем
Москва
Временный (на проект)

Студент (стажер) 1С
Нижний Новгород
зарплата от 25 000 руб.
Полный день

Бизнес-аналитик 1С
Москва
зарплата от 90 000 руб. до 150 000 руб.
Полный день

Программист 1С
Санкт-Петербург
зарплата от 120 000 руб.
Полный день

Программист 1С
Нижний Новгород
зарплата до 100 000 руб.
Полный день