Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Компрессия и Shrink Database и Files  [new]
Testor1
Member

Откуда:
Сообщений: 679
Всем привет !

Есть вопрос - как правильно делать шринк базы данных и файлов, чтобы не убить дисковую подсистему?

Включение компрессии на таблице высвобождает много места в в базе данных. Требуется вернуть высвободившееся место файловой системе. Есть комманды Shrink Database И Shrink File. Они работает корректно и хорошо, если бы не одно НО. Если база данных большая > 100ГБ, а высвобождаемое место составляет более 60%, то при запуске любых из процедур - происходит активное обращению к диску и система запущенная на этом же сервере начинает тормозить и причем существенно.

Понятно, что такие работы нужно проводить в часы наименьшей нагрузки, но все же хотелось бы узнать
каким способом быстро, качественно и без "перегрузки" дисковой подсистемы высвобождать место в базе?
Использовать Maintanace Tools или писать свои скрипты на базе dbcc shrink играя с target_size?
28 окт 13, 19:58    [15043050]     Ответить | Цитировать Сообщить модератору
 Re: Компрессия и Shrink Database и Files  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
Правильно _не_ делать шринк базы данных и файлов.
28 окт 13, 20:04    [15043063]     Ответить | Цитировать Сообщить модератору
 Re: Компрессия и Shrink Database и Files  [new]
Testor1
Member

Откуда:
Сообщений: 679
Гавриленко Сергей Алексеевич
Правильно _не_ делать шринк базы данных и файлов.


Я планирую выполнять данную операцию, перед тем как базу переведут в режим READONLY.
База хранит историю транзакций за определенный период времени. Она не будет больше обновляться.
По моим тестам, размер таблиц после включения PAGE compression уменьшился в 2.5 раза.
Данные из базы будут использовать только для выборок. Вот и возникла идея оптимизации
28 окт 13, 20:10    [15043078]     Ответить | Цитировать Сообщить модератору
 Re: Компрессия и Shrink Database и Files  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
Тогда перемещайте вашу базу на другие физические носители, чтобы операции над ней не мешали основной деятельности. Или проводите эти работы в сервисные окна. Или вообще ничего не трогайте, просто переведите в r/o.

Сообщение было отредактировано: 28 окт 13, 20:26
28 окт 13, 20:25    [15043122]     Ответить | Цитировать Сообщить модератору
 Re: Компрессия и Shrink Database и Files  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
Есть еще вариант аккуратной, медленной, долгой и ненапряжной для дисковой подсистемы переливкой в новую базу, но это уже не просто "нажать пару кнопок в интерфейсе".
28 окт 13, 20:27    [15043125]     Ответить | Цитировать Сообщить модератору
 Re: Компрессия и Shrink Database и Files  [new]
Testor1
Member

Откуда:
Сообщений: 679
Гавриленко Сергей Алексеевич
Есть еще вариант аккуратной, медленной, долгой и ненапряжной для дисковой подсистемы переливкой в новую базу, но это уже не просто "нажать пару кнопок в интерфейсе".


Цена вопроса в несколько терабайт на дисковом массиве :) Жаба давит так хранить данные.

Думал на счет переливания, НО переливание может так же убить диск. Плюс нужно выделять доп пространство.

Есть другая идея. Может быть безумная, но ...

Сделать цикл от 95 до 0 с шагом 5 для параметром target_size.

Я заметил, что если делать шринк на небольшую величину, то дисковая система не так сильно грузиться (хотя бывают и исключения). Плюс можно играть параметрами TRUNCATEONLY и NOTRUNCATE. Так намного медленнее, но спокойней можно сжать всю базу.

Кстати перед началом работ перевожу базу в режим SIMPLE.
28 окт 13, 20:43    [15043170]     Ответить | Цитировать Сообщить модератору
 Re: Компрессия и Shrink Database и Files  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
Вы хотите и рыбку съесть и ... чтобы ничего не тормозило.

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

  • делать работы в то время, когда нет нагрузки на основные базы, например, во время регламентных простоев;
  • изолировать нагрузку на другой, независимый носитель, т.е добавить дисков;
  • не использовать shrink, заменить ее на переливку данных - тут вы можете рулить нагрузкой как угодно, хоть "переливать 3 записи в час"; если не хватает места, то опять добавить дисков.

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

    Как-то так.
  • 28 окт 13, 22:42    [15043469]     Ответить | Цитировать Сообщить модератору
     Re: Компрессия и Shrink Database и Files  [new]
    Testor1
    Member

    Откуда:
    Сообщений: 679
    По последнему пункту вопрос.

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

    Дожна ли в теории увеличиться скорость выборок из этой таблицы после включения page compression, shrink database, update statistics? Интересует ответ - как должно быть. Практику проверю сегодня.
    29 окт 13, 07:53    [15044167]     Ответить | Цитировать Сообщить модератору
     Re: Компрессия и Shrink Database и Files  [new]
    imato
    Member

    Откуда: Москва
    Сообщений: 80
    Testor1
    По последнему пункту вопрос.

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

    Дожна ли в теории увеличиться скорость выборок из этой таблицы после включения page compression, shrink database, update statistics? Интересует ответ - как должно быть. Практику проверю сегодня.


    На больших объемах compression и shrink database для истории разумеется поможет. От update statistics при одном и том же распределении статистики смысла нет.
    Уменьшать fill_factor можно было бы по таблично, но если таблица одна, то алгоритм такой:
    1. сделать такую же новую, партиционированную с другим к-м заполнения
    2. переименовать старую
    3. сделать вью из новой и старой со старым названием (чтобы система работала)
    4. переносить данные из старой в новую по партициям (это простой цикл), на практике решить когда и сколько партиций в день можно переносить.
    29 окт 13, 09:45    [15044607]     Ответить | Цитировать Сообщить модератору
     Re: Компрессия и Shrink Database и Files  [new]
    Testor1
    Member

    Откуда:
    Сообщений: 679
    imato
    Testor1
    По последнему пункту вопрос.

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

    Дожна ли в теории увеличиться скорость выборок из этой таблицы после включения page compression, shrink database, update statistics? Интересует ответ - как должно быть. Практику проверю сегодня.


    На больших объемах compression и shrink database для истории разумеется поможет. От update statistics при одном и том же распределении статистики смысла нет.
    Уменьшать fill_factor можно было бы по таблично, но если таблица одна, то алгоритм такой:
    1. сделать такую же новую, партиционированную с другим к-м заполнения
    2. переименовать старую
    3. сделать вью из новой и старой со старым названием (чтобы система работала)
    4. переносить данные из старой в новую по партициям (это простой цикл), на практике решить когда и сколько партиций в день можно переносить.


    Скорость выборки увеличиться на компрессированных данных? Часто запросы делают по LIKE '%some text%'.


    Я скорее всего пойду по пути шринк с изменением процента.
    29 окт 13, 16:19    [15047604]     Ответить | Цитировать Сообщить модератору
     Re: Компрессия и Shrink Database и Files  [new]
    Testor1
    Member

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

    Еще вопрос

    Имеет ли значение последовательность выполнения процедур
    shrindatabase и shrinkfile ?
    29 окт 13, 17:19    [15048059]     Ответить | Цитировать Сообщить модератору
     Re: Компрессия и Shrink Database и Files  [new]
    Testor1
    Member

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


    Что то у меня подозрение, что при запуске Shrink Database он пытается в параллельном режиме высвободить во всех файлах. Вот из-за этого и начинаются тормоза. Хотя может быть я и не прав.

    http://www.sqlservercentral.com/Forums/Topic937013-146-1.aspx

    http://www.sql-server-performance.com/forum/threads/dbcc-shrinkdatabase-vs-dbcc-shrinkfile.9087/
    29 окт 13, 17:36    [15048154]     Ответить | Цитировать Сообщить модератору
     Re: Компрессия и Shrink Database и Files  [new]
    Testor1
    Member

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

    Шринк по файлово работает быстрее, менее грузит диск и лучше высвобождает место, чем шринк датабейс.
    Это на тот случай если стоит задача максимально быстро и полно вычистить базу.

    USE [tools]
    GO
    /****** Object:  StoredProcedure [dbo].[usp_sys_maintanance_02_ShrinkFiles]    Script Date: 10/31/2013 14:05:38 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[usp_sys_maintanance_02_ShrinkFiles] 
     @DatabaseName NVARCHAR(100) = NULL,
     @FreeSpace int = 0
    AS 
    BEGIN 
    
    	SET NOCOUNT OFF;
    
    	DECLARE @filesize AS INT = 1; -- MB
    	DECLARE @filename VARCHAR(256);
    	DECLARE @cmd VARCHAR(MAX);
    	DECLARE curdb CURSOR LOCAL FOR 
    	SELECT f.name AS filename, d.name AS databasename
    	FROM msdb.sys.master_files f INNER JOIN master.sys.sysdatabases d ON d.dbid = f.database_id
    	WHERE d.name NOT IN ('master', 'tempdb', 'model', 'msdb')
    	  AND (@DatabaseName IS NULL OR d.[name] = @DatabaseName)
    --	  AND state = 0
    	ORDER BY f.[file_id];
    
    	BEGIN TRY 
    		  
    		OPEN curdb
    		FETCH NEXT FROM curdb INTO @filename, @databasename
    		WHILE @@FETCH_STATUS = 0
    		BEGIN
    			--PRINT @filename
    			SET @cmd = 'USE [' + @databasename + ']; DBCC SHRINKFILE ([' + @filename + '], ' + CAST(@filesize AS VARCHAR) + ');';
    			PRINT @cmd;
    			EXEC (@cmd)
    			FETCH NEXT FROM curdb INTO @filename, @databasename;
    		END
    		DEALLOCATE curdb
    
    
    	END TRY 
    	BEGIN CATCH 
    		
    		EXEC dbo.usp_sys_RethrowError;
    
    	END CATCH 
    
    
    
    END 
    
    31 окт 13, 12:07    [15057636]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить