SQL.RU
 client/server technologies
 Главная | Документация | Статьи | Книги | Форум | Блоги | Опросы | Гостевая | Рассылка | Работа | Поиск | FAQ |

Путешествия экстентов и последствия DBREINDEX и SHRINKFILE

ПУБЛИКАЦИИ  

По материалам статьи Tony Rogerson: Extent Usage and Behaviours when using DBREINDEX and SHRINKFILE

В этой статье будет показано, как очень легко можно "прострелить себя в ногу", если не понимать принципы работы SHRINKFILE/SHRINKDATABASE и DBCC DBREINDEX или CREATE INDEX с опцией DROP_EXISTING. Правда здорово, когда после DBREINDEX фрагментация снижается с 99 % до 98 %! Давайте кратко рассмотрим некоторые инструменты, которые будут использоваться в этой статье:

DBCC SHOWCONTIG

Документированная в Books Online команда, которая в основном показывает состояние таблицы в терминах использования места и фрагментации (фрагментация экстентов в рамках базы данных и логическая фрагментация в рамках непосредственно таблицы или индекса).

DBCC EXTENTINFO

Загляните в статью http://support.microsoft.com/kb/324432/.

Эта полезная инструкция позволяет получить информацию об использовании экстентов в базе данных, и мы можем фиксировать эту информацию в таблице для последующего анализа.

DBCC DBREINDEX

Документированная в Books Online команда, которая пересоздать индекс в той его части, которая относится к фрагментации экстентов и логической фрагментации, показываемой командой DBCC SHOWCONTIG.

DBCC INDEXDEFRAG

Документированная в Books Online команда, которая в отличие от DBCC DBREINDEX и CREATE INDEX с опцией DROP_EXISTING (физически создающих новую копию индекса или таблицы, а затем задействующих эту копию вместо прежних индексов), позволяет избавится только от логической фрагментации, и не приводит к распределению новых экстентов. При этом никакие данные не копируются, они только перемещаются в очень коротких транзакциях, следовательно, эта команда может использоваться во время рабочей нагрузки на сервер.

DBCC SHRINKFILE

Документированная в Books Online команда, которая перемещает данные из конца базы данных, заполняя ими все свободные экстенты в начале базы данных, что может привести к физическому уменьшению размера файла. У неё есть несколько опций, которые перечислены в документации.

Хорошо, теперь перейдём к сути того, что я хочу объяснить.

По существу я хочу разъяснить, что получится после выполнения команд DBCC SHRINKFILE и DBCC SHRINKDATABASE. Вас бы удивило, если бы Вы узнали, как часто допускаются ошибки в работе с таблицей/индексом, когда вначале используется DBREINDEX / CREATE INDEX с опцией DROP_EXISTING, а потом это всё идёт насмарку, потому что тут же запускается SHRINKFILE / SHRINKDATABASE.
Создадим тестовую базу данных. Обратите внимание, что для тестов я использую отдельный файл, и это делается не только ради упрощения жизни при демонстрации; использование нескольких файлов и файловых групп даёт большие преимущества и является непреложным требованием непрерывного обслуживания данных!

USE [master] GO CREATE DATABASE [testfile] ON PRIMARY ( NAME = N'testfile', FILENAME = N'E:\SQL2005\testfile.mdf' , SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), FILEGROUP [FileTest] ( NAME = N'testfile1', FILENAME = N'E:\SQL2005\testfile1.ndf' , SIZE = 1MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'testfile_log', FILENAME = N'E:\SQL2005\testfile_log.LDF' , SIZE = 18560KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) COLLATE Latin1_General_CI_AS GO EXEC dbo.sp_dbcmptlevel @dbname=N'testfile', @new_cmptlevel=90 go ALTER DATABASE testfile MODIFY FILEGROUP FileTest DEFAULT; go

Давайте создадим и заполним первоначальный набор тестовых данных; обратите внимание, что я преднамеренно выполняю вставки в таблицы одновременно, чтобы продемонстрировать, как они становятся фрагментированными.

use testfile go create table test_table_1 ( id int not null identity unique clustered, somedata char(4000) not null ) create table test_table_2 ( id int not null identity unique clustered, somedata char(4000) not null ) go set nocount on declare @i int set @i = 1 while @i <= 10000 begin insert test_table_1 ( somedata ) values( replicate( 'a', 4000 ) ) insert test_table_2 ( somedata ) values( replicate( 'a', 4000 ) ) set @i = @i + 1 end go

Давайте создадим временную таблицу, в которую будем помещать результаты исполнения EXTENTINFO, структура таблицы будет зависеть от версии SQL Server, на которой выполняется команда.

if convert(int,substring(convert(binary(4), @@microsoftversion),1,1)) = 9 begin create table #extentinfo ( [file_id] smallint, page_id int, pg_alloc int, ext_size tinyint, obj_id int, index_id tinyint, partition_number int, partition_id bigint, iam_chain_type varchar(100), pfs_bytes varbinary(10) ) end go if convert(int,substring(convert(binary(4), @@microsoftversion),1,1)) = 8 begin create table #extentinfo ( [file_id] smallint, page_id int, pg_alloc int, ext_size tinyint, obj_id int, index_id tinyint, pfs_bytes varbinary(10) ) end go if convert(int,substring(convert(binary(4), @@microsoftversion),1,1)) = 7 begin create table #extentinfo ( [file_id] smallint, page_id int, pg_alloc int, ext_size tinyint, obj_id int, index_id tinyint, pfs_bytes varbinary(10), avg_used tinyint ) end go

На этом мы закончили подготовку тестовой среды, что позволяет нам приступить непосредственно к экспериментам.
Для начала, давайте посмотрим на результаты исполнения INSERT. Обратите внимание, как объекты расслоились по всей базе данных, что и принято называть "Extent Scan Fragmentation".

insert #extentinfo exec( ' dbcc extentinfo ( 0 ) with tableresults ' ) go -- Демонстрация расслоения экстентов select * from #extentinfo where file_id = 3 order by page_id Start Page ObjectId ------------ -------------- 8 2073058421 10 2105058535 12 2073058421 13 2073058421 14 2105058535 15 2105058535 16 2073058421 17 2105058535 18 2073058421

Давайте посмотрим теперь на то, что покажет команда DBCC SHOWCONTIG.

-- Обратить внимание на фрагментацию при сканировании экстентов. dbcc showcontig( test_table_1 ) dbcc showcontig( test_table_2 ) go DBCC SHOWCONTIG scanning 'test_table_1' table... Table: 'test_table_1' (2073058421); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned................................: 5000 - Extents Scanned..............................: 630 - Extent Switches..............................: 629 - Avg. Pages per Extent........................: 7.9 - Scan Density [Best Count:Actual Count].......: 99.21% [625:630] - Logical Scan Fragmentation ..................: 0.48% - Extent Scan Fragmentation ...................: 99.37% - Avg. Bytes Free per Page.....................: 70.0 - Avg. Page Density (full).....................: 99.14% DBCC SHOWCONTIG scanning 'test_table_2' table... Table: 'test_table_2' (2105058535); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned................................: 5000 - Extents Scanned..............................: 630 - Extent Switches..............................: 629 - Avg. Pages per Extent........................: 7.9 - Scan Density [Best Count:Actual Count].......: 99.21% [625:630] - Logical Scan Fragmentation ..................: 0.48% - Extent Scan Fragmentation ...................: 99.37% - Avg. Bytes Free per Page.....................: 70.0 - Avg. Page Density (full).....................: 99.14%

Посмотрите на это: 'Extent Scan Fragmentation' - удивлены?! Дело в том, что объекты действительно ужасно расслоились, и это очень похоже на реальную промышленную систему, именно так всё и случается на практике!
Пробуем теперь избавится от фрагментации:

dbcc indexdefrag( 0, test_table_1, 1) go

Эта команда не делает ничего существенного, INDEXDEFRAG занимается только тем, что устраняет 'Logical Scan Fragmentation', которая для обоих объектов и так в порядке.
Давайте теперь переиндексируем таблицу, чтобы она снова стала непрерывной.

dbcc dbreindex( test_table_1 ) go DBCC SHOWCONTIG scanning 'test_table_1' table... Table: 'test_table_1' (2073058421); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned................................: 5001 - Extents Scanned..............................: 626 - Extent Switches..............................: 625 - Avg. Pages per Extent........................: 8.0 - Scan Density [Best Count:Actual Count].......: 100.00% [626:626] - Logical Scan Fragmentation ..................: 0.02% - Extent Scan Fragmentation ...................: 0.64% - Avg. Bytes Free per Page.....................: 71.6 - Avg. Page Density (full).....................: 99.12%

Обратите внимание, как хорошо теперь смотрится значение 'Extent Scan Fragmentation', но что случилось с файлом нашей тестовой базы данных?

truncate table #extentinfo go insert #extentinfo exec( ' dbcc extentinfo ( 0 ) with tableresults ' ) select * from #extentinfo where file_id = 3 order by page_id go

Прекрасно! Размер базы данных увеличился, т.е. объект был создан на новом месте, что может быть приемлемо для тестов, но стать проблемой, если мы имеем дело с гигабайтами данных. Кроме того, в начале нашей базы данных теперь масса свободных экстентов, в том месте, где объект располагался первоначально, перед тем, как была выполнена дефрагментация (переиндексирование).
Что же SQL Server будет делать с этим свободным местом? Вы правильно решили, если подумали, что оно будет использовано в дальнейшем! Давайте создадим ещё один объект и заполним его данными.

create table test_table_3 ( id int not null identity unique clustered, somedata char(4000) not null ) go set nocount on declare @i int set @i = 1 while @i <= 10000 begin insert test_table_3 ( somedata ) values( replicate( 'a', 4000 ) ) set @i = @i + 1 end go

Посмотрим, куда были размещены данные…

truncate table #extentinfo go insert #extentinfo exec( ' dbcc extentinfo ( 0 ) with tableresults ' ) select * from #extentinfo where file_id = 3 order by page_id go

Как можно видеть, мы снова имеем слоеный пирог из экстентов; SQL Server использовал те же самые экстенты, которые стали доступными после исполнения DBREINDEX. Давайте теперь удалим эту таблицу, и посмотрим, что после этого случится.

drop table test_table_3 go truncate table #extentinfo go insert #extentinfo exec( ' dbcc extentinfo ( 0 ) with tableresults ' ) select * from #extentinfo where file_id = 3 order by page_id go

Всё снова вернулось к нормальному состоянию свободного места. Теперь давайте попробуем переиндексировать test_table_2, и посмотрим, что из этого выйдет, помня, что Extent Scan Fragmentation у этой таблицы был 99 %.

dbcc dbreindex( test_table_2 ) go dbcc showcontig( test_table_2 )

Ну надо же!? После исполнения этой команды Extent Scan Fragmentation стал лучше на 1 %, теперь фрагментация сканируемых экстентов составляет 98 %!
Давайте увеличим немного размер нашей тестовой базы данных с 130Mb до 500Mb…

ALTER DATABASE [testfile] MODIFY FILE ( NAME = N'testfile1', SIZE = 512000KB )

И теперь снова пробуем выполнить команду DBREINDEX.

dbcc dbreindex( test_table_2 ) go dbcc showcontig( test_table_2 )

Это уже лучше! Extent Scan Fragmentation стал теперь равным 0.48 %. Т.о. всего 32 страницы из 10063 остались свободны, и это за счёт 79Mb места в начале базы данных, которое было высвобождено.
Давайте выполним для test_table_1 команду DBREINDEX, что бы увидеть, к чему это приведёт, ну и, в общем, видно, что появился большой участок памяти в начале базы данных, который можно занять и при этом не испытывать проблем с Extent Scan Fragmentation. Если мы не увеличили бы размер базы данных, тогда команда DBREINDEX переместила бы test_table_1 назад, на те экстенты в начале, которые были заняты таблицей прежде. Так происходит по следующим причинам: a) в базе данных нет достаточного свободного участка памяти, и b) имеющиеся свободные экстенты перемешаны с экстентами test_table_2.
Так где же обещанные SHRINKFILE и SHRINKDATABASE, и как они связаны с рассматриваемой проблемой? По существу, Вы можете воспринимать их работу, как аналог команды DBREINDEX, т.е. они берут данные из конца базы данных и заполняют ими все свободные экстенты в начале базы данных, таким образом они могут уменьшить и высвободить неиспользуемое место в файлах. Это очень плохо, потому что объекты станут фрагментированными; очень многие администраторы делают DBREINDEX, а затем пытаются высвободить неиспользуемое место, считая это само собой разумеющейся последовательностью действий!
Будьте осторожны с использованием команд SHRINKFILE/SHRINKDATABASE, подход к задачам обслуживания баз данных у хорошего DBA/разработчика должен быть взвешенным и осмысленным.
Еще важнее всегда владеть полной информацией о ваших данных, всегда иметь достаточный резерв хранения, проводить плановые тесты, не бояться использовать множество файловых групп (в разумных пределах), если фрагментация и индекс порождают проблемы - помещать проблемный объект и/или индекс(ы) в выделенную файловую группу.
Так или иначе, системы обслуживания данных зависят от людей; и я искренне надеюсь, что Вы сумеете за ними уследить и понимаете их концепцию и поведение.

[В начало]

Перевод: Александра Гладченко  2007г.

Rambler's Top100 Рейтинг@Mail.ru  Administrator: Обратная связь 
Copyright: SQL.Ru 2000-2013