Главная » Microsoft SQL Server, Базы данных » Команды утилиты DBCC

0

Для обслуживания SQL Server компания Microsoft предлагает утилиту DBCC, которая насчитывает тридцать четыре команды.

В первую очередь стоит ознакомиться с командой help, которая выводит на экран синтаксис всех доступных в утилите DBCC команд.

DBCC Help (‘CheckDB’);

Будет получен следующий результат:

CheckDB [ ( ‘ имя_ б а зы_да иных’

[, NOINDEX | REPAIR])]

[WITH NO_INFOMSGS[, ALL_ERRORMSGS]

[, PHY SI CAL__ONL Y ]

[, ESTIMATEONLY] [, TABLOCK]]

DBCC execution completed. If DBCC printed error messages,contact your system administrator.

Все команды утилиты DBCC выводят информацию о выполняемых действиях и обнаруженных ошибках, после чего предлагают пользователю обратиться со списком ошибок к системному администратору. Если вы считаете себя профессионалом, то лучше вас с данной утилитой никто не справится.

Для поддержания обратной совместимости с предыдущими версиями сервера в утилиту dbcc было включено несколько устаревших команд, которые в настоящей главе мы рассматривать не будем. Например, команда dbcc rowlock больше не используется, поскольку SQL Server 2005 выполняет блокировку строк автоматически.

Полный список устаревших команд утилиты DBCC, в том числе поддерживаемых в новой версии сервера баз данных, вы найдете по адресу:

http://msdn2.microsoft.com/en-US/library/msl44262.aspx

Проверка целостности базы данных

Команда DBCC CheckDB выполняет серию проверок целостности внутренней структуры базы данных. Корректность физической структуры исключительно важна для правильного функционирования базы данных. Эта команда выполняет проверку таких объектов, как указатели индексов, смещения страниц данных, связи между страницами индексов и данных, а также структуры страниц индексов и данных. Если в результате сбоя питания какая-либо страница базы данных была записана только наполовину, то лучшим средством обнаружения и устранения ошибки будет команда DBCC CheckDB. В качестве примера проверим целостность учебной базы данных OBXKites:

DBCC CheckDB (‘OBXKites’);

Будет получен следующий результат (приводится с сокращениями):

DBCC results for ‘OBXKites’.

DBCC results for ‘sysobjects’.

There are 114 rows in 2 pages for object ‘sysobjects’.

DBCC results for ‘sysindexes’.

There are 77 rows in 3 pages for object ‘sysindexes’.

DBCC results for ‘ProductCategory’.

There are 8 rows in 1 pages for object ‘ProductCategory’.

DBCC results for ‘Product’.

There are 55 rows in 1 pages for object ‘Product’.

CHECKDB found 0 allocation errors

and 0 consistency errors in database ‘OBXKites’.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Полученный вами результат может отличаться от приведенного в книге. Все На заметку зависит от конфигурации сервера и тех изменений, которые были внесены в базу данных.

С помощью двух параметров — all_errormsgs и no_inf omsgs — можно задать необходимый уровень детализации информационных сообщений. Параметр estimate_only позволяет получить примерный размер базы данных tempdb, необходимый для выполнения команды CheckDB.

Если база данных слишком большая, можно воспользоваться параметром no index и пропустить проверку целостности всех пользовательских некластеризованных индексов. Еще больше времени поможет сэкономить параметр Physical_Only— он выполнит только самую критичную проверку физической структуры страниц. Вы можете воспользоваться этим параметром, если нет времени для полноценной проверки базы командой CheckDB или если индексы были недавно созданы.

            1 Чтобы получить список системных таблиц текущей базы данных, можете выпол-

SVS нить запРос к представлению каталога sysobjects, указав в качестве фильтра I * выражение type_desc=1 system table 1. То же можно сделать и для других ти-             ? пов объектов, в частности, для пользовательских таблиц (user table), различных типов ограничений (primary_key_constraint, default_constraint,

CHECK_CONSTRAINT, UNIQUE_CONSTRAINT И FOREIGN_KEY_CONSTRAINT),

хранимых процедур (sql_stored_procedure), триггеров (sql_trigger) и представлений (view). Вы также откроете для себя специальные объекты SQL Server, такие как внутренние таблицы (internal_table) и служебные запросы

(SERVICE_QUEUE).

Воссоздание базы данных

Если при проверке базы данных были выявлены ошибки, утилита DBCC попытается их устранить. Эта операция отличается от обычной проверки целостности, так как база данных должна быть предварительно переведена в монопольный (т.е. однопользовательский) режим с помощью хранимой процедуры sp_dboption. Только после этого можно будет выполнить команду DBCC CheckDB с параметром Repair_Rebuild. Не забудьте после проверки восстановить исходный режим работы базы данных.

ЕХЕС sp_dboption OBXKites, ‘single_user’, ‘True';

DBCC CheckDB (‘OBXKites1, Repair_Rebuild);

GO

EXEC sp_dboption OBXKites, 1Single_user1, ‘False';

GO

Будет получен следующий результат (приводится с сокращениями):

DBCC results for ‘OBXKites’.

Service Broker Msg 9675, State 1: Message Types analyzed: 14.

Service Broker Msg 9616, State 1: Service Contracts analyzed: 6.

Service Broker Msg 9667, State 1: Services analyzed: 3.

DBCC results for ‘OrderPriority’.

There are 1 rows in 1 pages for object "OrderPriority".

DBCC results for ‘Productcategory’.

There are 8 rows in 1 pages for object "Productcategory".

CHECKDB found 0 allocation errors and 0 consistency errors in database ‘OBXKites’.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

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

?               Repair_Fast. Самый простой режим проверки. Восстанавливает ключи некластери- зованных индексов, но оставляет нетронутыми страницы данных.

?               Repair_Rebuild. Средний уровень проверки. Выполняет полную проверку некла- стеризованных индексов и индексных указателей, но не затрагивает страницы данных.

?               Repair_Allow_Data_Loss. Наиболее сложная проверка. Воссоздает и перестраивает все индексы вместе с указателями на страницы данных, удаляет все повреждения, обнаруженные на страницах данных. Во время обновления структуры страниц данных возможна частичная потеря информации.

Выполняйте команду dbcc CheckDB ежедневно и после каждого сбоя оборудования. Если будет обнаружена какая-либо ошибка, запустите режим Repair_ Rebuild, чтобы попытаться восстановить базу данных перед использованием режима Repair_Allow_Data_Loss, допускающего потерю информации. Если вы имеете дело с особо крупными базами данных при наличии требования повышенной доступности, то можете выполнять проверку раз в неделю в период снижения активности пользователей.

Многопользовательский режим

В версии SQL Server 2005 стало возможно выполнять команду DBCC CheckDB, не требуя выхода других пользователей из базы данных. Все процессы будут разбиты на отдельные потоки, задействующие все доступные процессоры. Следует отметить, что команда CheckDB интенсивно использует процессор и дисковую подсистему. По этой причине запускайте ее тогда, когда к базе подключено минимальное число пользователей.

Теперь одновременно с командой dbcc CheckDB вы можете выполнять коман-

Новинка V ДЫ DBCC CHECKALLOC, DBCC CHECKTABLE И DBCC CHECKCATALOG.

2005 4

Если одновременно с выполнением команды DBCC CheckDB с базой данных работают пользователи, то утилита DBCC использует стандартную блокировку схемы. Параметр TabLock несколько снижает гранулярность блокировок. Несмотря на то что работа команды DBCC CheckDB будет менее эффективной, конкуренция в базе данных станет выше, в результате чего пользователи смогут продолжить работу с ней.

DBCC CheckDB (‘OBXKites’) With TabLock;

Будет получен следующий результат (приводится с сокращениями):

DBCC results for ‘OBXKites’.

DBCC CHECKDB will not check SQL Server catalog or Service Broker consistency because a database snapshot could not be created or because WITH TABLOCK was specified.

DBCC results for ‘syssysrowsetcolumns’.

There are 654 rows in 6 pages for object "syssysrowsetcolumns".

DBCC results for ‘syssysrowsets’.

DBCC results for ‘ProductCategory’.

There are 8 rows in 1 pages for object "ProductCategory".

CHECKDB found 0 allocation errors and 0 consistency errors in database ‘OBXKites’.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

При использовании параметра TabLock в вывод добавляется дополнительная На заметку строка “DBCC CHECKDB will not check SQL Server catalog or Service Broker consistency because a database snapshot could not be created or because WITH TABLOCK was specified”. Это предупреждение о том, что проверка каталога SQL Server и целостности брокера служб выполняться не будет, поскольку моментальный снимок базы данных не может быть создан либо использован параметр with tablock. Это— чисто информационное сообщение, так что не стоит особо волноваться на этот счет. В то же время, когда вы встретите это сообщение, не используя параметр TabLock, проверьте, все ли необходимые службы запущены.

Проверка на уровне объектов

Команда DBCC CheckDB выполняет комплексную проверку целостности структуры базы данных. В то же время можно выполнить и отдельные проверки и при этом получить дополнительную информацию об отдельных объектах. По этой причине лучше выполнять команду DBCC CheckDB ежедневно, а специализированные версии, привязанные к объектам, использовать на этапе отладки.

Если база данных требует восстановления, всегда используйте полноценную версию CheckDB, не останавливаясь на следующих специализированных командах.

?               DBCC CheckAlloc (‘ база_данных’). Подмножество команды CheckDB, проверяющее физическую структуру базы данных. Создается отчет с высокой степенью детализации. В нем перечисляется количество экстентов (занимающих 64 Кбайт или восемь страниц данных) и страницы данных, используемые всеми таблицами и индексами.

?               DBCC CheckFileGroup (‘файловая группа’). Команда, аналогичная CheckDB, но проверяющая всего одну заданную файловую группу.

?               DBCC CheckTable (‘ таблица’). Выполняет несколько параллельных проверок таблицы.

?               DBCC CleanTable (‘ база_данных’ , ‘ таблица ‘). Перераспределяет пространство, освобожденное после удаления из таблицы столбца с типом varchar, nvarchar, text или ntext. Фактически эта команда обновляет базу данных; она не включена в состав команды CheckDB, если не используется параметр полного восстановления. Исходя из этого, если обновление тестовых полей базы данных выполняется регулярно, рекомендуется включить эту команду в план ежедневного обслуживания базы данных.

Целостность данных

Над слоем физической структуры базы находится слой данных, проверка которого выполняется с помощью следующих команд. Перечисленные ниже три команды DBCC не входят в состав DBCC CheckDB — их нужно выполнять отдельно.

?               DBCC CheckCatalog ( 1 база_данных’ ). Проверка целостности системных таблиц базы данных для обеспечения ссылочной целостности таблиц, представлений, столбцов и типов данных. При нормальных условиях отчет не слишком детализирован. Эта команда не исправляет обнаруженные ошибки. Если в базе данных будут обнаружены ошибки, придется с помощью сценария заново создать базу или таблицу, после чего переместить сохранившиеся данные из старой таблицы в новую. Если ошибки не обнаружены, то отчет не содержит никакой полезной информации.

?               DBCC CheckConstraints {‘таблица’, ‘ограничение’). Проверка целостности указанного ограничения или всех ограничений таблицы. Для проверки ограничений эта команда фактически генерирует и выполняет запрос, после чего выводит отчет обо всех обнаруженных ошибках. Как и в случае с командой CheckCatalog, если ошибки не обнаружены, никакой ценной информации не выводится.

?               DBCC Checkldent ( 1 таблипа’ ). Проверяет согласованность значений столбца идентичности заданной таблицы. Если существует проблема, то для исправления ошибки корректируется следующее значение столбца идентичности. Если столбец идентичности поврежден, то его новое значение нарушит ограничения уникальности и первичного ключа, после чего новые строки не смогут быть вставлены в таблицу.

В следующем примере продемонстрировано использование команды DBCC Checkldent: Use СНА2;

DBCC Checkldent (‘Customer’);

Будет получен следующий результат:

Checking identity information: current identity value ‘127’, current column value ‘127’.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Обслуживание индексов

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

Фрагментация базы данных

По мере вставки информации на страницы данных и индексов страницы заполняются на 100%. Как только достигается этот порог, SQL Server выполняет разбиение страницы на две, каждая из которых заполнена примерно на 50%. Несмотря на то что такой подход решает проблемы, связанные с отдельными страницами, внутренняя структура базы данных становится фрагментированной.

Чтобы продемонстрировать эффект выполнения команды DBCC, влияющей на фрагментированные таблицы и индексы, нужна сравнительно большая таблица. В следующем сценарии создается подходящая таблица и ее некластеризованный индекс. Кластеризованным первичным ключом является глобальный уникальный идентификатор GUED. По этой причине вставка выполняется в различные части таблицы, что создает первопричину изрядной фрагментации.

USE Tempdb;

CREATE TABLE Frag (

FragID UNIQUEIDENTIFIER NOT NULL DEFAULT NewID(),

Coll INT,

Col2 CHAR(200),

Created DATETIME DEFAULT GetDate(),

Modified DATETIME DEFAULT GetDate()

) ;

ALTER TABLE Frag

ADD CONSTRAINT PK_Frag PRIMARY KEY CLUSTERED (FragID);

CREATE NONCLUSTERED INDEX ix_col ON Frag (Coll);

Запуск следующей хранимой процедуры приведет к вставке в таблицу ста тысяч строк:

CREATE PROC AddlOOK AS

SET nocount on;

DECLARE @X INT;

SET @X = 0;

WHILE @X < 100000 BEGIN

INSERT Frag (Coll,Col2)

VALUES (@X, ‘sample data’);

SET @X = @X + 1;

END

GO

В следующем пакете мы вызываем эту хранимую процедуру, заполняя таблицу Frag (наберитесь терпения, поскольку выполнение данного пакета может занять несколько минут):

EXEC AddlOOK;

EXEC AddlOOK;

EXEC AddlOOK;

EXEC AddlOOK;

EXEC AddlOOK;

Выполнение команды DBCC ShowContig (таблица, индекс) приведет к созданию отчета о фрагментации и плотности данных заданной таблицы и индекса. Имея в наличии полмиллиона строк, таблица Frag является достаточно фрагментированной. При этом большая часть страниц заполнена только наполовину, что и следует из отчета:

DBCC ShowContig (frag) WITH ALL_INDEXES;

В следующих результатах индекс с номером 1 является первичным кластеризованным ключом, так что он также присутствует в отчете, индекс с номером 2 является некластеризованным:

DBCC SHOWCONTIG scanning ‘Frag’ table…

Table: ‘Frag1 (1227255527); index ID: 1, database ID: 2 TABLE level scan performed.

-                                                                                              Pages Scanned   : 22056

-                                                                                              Extents Scanned : 2772

-                                                                                              Extent Switches  : 22055

-                                                                                              Avg. Pages per Extent       : 8.0

-                                                                                              Scan Density [Best Count:Actual Count]          : 12.50%

[2757:22056]

-                                                                                              Logical Scan Fragmentation         : 99.24%

-                                                                                              Extent Scan Fragmentation                : 12.63%

-                                                                                              Avg. Bytes Free per Page  : 2542.0

-                                                                                              Avg. Page Density (full) : 68.59%

DBCC SHOWCONTIG scanning ‘Frag’ table…

Table: ‘Frag’ (1227255527); index ID: 2, database ID: 2 LEAF level scan performed.

-                                                                                              Pages Scanned   : 2748

-                                                                                              Extents Scanned : 348

-                                                                                              Extent Switches  : 2721

-                                                                                              Avg. Pages per Extent       : 7.9

-                                                                                              Scan Density [Best Count:Actual Count]          : 12.64%

[344:2722]

-                                                                                              Logical Scan Fragmentation         : 98.07%

-                                                                                              Extent Scan Fragmentation                : 99.14%

-                                                                                              Avg. Bytes Free per Page  : 3365.3

-                                                                                              Avg. Page Density (full) : 58.42%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Команда DBCC IndexDefrag выполняет дефрагментацию страниц как кластеризованных индексов, так и некластеризованных. Она организует узлы для повышения производительности, уменьшения размеров индекса и повышения коэффициента заполнения индексных страниц. Ее синтаксис следующий:

DBCC IndexDefrag (имя_базы, имя_ та блицы, имя_индекса) ;

Выполнение операции DBCC IndexDefrag аналогично перестройке индекса, однако имеет то преимущество, что дефрагментация разбивается на серию небольших транзакций, что позволяет снизить вероятность блокировки пользователей, выполняющих вставку и обновление данных.

Следующие две команды выполняют дефрагментацию обоих индексов таблицы Frag: DBCC IndexDefrag (‘Tempdb’, ‘Frag’, ‘PK_Frag1);

Вот результат:

Pages Scanned Pages Moved Pages Removed

22033      15194                   6852

Вторая команда:

DBCC IndexDefrag (1Tempdb1, 1 Frag’, ‘ix_col’);

Результат таков:

Pages Scanned Pages Moved Pages Removed

2748        1610                     1134

Теперь с помощью команды DBCC ShowContig проверим результаты дефрагментации индекса. Как мы видим, проблемы логической фрагментации и наполнения страниц, созданные при вставке полумиллиона строк, были решены:

DBCC ShowContig (frag) WITH ALL_INDEXES;

DBCC SHOWCONTIG scanning ‘Frag’ table…

Table: ‘Frag’ (1227255527); index ID: 1, database ID: 2 TABLE level scan performed.

-                                                                                              Pages Scanned   :               152 04

-                                                                                              Extents Scanned :               1915

-                                                                                              Extent Switches :               1925

-                                                                                              Avg. Pages per Extent       :               7.9

-                                                                                              Scan Density [Best Count:Actual Count]         :               98.70%

[1901:1926]

-                                                                                              Logical Scan Fragmentation        :              0.60%

-                                                                                              Extent Scan Fragmentation                :               15.25%

-                                                                                              Avg. Bytes Free per Page  :               3 8.9

-                                                                                              Avg. Page Density (full) :              99.52%

DBCC SHOWCONTIG scanning ‘Frag’ table…

Table: ‘Frag’ (1227255527); index ID: 2, database ID: 2 LEAF level scan performed.

-                                                                                              Pages Scanned   :               1614

-                                                                                              Extents Scanned :               205

-                                                                                              Extent Switches :               207

-                                                                                              Avg. Pages per Extent       :               7.9

-                                                                                              Scan Density [Best Count:Actual Count]         :               97.12%

[202 :208]

-                                                                                              Logical Scan Fragmentation        :              1.05%

-                                                                                              Extent Scan Fragmentation                :               99.02%

-                                                                                              Avg. Bytes Free per Page  :               41.5

-                                                                                              Avg. Page Density (full) :              99.49%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

На данный момент в базе данных tempdb накопилась масса объектов, которые было бы неплохо удалить с помощью следующего программного кода:

DROP TABLE Frag;

GO

DROP PROCEDURE AddlOOK;

GO

Статистика индексов

Полезность индекса определяется распределением данных внутри него. Например, если 60% заказчиков живут в Москве, их поиск будет быстрее выполнить путем сканирования таблицы, а не использования индекса. В то же время для поиска единственного заказчика, который живет в Урюпинске, запросу потребуется помощь индекса. Оптимизатор запросов принимает в расчет статистику индексов и на ее основе делает выводы относительно целесообразности использования индексов при выполнении конкретного запроса.

Статистика отображается в некоторых отчетах как индексы с именами, начинающимися с _WA_Sys или heed_.

Команда DBCC Show_Statistic выводит отчет с указанием даты сбора статистики. В этом отчете содержится базовая статистика индекса, включая его полезность. Низкая плотность указывает на высокую избирательность индекса; высокая демонстрирует то, что один узел индекса указывает на несколько строк, следовательно, он менее полезный.

В следующем коде продемонстрировано использование команды Update Statistics: use cha2;

exec sp_help customer;

Update Statistics Customer;

Системные хранимые процедуры sp_createstats и sp_updatestats создают и обновляют статистику всех таблиц базы данных.

I Чтобы увидеть операционную статистику индекса, выберите в динамическом SVS представлении управления sysdm_db_index_operational_stats. Также вы ^ I * можете получить статистику использования с помощью представления index_ ———J usage_stats и физическую статистику с помощью sysdm_db_index_physical_ stats. Представления sysdm_db_index_operational_stats и sysdm_db_ index_physical_stats требуют предоставления всех аргументов или заменяющих их значений null. Обязательными для ввода являются аргументы идентификаторов базы данных, объекта и индекса, равно как и номер раздела.

Плотность индекса

Плотность индекса определяется процентным отношением числа страниц индекса, содержащих данные. Если плотность индекса низкая, то для получения доступа к индексированным данным серверу придется просмотреть больше страниц. Коэффициент заполнения определяет процент индексных страниц, содержащих данные, после создания или дефрагментации индекса. Следует отметить, что плотность индекса немного изменяется с каждой операцией вставки, обновления или удаления.

Команда DBCC DbReIndex полностью перестраивает индекс. По существу, эта команда является аналогом последовательных операций удаления и создания индекса, за тем исключением, что она дает возможность задания коэффициента заполнения. В противоположность этому команда DBCC IndexDef rag восстанавливает исходную фрагментацию индекса, однако не доводит ее до значения заданного коэффициента заполнения.

Следующая команда воссоздает индексы таблицы Frag и устанавливает коэффициент заполнения в 98%:

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

Размер файла базы данных

СУБД SQL Server 7.0 оставила далеко позади используемую в предыдущих версиях методику выделения пространства в файлах с фиксированным размером, называемых устройствами. Начиная с версии 7.0 данные и журнал транзакций автоматически увеличиваются в размерах по мере необходимости. И все же управление размерами файлов входит в состав важных задач обслуживания базы данных. Без мониторинга и вмешательства пользователя файлы данных могут разрастись до небывалых размеров. Управление размерами файлов осуществляется с помощью рассмотренных далее команд утилиты DBCC.

I Для просмотра информации о размере файла базы данных выберите в дина- SVS мическом представлении управления sysdm_db_f ile_space_usage.

Мониторинг размеров файлов базы данных

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

Текущий и максимальный размеры файлов хранятся в системной таблице sys files: SELECT name, size, maxsize from sysfiles;

Будет получен следующий результат: name        size maxsize

CHA2               280            -1

CHA2_log 96   268435456

Для определения доли используемого пространства в файле можно воспользоваться системной хранимой процедурой sp_spaceused. Команда DBCC Updateusage гарантирует точность информации об использовании пространства файла:

DBCC Updateusage (‘tempdb’);

EXEC sp_spaceused;

Результат такоз:

database_name    database_size unallocated space

Tempdb               210.56 MB                       73.44 MB

reserved data                      index_size unused

138368 KB 122168 KB 14504 KB                      1696 KB

I Чтобы просмотреть, сколько пространства использовалось в текущей сессии, S VS можно выбрать в динамическом представлении управления sysdm_db_session_ _____ | * space_usage.

Для определения размера файла журнала транзакций и свободного места в нем используется команда DBCC SQLPerf (LogSpace).

Эта команда возвращает результат следующего вида:

Database Name Log Size (MB) Log Space Used (%) Status

master        3.3671875                   33.207657                                   0

tempdb       0.7421875                    59.473682                                   0

model         0.4921875                   63.194443                                   0

OOD          0.484375                     72.278229                                   0

MS             0.7421875                   37.302631                                   0

DBCC execution completed.

If DBCC printed error messages, contact your system administrator.

Для мониторинга свободного пространства в дисковой подсистеме сервера используется процедура xp_f ixeddrives, возвращающая такой результат: drive MB Free

С          429

F          60358

Сжатие базы данных

Если база данных не сконфигурирована для автоматического сжатия в фоновом режиме, то свободное пространство, освобождаемое при удалении неиспользуемых объектов и строк, не возвращается операционной системе. Файлы в данном случае сохраняют максимальный размер, которого достигли данные. Если данные постоянно добавляются и удаляются, регулярное сжатие и наращивание размера файла будет пустой тратой времени. Однако если дисковое пространство представляет собой особую ценность, то после удаления информации нужно выполнять ручное сжатие файла данных. Сжатие может выполняться параллельно с любыми транзакциями.

Использование автоматического сжатия в течение дня, когда пользователи ак- На заметку тивно работают с данными, может негативно сказаться на общей производительности. Как правило, автоматическое сжатие выполняют в нерабочее время.

Использование команды DBCC ShrinkDatabase позволяет уменьшить размеры файлов базы данных. При этом выполняются две основные задачи.

1.              Данные упаковываются в начале файла, а пространство в конце файла остается свободным.

2.              Свободное пространство в конце файла обрезается, за счет чего уменьшается размер файла.

Управление этими двумя действиями осуществляется с помощью параметров, описанных ниже.

?               Параметр notruncate приводит к выполнению командой DBCC ShrinkDatabase только первого действия — выполняется упаковка данных, но размер файла не изменяется.

?               Параметр t rune at eon ly приводит к обрезанию свободного пространства в конце файла, предварительно не выполняя упаковку данных.

?               Целевой размер файла может быть задан с помощью указания процента свободного пространства после сжатия файла. Так как автоматическое увеличение размеров является достаточно затратной операцией, полезно оставлять некоторый объем свободного пространства. Если необходимый размер превышает доступное пространство, то размеры файла не изменяются.

В следующем примере выполняется сжатие базы данных OBXKites с 10%-ным запасом свободного пространства:

DBCC ShrinkDatabase (‘OBXKites’, 10);

Будет получен следующий результат:

DBCC SHRINKDATABASE: File ID 3 of database ID 12 was skipped because the file does not have enough free space to reclaim.

Dbld Fileld CurrentSize MinimumSize UsedPages EstimatedPages

12 1    216                 152                 184                 184

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Как видно из примера, не все файлы имеют достаточно места для перераспределения. Данная команда отображает старые и новые размеры файлов, давая понять, какие изменения произошли в базе данных. Команда DBCC ShrinkDatabase влияет на все файлы базы данных, в то время как команда DBCC ShrinkFile оказывает влияние только на файл, указанный в аргументах.

.Дополнительная База данных может быть сконфигурирована для автоматического сжатия фай- информация лов. Подробно об этом процессе настройки см. в главе 34.

Сжатие журнала транзакций

При сжатии базы данных вместе с ней сжимается и журнал транзакций. Использование параметров notruncate и truncateonly никак не отражается на сжатии журнала транзакций. Если существует несколько файлов журнала транзакций, то SQL Server сжимает их так, будто они представляют собой единый последовательный файл.

Наиболее распространенной проблемой, связанной с журналом транзакций, является постоянное увеличение размера файла. Причиной такого поворота событий может стать старая открытая транзакция. Журнал транзакций состоит из виртуальных разделов. Успех или неудача выполнения операции сжатия журнала зависит от срока действия транзакций в виртуальных разделах и установленных контрольных точек. SQL Server может сжимать журнал транзакций путем удаления данных, которые старше самой давней транзакции в структуре виртуальных журналов.

Для проверки факта существования старых транзакций в журнале используется команда DBCC OpenTran:

USE OBXKites;

BEGIN TRAN;

UPDATE Product

SET ProductDescription = ‘OpenTran’

WHERE Code = ‘1002’;

DBCC OpenTran (‘OBXKites’);

Будет получен следующий результат:

Transaction information for database ‘OBXKites’.

Oldest active transaction:

SPID (server process ID): 57

UID (user ID) : -1 Name : user_transaction LSN : (19:524:2)

Start time : Mar 8 2006 4:20:38:890PM SID :

0x010500000000000515000000b4b7cd2267fd7c3043170a32eb030000 DBCC execution completed. If DBCC printed error messages, contact your system administrator.

На основании этой информации можно отыскать затерявшиеся транзакции, а затем прервать подключение пользователя. Узел Current Activity утилиты Management Studio может предоставить больше информации о подключении с заданным идентификатором. Существует еще одно, более категоричное решение проблемы — можно остановить сервер и перезапустить его, после чего выполнить сжатие базы данных.

Дополнительная На разрастание журнала транзакций оказывают влияние выбранная модель информация восстановления и порядок резервирования журнала. Более подробную инфор-        __________________ мацию об этих критичных вопросах см. в главе 36.

Когда вы найдете открытую транзакцию, закройте ее. В следующем примере подтверждается транзакция и проверяется, существуют ли в базе данных другие открытые транзакции: COMMIT TRAN;

DBCC OpenTran (‘OBXKites’);

Будет получен следующий результат:

No active open transactions.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Дополнительные команды утилиты DBCC

Оставшиеся семь команд утилиты DBCC используются для разрешения проблем, связанных с тестированием хранимых процедур и триггеров.

?               DBCC DropCleanBuf f ers. Очистка памяти, занятой буферизированными данными, чтобы они не влияли на производительность запросов во время тестирования.

?               DBCC Inputbuffer (SPID). Возвращает последнюю инструкцию, выполненную клиентом с заданным идентификатором SPID. По очевидным причинам эта команда может быть выполнена только членами серверной роли sysadmin.

?               DBCC Outputbuf fer {SPID). Возвращает результат последней инструкции, выполненной клиентом с заданным идентификатором. Подобно DBCC Inputbuffer, эта команда может быть выполнена исключительно членами серверной роли sysadmin.

?               DBCC PinTable (идентификатор_базы, идентификатор_объекта). Закрепляет таблицу в памяти. Старайтесь избегать закрепления таблиц в полном объеме. Гораздо эффективнее по мере необходимости выгружать в кэш отдельные страницы.

?               DBCC UnPinTable (идентификатор_базы, идентификатор_объекта). Удаляет таблицу из списка закрепленных в памяти.

?               DBCC Р г ос Cache. Выводит основную статистику процедурного кэша после того, как запросы и процедуры скомпилированы и сохранены в памяти.

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

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии