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

уже есть.
и 2 страницы ушли
а объяснения нет, поэтому пока что молчу.
найду, выложу.
28 янв 16, 17:17    [18741512]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранения файлов  [new]
o-o
Guest
привожу Рэндалов ответ по этому поводу.
ну и кто подписан, в пн сможет почитать обсуждение
Randal
Hey <o-o> - yes, shrink will remove empty pages from a heap.
Deletes often don't result in empty page deletion from heaps -
see curious-case-empty-heap-table
(for some reason the script is messed up on the page, but it's the explanation you need).
And I'll talk about it in next Monday's Insider email too - thanks for the topic!
9 фев 16, 10:58    [18791601]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранения файлов  [new]
o-o
Guest
из вчерашней рассылки, полностью:
Randal
This time it’s another topic drawn from several email questions I received.
They boiled down to this (paraphrasing):
I’ve got a large heap table where the space isn’t being given up when I delete a large number of records,
but then when I shrink the database the heap is reduced in size. Can you explain?

The behavior you’re seeing is how SQL Server works, but it’s pretty non-intuitive.
When a page in an index becomes empty, it’s always deallocated
(as an empty page isn’t permitted in a SQL Server index structure from SQL Server 2005 onwards).
However, the structure of a heap is different and as a result, the behavior is too.

Whenever a row is deleted in a heap, it’s most likely that the page does not become empty.
However, if the page that the row is stored on becomes empty as a result of the delete,
the page cannot be deallocated from the table unless an exclusive table lock is held
(to remove the page from “tracking”).
This is usually not the case, unless lock escalation has occurred because you’re deleting enough rows to trigger escalation,
or if you specifically use the TABLOCK hint on the delete statement, for instance.
But, because all of these things are unlikely, the page usually cannot be deallocated.

There is a Knowledge Base article that describes this phenomenon: KB 913399.
However, the KB article only references up to and including SQL Server 2005
but this behavior exists in more recent releases too and is very easy to reproduce if you want to prove it to yourself.

The empty pages will be reused by subsequent inserts,
but if the space isn’t going to be reused following a large delete in a heap,
you might consider using the TABLOCK hint to allow the empty pages to be deallocated
and the space made available for other objects in the database to use.

Another alternative is to just use a clustered index instead, or if a heap is necessary,
you could rebuild the heap using ALTER TABLE … REBUILD
(that was added in SQL Server 2008 to support enabling compression on a heap),
with the caveat that this will cause all the table’s nonclustered indexes to be rebuilt.

On the extreme end (in my opinion), you could reclaim the empty heap space using a shrink operation.
Shrink won’t free up space inside pages as it moves them
(with the exception of compacting LOB pages as it goes –
somewhat unsuccessfully depending on which version and build you’re on – see KB 2967240),
but it will remove empty pages rather than moving them.
This will effectively shrink the heap after a large delete,
but with the usual caveats about shrink causing index fragmentation and generally being an expensive, slow operation to perform.

Call to action: Not really a call to action,
but one more thing to be aware of around how SQL Server works
and understanding the behavior you’re seeing when using heaps instead of clustered indexes.
16 фев 16, 15:43    [18824246]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 2 [3]      все
Все форумы / Microsoft SQL Server Ответить