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

Откуда:
Сообщений: 202
Коллеги, приветствую!

Имеется большая, но плохо спроектированная БД.
В ней имеется около десятка таблиц - куч, без кластерного индекса и первичного ключа.
В том числе имеется несколько таблиц, в которых идет довольно интенсивная вставка и удаление данных.
У Дмитрия Пилюгина я прочитал, что с удалением из кучи - не всё так просто, и по хорошему его нужно делать как delete from table1 with(tablock), в противном случае страницы, на которых располагаются удаленные данные оказываются по прежнему распределенными за соответствующей таблицей, и повторно - не используются (если я правильно всё понял, если нет - поправьте меня).

В связи с этим вопросы:
1. А как оценить такого рода "потерю места" в файле БД?
2. Что нужно сделать, чтобы выправить ситуацию? Достаточно ли будет сделать Alter table table1 rebuild?
3. Большая часть таблиц такого рода представляют из себя таблицы вида GUID + одно или несколько BLOB полей (nvarchar(max) и varbinary(max)). Страницы, занимаемые BLOBами тоже будут "теряться"?
13 сен 17, 16:53    [20793361]     Ответить | Цитировать Сообщить модератору
 Re: Как дефрагментировать кучи в базе данных?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
iii2,

создать кластерный индекс, и удилить, если куча нужна и дальше
13 сен 17, 16:55    [20793369]     Ответить | Цитировать Сообщить модератору
 Re: Как дефрагментировать кучи в базе данных?  [new]
iii2
Member

Откуда:
Сообщений: 202
TaPaK
iii2,

создать кластерный индекс, и удилить, если куча нужна и дальше

Пол Рэндал говорит, что это плохо: https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation/
Но я, честно говоря, не понял, почему.
Он, впрочем, и против Alter table rebuild возражает.
13 сен 17, 17:14    [20793422]     Ответить | Цитировать Сообщить модератору
 Re: Как дефрагментировать кучи в базе данных?  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
iii2
TaPaK
iii2,

создать кластерный индекс, и удилить, если куча нужна и дальше

Пол Рэндал говорит, что это плохо: https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation/
Но я, честно говоря, не понял, почему.
Он, впрочем, и против Alter table rebuild возражает.

Так потому что при этом два раза перестраиваются все некластерные индексы.
а при rebuild - один раз перестраиваются.
Его позиция - "сделайте кластерный индекс и оставьте его там".
13 сен 17, 17:21    [20793454]     Ответить | Цитировать Сообщить модератору
 Re: Как дефрагментировать кучи в базе данных?  [new]
msLex
Member

Откуда:
Сообщений: 7724
iii2
Но я, честно говоря, не понял, почему.
Он, впрочем, и против Alter table rebuild возражает.


Потому что и создании кластерного и удаление кластерного индекса и rebuild кучи приводят к перестроению всех не кластерных.


Если у вас нет не кластерных индексов, или вы согласны дополнительную нагрузку на систему, то почему-бы и нет?
13 сен 17, 17:22    [20793458]     Ответить | Цитировать Сообщить модератору
 Re: Как дефрагментировать кучи в базе данных?  [new]
o-o
Guest
iii2
1. А как оценить такого рода "потерю места" в файле БД?

ну посмотрите used_pages против total_pages в sys.allocation_units.
used это реально используемые, а total это то,
что allocated -- за кучей числится и другим не достанется, ибо в собственности кучи
iii2
2. Что нужно сделать, чтобы выправить ситуацию? Достаточно ли будет сделать Alter table table1 rebuild?

да
iii2
3. Большая часть таблиц такого рода представляют из себя таблицы вида GUID + одно или несколько BLOB полей (nvarchar(max) и varbinary(max)). Страницы, занимаемые BLOBами тоже будут "теряться"?

да

можно все в том же sys.allocation_units наблюдать, сгруппировав по type_desc
13 сен 17, 17:37    [20793520]     Ответить | Цитировать Сообщить модератору
 Re: Как дефрагментировать кучи в базе данных?  [new]
iii2
Member

Откуда:
Сообщений: 202
o-o, спасибо, сейчас будем посмотреть
Minamoto, msLex, спасибо за разъяснения.
13 сен 17, 17:52    [20793580]     Ответить | Цитировать Сообщить модератору
 Re: Как дефрагментировать кучи в базе данных?  [new]
o-o
Guest
у меня тут наврано:
o-o
iii2
1. А как оценить такого рода "потерю места" в файле БД?

ну посмотрите used_pages против total_pages в sys.allocation_units.
used это реально используемые, а total это то,
что allocated -- за кучей числится и другим не достанется, ибо в собственности кучи


никак не увидеть.
select   type_desc, 
         sum(total_pages) as tot,
         sum(used_pages) as used,
		 sum(data_pages) as data
from   sys.partitions p
       join sys.allocation_units au
           on au.container_id = p.partition_id
where p.object_id = object_id('dbo.heap')
group by type_desc

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

остается разве что вот такое посмотреть:
select alloc_unit_type_desc, 
       avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.heap'), 0, null, 'detailed')

и обнаружить там почти 0
15 сен 17, 10:43    [20798007]     Ответить | Цитировать Сообщить модератору
 Re: Как дефрагментировать кучи в базе данных?  [new]
дампер
Member [заблокирован]

Откуда:
Сообщений: 55
TaPaK
iii2,

создать кластерный индекс, и удилить, если куча нужна и дальше


+1 я так делаю.
15 сен 17, 11:03    [20798082]     Ответить | Цитировать Сообщить модератору
 Re: Как дефрагментировать кучи в базе данных?  [new]
wsrmnj
Guest
o-o,

https://www.brentozar.com/archive/2015/07/finding-tables-with-nonclustered-primary-keys-and-no-clustered-index/

INNER JOIN [sys].[allocation_units] [a]
ON [a].[container_id] = CASE WHEN [a].[type] IN ( 1, 3 ) THEN [p].[hobt_id] WHEN [a].[type] = 2 THEN [p].[partition_id] END
15 сен 17, 11:08    [20798106]     Ответить | Цитировать Сообщить модератору
 Re: Как дефрагментировать кучи в базе данных?  [new]
o-o
Guest
wsrmnj
o-o,

https://www.brentozar.com/archive/2015/07/finding-tables-with-nonclustered-primary-keys-and-no-clustered-index/

INNER JOIN [sys].[allocation_units] [a]
ON [a].[container_id] = CASE WHEN [a].[type] IN ( 1, 3 ) THEN [p].[hobt_id] WHEN [a].[type] = 2 THEN [p].[partition_id] END

и зачем мне искать таблицы без кластерного индекса
или с некластерными ПК?
у вас что, есть сомнения, что я у правильной таблицы смотрю allocation_units?
15 сен 17, 11:32    [20798221]     Ответить | Цитировать Сообщить модератору
 Re: Как дефрагментировать кучи в базе данных?  [new]
o-o
Guest
дампер
TaPaK
iii2,

создать кластерный индекс, и удилить, если куча нужна и дальше


+1 я так делаю.

все послушали Козлова и поняли, что так делать не надо.
уже только потому, что он Козлов
(но советовать-то страсть как хочется, да, Козлов?)
ну и еще потому, что выше уже писали:
таким образом вы перестроите свои некластерные индексы дважды.
так что вменяемые товарищи просто ребилдят.
---
Козлов, пойди-ка лучше разберись со своими сыпящимися базами.
дампы еще не весь диск сожрали?
или как раз весь, посему сидишь, форматируешь все по старой доброй традиции,
а от нечего делать советы раздаешь?
вот же до чего неуемные козлы пошли...
15 сен 17, 11:37    [20798247]     Ответить | Цитировать Сообщить модератору
 Re: Как дефрагментировать кучи в базе данных?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
o-o,

ну там больше упор на "если нужна и дальше". Я ими вообще не страдаю и искореняю :)
15 сен 17, 11:41    [20798262]     Ответить | Цитировать Сообщить модератору
 Re: Как дефрагментировать кучи в базе данных?  [new]
wsrmnj
Guest
o-o,

https://technet.microsoft.com/en-us/library/ms189792(v=sql.110).aspx

container_id bigint

ID of the storage container associated with the allocation unit.
If type = 1 or 3, container_id = sys.partitions.hobt_id.
If type is 2, then container_id = sys.partitions.partition_id.
0 = Allocation unit marked for deferred drop
15 сен 17, 11:42    [20798267]     Ответить | Цитировать Сообщить модератору
 Re: Как дефрагментировать кучи в базе данных?  [new]
o-o
Guest
wsrmnj
o-o,

https://technet.microsoft.com/en-us/library/ms189792(v=sql.110).aspx

container_id bigint

ID of the storage container associated with the allocation unit.
If type = 1 or 3, container_id = sys.partitions.hobt_id.
If type is 2, then container_id = sys.partitions.partition_id.
0 = Allocation unit marked for deferred drop

от меня что надо?
доказать, что мой код правильно выцепляет подопытную кучу?
вообще-то обычно наоборот поступают: если код неверный,
приводят репро в доказательство.
давайте ваше репро
15 сен 17, 11:46    [20798281]     Ответить | Цитировать Сообщить модератору
 Re: Как дефрагментировать кучи в базе данных?  [new]
o-o
Guest


К сообщению приложен файл. Размер - 30Kb
15 сен 17, 12:06    [20798378]     Ответить | Цитировать Сообщить модератору
 Re: Как дефрагментировать кучи в базе данных?  [new]
wsrmnj
Guest
o-o,

извиняюсь,
на текущий момент ни на что не влияет

https://blogs.msdn.microsoft.com/chadboyd/2007/02/24/what-is-a-hobt_id-in-sql-2005/
https://www.sqlservercentral.com/Forums/Topic1425936-391-1.aspx

the relationship between a partition and a hobt is ALWAYS 1-to-1

может что появится в vNext

https://github.com/NikoNeugebauer/CISL/blob/master/SQL vNext/alignment.sql
15 сен 17, 15:18    [20799171]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить