1

Topic: How to defragment heaps in a database?

Colleagues, I welcome!
There is big, but badly designed DB.
In it is available about ten tables - heaps, without clustered index and primary key.
Including there are some tables to which there is intensive enough insertion and removal of the data.
At Dmitry Piljugina I read that with removal from a heap - not all so is simple, and on good it is necessary to do it as delete from table1 with (tablock), otherwise pages on which remote data are allocated appear on former arranged for the appropriate table, and repeatedly - are not used (if I correctly understood all, if was not present - correct me).
In this connection questions:
1. And how to estimate such "place loss" in a DB file?
2. What it is necessary to make to correct a situation? Whether will enough make Alter table table1 rebuild?
3. The most part of tables such are tables of type GUID + one or the several BLOB fields (nvarchar (max) and varbinary (max)). The pages occupied BLOB too will "be lost"?

2

Re: How to defragment heaps in a database?

iii2;
To create clustered index, and  if the heap is necessary and further

3

Re: How to defragment heaps in a database?

TaPaK wrote:

iii2;
To create clustered index, and  if the heap is necessary and further

The floor of Rendal says that it is bad: https://www.sqlskills.com/blogs/paul/a- … mentation/
But I, to tell the truth, did not understand, why.
He, however, and against Alter table rebuild objects.

4

Re: How to defragment heaps in a database?

iii2 wrote:

it is passed...
The floor of Rendal says that it is bad: https://www.sqlskills.com/blogs/paul/a- … mentation/
But I, to tell the truth, did not understand, why.
He, however, and against Alter table rebuild objects.

So because thus two times are rebuilt all .
And at rebuild - are once rebuilt.
Its position - "make clustered index and leave it there".

5

Re: How to defragment heaps in a database?

iii2 wrote:

But I, to tell the truth, did not understand, why.
He, however, and against Alter table rebuild objects.

Because both creation cluster and removal of clustered index and rebuild heaps lead to evolution of all not the cluster.
If you do not have not clustered indexes, or you agree additional loading system why is not present?

6

Re: How to defragment heaps in a database?

iii2 wrote:

1. And how to estimate such "place loss" in a DB file?

Well look used_pages against total_pages in sys.allocation_units.
used it is really used, and total it that,
That allocated - behind a heap is registered also on another does not get, for in the heap property

iii2 wrote:

2. What it is necessary to make to correct a situation? Whether will enough make Alter table table1 rebuild?

Yes

iii2 wrote:

3. The Most part of tables such are tables of type GUID + one or the several BLOB fields (nvarchar (max) and varbinary (max)). The pages occupied BLOB too will "be lost"?

Yes
All is possible in the same sys.allocation_units to watch, grouping on type_desc

7

Re: How to defragment heaps in a database?

o-o, thanks, we will look now
Minamoto, msLex, thanks for clarification.

8

Re: How to defragment heaps in a database?

At me here it is told lies:

o-o wrote:

it is passed...
Well look used_pages against total_pages in sys.allocation_units.
used it is really used, and total it that,
That allocated - behind a heap is registered also on another does not get, for in the heap property

Not to see in any way.

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

Will continue to show for almost empty heap all the same,
That was for full.
sp_spaceused There.
Remains unless here such to look:

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 ')

And to find out there almost 0

9

Re: How to defragment heaps in a database?

o-o;
https://www.brentozar.com/archive/2015/ … red-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

10

Re: How to defragment heaps in a database?

wsrmnj wrote:

o-o;
https://www.brentozar.com/archive/2015/ … red-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

And what for to me to search for tables without clustered index
Or with not cluster PCs?
You that, have doubts, what I at the correct table look allocation_units?

11

Re: How to defragment heaps in a database?

wrote:

it is passed...
+1 I so do.

All listened to Kozlova and understood that it is not necessary so to do.
Already only because it of Goats
(But to advise passion as it would be desirable, yes, Goats?)
Well and still because above already wrote:
Thus you rebuild the  twice.
So made companions simply .
---
Goats, go understand with the  bases is better.
all disk gobbled up?
Or just everything, therefore you sit, you format all by old kind tradition;
And from there is nothing to do councils you distribute?
Here to what irrepressible goats went...

12

Re: How to defragment heaps in a database?

o-o;
Well emphasis on "there there is more if it is necessary and further". I them do not suffer at all and I eradicate smile

13

Re: How to defragment heaps in a database?

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

14

Re: How to defragment heaps in a database?

wsrmnj wrote:

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

From me that is necessary?
To prove, what my code correctly  an experimental heap?
Generally normally on the contrary arrive: if the code incorrect;
Result  as proof.
Give yours

15

Re: How to defragment heaps in a database?

16

Re: How to defragment heaps in a database?

o-o;
I apologize;
By the current moment on what does not influence
https://blogs.msdn.microsoft.com/chadbo … -sql-2005/
https://www.sqlservercentral.com/Forums … 391-1.aspx
the relationship between a partition and a hobt is ALWAYS 1-to-1
Can that appears in vNext
https://github.com/NikoNeugebauer/CISL/blob/master/SQL vNext/alignment.sql