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

Откуда:
Сообщений: 4996
Такая проблема: У меня в базе сильно фрагментированы несколько таблиц
применение dbcc indexdefrag и DBREINDEX результата не дало удаление кластерного индекса и повторное его создание тоже ничего не изменило
Как было:
- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]
- Extent Scan Fragmentation ...................: 50.00%
так все и осталось (измнение fillfactor-a c 80 до 100 ничего не изменило)
Можно ли еще как-нибудь справиться с указанной выше проблемой ?
29 апр 04, 11:02    [656404]     Ответить | Цитировать Сообщить модератору
 Re: DBCC DBREINDEX  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37101
Я думаю на таких маленьких индексах(Scan Density [Best Count:Actual Count].......: 50.00% [1:2] ) это все не имеет смысла.
29 апр 04, 11:10    [656432]     Ответить | Цитировать Сообщить модератору
 Re: DBCC DBREINDEX  [new]
vitaliy14
Member [заблокирован]

Откуда:
Сообщений: 4996
В смысле лучше ничего не делать?

Эти таблицы в основном используются на чтение, insert/update относительно редкие
29 апр 04, 11:27    [656501]     Ответить | Цитировать Сообщить модератору
 Re: DBCC DBREINDEX  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37101
автор
Эти таблицы в основном используются на чтение, insert/update относительно редкие

Тогда fillfactor в 100%.
А два экстента по 64 Кб - для сиквела это так мало ...
автор
В смысле лучше ничего не делать?

А Вы чего хотите в итоге получить?
29 апр 04, 11:30    [656516]     Ответить | Цитировать Сообщить модератору
 Re: DBCC DBREINDEX  [new]
vitaliy14
Member [заблокирован]

Откуда:
Сообщений: 4996
автор
А Вы чего хотите в итоге получить?

По идее вот это:
- Scan Density [Best Count:Actual Count].......: 100.00%
- Extent Scan Fragmentation ...................: 0%
Или я что-то не доганяю?
29 апр 04, 11:33    [656525]     Ответить | Цитировать Сообщить модератору
 Re: DBCC DBREINDEX  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37101
Тогда бы уж весь output привели.
И все-таки - при маленьких объемах это, imho, не имеет абсолютно никакого смысла, но если охота правды, давайте смотреть.
Inside SQL Server гласит:
1)
Removing Fragmentation
Several methods are available for removing fragmentation from an index. First, you can rebuild the index and have SQL Server allocate all new contiguous pages for you. You can do this by using a simple DROP INDEX and CREATE INDEX, but I've already discussed some reasons why this is not optimal. In particular, if the index supports a constraint, you can't use the DROP INDEX command. Alternatively, you can use DBCC DBREINDEX, which can rebuild all the indexes on a table in a single operation, or you can use the drop_existing clause along with CREATE INDEX.

The drawback of these methods is that the table is unavailable while the index is being rebuilt. If you are rebuilding only nonclustered indexes, there is a shared lock on the table, which means that no modifications can be made, but other processes can SELECT from the table. Of course, they cannot take advantage of the index you're rebuilding, so the query might not perform as well as it should. If you're rebuilding the clustered index, SQL Server takes an exclusive lock, and no access is allowed at all.

SQL Server 2000 allows you to defragment an index without completely rebuilding it. In this release, DBCC INDEXDEFRAG reorders the leaf level pages into physical order as well as logical order, but using only the pages that are already allocated to the leaf level. It basically does an in-place ordering, similar to a sorting technique called bubble-sort. This can reduce the logical fragmentation to 0 to 2 percent, which means that an ordered scan through the leaf level will be much faster. In addition, DBCC INDEXDEFRAG compacts the pages of an index, based on the original fillfactor, which is stored in the sysindexes table. This doesn't mean that the pages always end up with the original fillfactor, but SQL Server uses that as a goal. The process does try to leave at least enough space for one average-size row after the defragmentation takes place. In addition, if a lock cannot be obtained on a page during the compaction phase of DBCC INDEXDEFRAG, SQL Server skips the page and doesn't go back to it. Any empty pages created as a result of this compaction are removed. Here's an example:

DBCC INDEXDEFRAG(0, 'charge', 1)

Here's the output:

Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
673 668 12

The algorithm SQL Server uses for DBCC INDEXDEFRAG finds the next physical page in a file belonging to the leaf level and the next logical page in the leaf level with which to swap it. It finds the next physical page by scanning the IAM pages for that index. The single-page allocations are not included in this release. Pages on different files are handled separately. The algorithm finds the next logical page by scanning the leaf level of the index. After each page move, all locks and latches are dropped and the key of the last page moved is saved. The next iteration of the algorithm uses the key to find the next logical page. This allows other users to update the table and index while DBCC INDEXDEFRAG is running.

For example, suppose the leaf level of an index consists of these pages, in this order:

47 22 83 32 12 90 64

The first step is to find the first physical page, which is 12, and the first logical page, which is 47. These pages are then swapped, using a temporary buffer as a holding area. After the first swap, the leaf level looks like this:

12 22 83 32 47 90 64

The next physical page is 22, which is the same as the next logical page, so no work is done. The next physical page is 32, which is swapped with the next logical page, 83, to look like this:

12 22 32 83 47 90 64

After the next swap of 47 with 83, the leaf level looks like this:

12 22 32 47 83 90 64

The 64 is swapped with 83:

12 22 32 47 64 90 83

And finally, 83 and 90 are swapped:

12 22 32 47 64 83 90

Keep in mind that DBCC INDEXDEFRAG uses only pages that are already part of the index leaf level. No new pages are allocated. In addition, the defragmenting can take quite a while on a large table. You get a report every five minutes on the estimated percentage completed.

2)
SQL Server also keeps track of all the extents that have been accessed, and then it determines how many gaps are in the used extents.
An extent is identified by the page number of its first page. So, if extents 8, 16, 24, 32, and 40 make up an index, there are no gaps.
If the extents are 8, 16, 24, and 40, there is one gap.
The value in DBCC SHOWCONTIG's output called Extent Scan Fragmentation is computed by dividing the number of gaps by the number of extents,
so in this example the Extent Scan Fragmentation is ¼, or 25 percent.
A table using extents 8, 24, 40, and 56 has 3 gaps, and its Extent Scan Fragmentation is ¾, or 75 percent.
The maximum number of gaps is the number of extents - 1, so Extent Scan Fragmentation can never be 100 percent.

The value in DBCC SHOWCONTIG's output called Logical Scan Fragmentation is computed by dividing the number of Out Of Order Pages by the number of pages in the table.
This value is meaningless in a heap.

You can use either the Extent Scan Fragmentation value or the Logical Scan Fragmentation value to determine the general level of fragmentation in a table.
The lower the value, the less fragmentation there is.
Alternatively, you can use the value called Scan Density, which is computed by dividing the optimum number of extent switches by the actual number of extent switches.
A high value means that there is little fragmentation. Scan Density is not valid if the table spans multiple files, so all in all it is less useful than the other values.

Насчет ExtentScanDefragmentstion - значит экстенты под индексом расположены последовательно(потому что INDEXDEFRAG только меняет их местами), но не по порядку - т.е. межу ними вклинился по меньшей мере еще один екстент, в котором хранятся страницы другого(других) объектов и INDEXDEFRAG их не трогает( см п.1). При ребилде индекса, по всей видимости, индекс занимает те же екстенты( как вариант - попробовать убить индекс, сжать базу, и повесить индекс на место)
автор
which is computed by dividing the optimum number of extent switches by the actual number of extent switches.

Оптимально количество переключений между экстентами - 1( при двух экстентах - понятно), а реальных свичей было 2... См выше.
29 апр 04, 12:11    [656682]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить