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

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

Есть проблема : не обновляется свободное место после удаления строк в MSSQL 2008 .
1. Имеется БД около 240 ГБ с 5 таблицами большими таблицами (1-4 по 10...15 Гб ; 5-я - 190 Гб).
2. При удалении ненужных записей из большой таблицы 5 по окончании скрипта размер таблицы, число записей , размер индексов для этой таблицы тоже уменьшаются , но общее свободное место для все БД так и не увеличилось.
3. Если такую операцию (удаление старых записей) проводить для таблиц 1-4 - то в итоге общее свободное место во всей БД увеличивается.

Операция удаления для всех таблиц в среднем занимает 30-60 минут и завершается без ошибок (удаляется около 60 000 000 записей и освобождается в таблице до 6 Гб). Пробовал делать с более меньшим объемом удаляемых строк - результат для 5-ой таблицы такой же.

В итоге - при заполнении таблиц новыми записями таблицы 5 значение "общее свободное место в БД" продолжает уменьшаться и в некоторый момент , при достижении минимального значения происходит автоматическое увеличение размера БД . Но такой вариант мне не нужен. Нужно чтобы после удаления записей из табл 5 происходило увеличение общего свободного места в БД.

Проблема решается , если изменить имя 5-ой таблицы , создать идентичную пустую , скопировать из старой все нужные записи в новую ,а потом удалить старую. В этом случае общее свободное место в БД обновляется корректно , но этот процесс занимает много времени.

Также пробовал запускать проверку таблицы (в рабочем режиме) - скрипт выполняется долго. Ждал 3 дня - потом снял задачу.
26 ноя 14, 11:07    [16906587]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
aleks2
Guest 		А чего у тя в 5-й таблице лежит?
26 ноя 14, 11:14    [16906635]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
Glory
Member

Откуда:
Сообщений: 104751
 		У вас какой-то вопрос по описаной ситуации ?
Удаление записи из середины таблицы не может освободить занятое таблицей место.
Это называется фрагментацией
Способы борьбы с фрагментацией описаны в хелпе
26 ноя 14, 11:14    [16906636]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
rusrussia
Member

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

там логи парсированные лежат :)
26 ноя 14, 11:16    [16906650]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
rusrussia
Member

Откуда:
Сообщений: 29
Glory
У вас какой-то вопрос по описаной ситуации ?
Удаление записи из середины таблицы не может освободить занятое таблицей место.
Это называется фрагментацией
Способы борьбы с фрагментацией описаны в хелпе


После удаления из 5-ой таблицы ее размер , размер индексов, число строк уменьшается на 50-60 млн записей и 5-6 гб , но свободное место во все БД не увеличивается ! ИЗ-за этого при уменьшении свободного места в БД до минимума происходит расширение файла всей БД - мне это не надо. Если такую операцию удаления производить для других таблиц - там все корректно !
26 ноя 14, 11:19    [16906675]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
aleks2
Guest
rusrussia
Glory
У вас какой-то вопрос по описаной ситуации ?
Удаление записи из середины таблицы не может освободить занятое таблицей место.
Это называется фрагментацией
Способы борьбы с фрагментацией описаны в хелпе


После удаления из 5-ой таблицы ее размер , размер индексов, число строк уменьшается на 50-60 млн записей и 5-6 гб , но свободное место во все БД не увеличивается ! ИЗ-за этого при уменьшении свободного места в БД до минимума происходит расширение файла всей БД - мне это не надо. Если такую операцию удаления производить для других таблиц - там все корректно !


Тибеж намекнули: Обеспечь удаление строго из начала или конца кластерного индекса и будет тебе щастье...
26 ноя 14, 11:21    [16906695]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
rusrussia
Member

Откуда:
Сообщений: 29
rusrussia
Glory
У вас какой-то вопрос по описаной ситуации ?
Удаление записи из середины таблицы не может освободить занятое таблицей место.
Это называется фрагментацией
Способы борьбы с фрагментацией описаны в хелпе


После удаления из 5-ой таблицы ее размер , размер индексов, число строк уменьшается на 50-60 млн записей и 5-6 гб , но свободное место во все БД не увеличивается ! ИЗ-за этого при уменьшении свободного места в БД до минимума происходит расширение файла всей БД - мне это не надо. Если такую операцию удаления производить для других таблиц - там все корректно !


Последую вашему совету - попробую дефрагментировать . Но чувствую это будет долго и придется доступ к бд останавливать
26 ноя 14, 11:22    [16906703]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31822
rusrussia
Есть проблема : не обновляется свободное место после удаления строк в MSSQL 2008 .
Освобождение места зависит от структуры таблицы (включая тип хранения), типов данных столбцов, и от того, что именно удаляется, поэтому на такой общий вопрос ответить без конкретики нельзя. Единственное гарантированное срендство - переливка, как вы и пробовали делать.

По другому - нужно так проектировать базу и объекты, что бы место освобождалось. И даже для быстрого и полного удаления и освобождения места используют специальные средства типа секционирования.
26 ноя 14, 11:22    [16906704]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31822
rusrussia
Последую вашему совету - попробую дефрагментировать . Но чувствую это будет долго и придется доступ к бд останавливать
Дефрагментация не требует ничего останавливать, но действительно может очень долго выполняться, и может не привести к желаемому результату.
26 ноя 14, 11:24    [16906716]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8488
 		ALTER TABLE REBUILD, но солить надо по вкусу.
26 ноя 14, 13:14    [16907518]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
rusrussia
Member

Откуда:
Сообщений: 29
Владислав Колосов
ALTER TABLE REBUILD, но солить надо по вкусу.


Много ли свободного места на диске для журнала для такой операции требуется ? сейчас свободно 120 Гб , а таблица для перестройки 190 гб
26 ноя 14, 16:05    [16909071]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8488
 		rusrussia,
не измерял, не могу точно сказать. По моему предположению, суть операции в пометке страниц как "свободных", т.е. дефрагментация не производится.
26 ноя 14, 16:28    [16909245]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
o-o
Guest 		фигасебе ребилд "помечает".
он создаст как минимум вторую копию, прежде чем грохнет старую таблицу(индекс),
т.е. на диске с данными примерно то же место запасайте, ну и в лог все пойдет, а как же.
но если у вас FULL, можете на время ребилда в BULK LOGGED переключить, в лог меньше запишется
26 ноя 14, 16:46    [16909350]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
 		Кластерный индекс то есть на таблице?

rusrussia
более меньшим
...
при заполнении таблиц новыми записями таблицы 5
rusrussia, а вы вообще русский?
27 ноя 14, 00:35    [16911350]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8488
 		o-o, понятно,меня прежде всего удивило, с какой скоростью он перестраивает таблицу, гораздо быстрее, чем если бы я копировал.
27 ноя 14, 00:51    [16911397]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
o-o
Guest 		Владислав Колосов,

могу предположить, что речь идет о модели SIMPLE и переливаете INSERT INTO без TABLOCK.
такое копирование будет FULLY LOGGED, в то время как REBUILD пройдет как MINIMALLY LOGGED OPERATION.
вот замеры того, что попадает в лог, при копировании/ребилде кучи в 7000 строк и примерно столько же вставляемого
-- такая куча
create table dbo.t(id int identity, col char(1000));
insert into dbo.t(col) values ('a');
go 7000
-- так замеряю. в SIMPLE -- checkpoint между операциями, в FULL -- backup log 
select sum([Log Record Length])/1024 as [size, kb]
from sys.fn_dblog(null,null)


recovery model operation size(Kb)
simple rebuild 132
bulk logged rebuild 145
full rebuild 7261
simple insert into (no tablock) 8116
simple insert into with (tablock) 113
simple select into 389
full select into 7477
full insert into 8125

выходит, в SIMPLE insert into без указания TABLOCK -- это самый гадкий вариант,
если посчитать операции, к-ые идут в лог,
то на 1-ом месте 7000 штук LOP_INSERT_ROWS
select Operation, COUNT(*) as cnt
from sys.fn_dblog(null,null)
group by Operation

если же ребилдим, то чемпионы по кол-ву
LOP_SET_FREE_SPACE 1000
LOP_SET_BITS 571
ну т.е. он в основном размечат PFS и ML map,
т.е.как и обещано для minimally logged operations, в лог идут только размещения страниц/экстентов.

ну а для FULL в лог при REBUILD прямо копии страниц ложатся:
LOP_FORMAT_PAGE 1004 -- это и есть записываемые страницы
LOP_SET_FREE_SPACE 1001

так что бесполезно ожидать, что лог не вырастет хотя бы до конечного размера перестроенного.
(ну и переключение в BULK LOGGED при ребилде спасет от роста лога)
28 ноя 14, 11:25    [16917948]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
rusrussia
Member

Откуда:
Сообщений: 29
Mind
Кластерный индекс то есть на таблице?

rusrussia
более меньшим
...
при заполнении таблиц новыми записями таблицы 5
rusrussia, а вы вообще русский?



В проблемной таблице 4 индекса , 1 из них кластерный(формируется по 3 полям), остальные по 1-2 полям.
В свойствах :
размер , занимаемый индексом : 49 360,602 MB ;
число строк : 431492495
Занимаемое пространство : 188 593,758 MB .


1 и 3 параметр после удаления уменьшается корректно , а в свойствах БД значение не меняется в меньшую сторону.

Проблема есть , решение тоже есть - через переливку данных . Но пришло время навести порядок в этом бардаке, доставшемся по наследству
28 ноя 14, 22:10    [16922261]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
rusrussia
Member

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

могу предположить, что речь идет о модели SIMPLE и переливаете INSERT INTO без TABLOCK.
такое копирование будет FULLY LOGGED, в то время как REBUILD пройдет как MINIMALLY LOGGED OPERATION.
вот замеры того, что попадает в лог, при копировании/ребилде кучи в 7000 строк и примерно столько же вставляемого
-- такая куча
create table dbo.t(id int identity, col char(1000));
insert into dbo.t(col) values ('a');
go 7000
-- так замеряю. в SIMPLE -- checkpoint между операциями, в FULL -- backup log 
select sum([Log Record Length])/1024 as [size, kb]
from sys.fn_dblog(null,null)


recovery model operation size(Kb)
simple rebuild 132
bulk logged rebuild 145
full rebuild 7261
simple insert into (no tablock) 8116
simple insert into with (tablock) 113
simple select into 389
full select into 7477
full insert into 8125

выходит, в SIMPLE insert into без указания TABLOCK -- это самый гадкий вариант,
если посчитать операции, к-ые идут в лог,
то на 1-ом месте 7000 штук LOP_INSERT_ROWS
select Operation, COUNT(*) as cnt
from sys.fn_dblog(null,null)
group by Operation

если же ребилдим, то чемпионы по кол-ву
LOP_SET_FREE_SPACE 1000
LOP_SET_BITS 571
ну т.е. он в основном размечат PFS и ML map,
т.е.как и обещано для minimally logged operations, в лог идут только размещения страниц/экстентов.

ну а для FULL в лог при REBUILD прямо копии страниц ложатся:
LOP_FORMAT_PAGE 1004 -- это и есть записываемые страницы
LOP_SET_FREE_SPACE 1001

так что бесполезно ожидать, что лог не вырастет хотя бы до конечного размера перестроенного.
(ну и переключение в BULK LOGGED при ребилде спасет от роста лога)


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

А как , на ваш взгляд, выглядит вариант - попробовать секционирование таблицы , а потом в каждой секции сделать дефрагментацию !

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

В свойствах :
размер , занимаемый индексом : 49 360,602 MB ;
число строк : 431492495
Занимаемое пространство : 188 593,758 MB .

DBCC SHOWCONTIG просматривает таблицу "TTT"..
Таблица: "TTT" (767654128); идентификатор индекса: 1, идентификатор базы данных: 7
Выполнен просмотр TABLE уровня.
- Просмотрено страниц................: 23947848
- Просмотрено экстентов................: 3187236
- Переключений экстентов................: 21215331
- Среднее число страниц на экстент............: 7.5
- Плотность просмотра [лучший счетчик:фактический счетчик]....: 14.11% [2993481:21215332]
- Логическое разбиение просмотра.........: 93.14%
- Разбиение просмотра по экстентам.........: 40.61%
- В среднем байт на страницу............: 1830.6
- Средняя плотность страницы (полная)...........: 77.38%
Выполнение DBCC завершено. Если DBCC выдает сообщения об ошибках, обратитесь к системному администратору.
28 ноя 14, 22:22    [16922316]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
rusrussia
Member

Откуда:
Сообщений: 29
aleks2
rusrussia
пропущено...


После удаления из 5-ой таблицы ее размер , размер индексов, число строк уменьшается на 50-60 млн записей и 5-6 гб , но свободное место во все БД не увеличивается ! ИЗ-за этого при уменьшении свободного места в БД до минимума происходит расширение файла всей БД - мне это не надо. Если такую операцию удаления производить для других таблиц - там все корректно !


Тибеж намекнули: Обеспечь удаление строго из начала или конца кластерного индекса и будет тебе щастье...


Не намекнете , как узнать , какие строки находятся в начале или конце кластерного индекса ?
При переливе хронология заполнения записей нарушается , т.к. перелив идет одновременно с рабочим процессом.
28 ноя 14, 22:28    [16922351]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
Glory
Member

Откуда:
Сообщений: 104751
rusrussia
Не намекнете , как узнать , какие строки находятся в начале или конце кластерного индекса ?

Наверное в начала находятся записи с наименьшими значениями ключа, а в конце - с наибольшими
https://www.sql.ru/articles/mssql/03013101indexes.shtml
Заодно почитайте про физическую организацию данных http://technet.microsoft.com/en-us/library/ms179276(v=sql.90).aspx
А заодно про то, как определяют и борются с фрагментацией http://msdn.microsoft.com/en-us/library/ms189858.aspx
28 ноя 14, 22:34    [16922381]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
rusrussia
Member

Откуда:
Сообщений: 29
rusrussia
Владислав Колосов
ALTER TABLE REBUILD, но солить надо по вкусу.


Много ли свободного места на диске для журнала для такой операции требуется ? сейчас свободно 120 Гб , а таблица для перестройки 190 гб


Из опытов - могу сказать следующее - была древняя копия БД с размером примерно 60 ГБ. Пробовал на ней делать REBUILD для всех индексов (в качестве опыта - для проверки его влияние на ускорение запросов SELECT к этой таблице; проблемы с удалением данных , как я понял тогда еще не возникала) - так она на тестом компе шла около 40 часов ! Особого прироста производительности в итоге не было. При этом разработчик MSSQL рекомендует делать такую операцию 1 раз день :) Для моего случая - я сразу понял не вариант.
28 ноя 14, 22:38    [16922402]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
Glory
Member

Откуда:
Сообщений: 104751
rusrussia
Для моего случая - я сразу понял не вариант.

Как в том анекдоте
Подумаешь Карузо! Ничего особенного. Слышал я его, мне сосед напел.

Для того, чтобы делать выводы о последствиях той или иной команды, нужно хотя бы иметь представление, что было до выполнения команды и что стало после выполнения команды
28 ноя 14, 22:43    [16922415]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
rusrussia
DBCC SHOWCONTIG просматривает таблицу "TTT"..
Таблица: "TTT" (767654128); идентификатор индекса: 1, идентификатор базы данных: 7
Выполнен просмотр TABLE уровня.
DBCC SHOWCONTIG - вымирающий динозавр. Проверьте на всякий случай ghost_record_count из sys.dm_db_index_physical_stats, а то был такой баг.

Если же окажется, что проблема исключительно в том что данные удаляются из середины, то как уже было сказано - меняйте кластерный индекс.
28 ноя 14, 23:05    [16922563]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
rusrussia
Member

Откуда:
Сообщений: 29
Mind
rusrussia
DBCC SHOWCONTIG просматривает таблицу "TTT"..
Таблица: "TTT" (767654128); идентификатор индекса: 1, идентификатор базы данных: 7
Выполнен просмотр TABLE уровня.
DBCC SHOWCONTIG - вымирающий динозавр. Проверьте на всякий случай ghost_record_count из sys.dm_db_index_physical_stats, а то был такой баг.

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


ghost_record_count
0
0
0
0
0
0
0
0
0
78
0
0
0
0
0
0
0
0

Полная информация во вложении

К сообщению приложен файл (ttt.xls - 18Kb) cкачать
29 ноя 14, 06:24    [16923274]     Ответить | Цитировать Сообщить модератору
 Re: не обновляется свободное место после удаления строк в MSSQL 2008  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8488
rusrussia
Пробовал на ней делать REBUILD для всех индексов

Вы путаете перестроение индексов и перестроение таблиц.
1 дек 14, 13:19    [16930959]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl→      все
Все форумы / Microsoft SQL Server Ответить