Статья актуальна для MS SQL Server 2012.
Смотрим блокировки
Иногда требуется посмотреть, какие объекты и данные заблокированы и какие блокировки на этих объектах стоят (речь идет только о транзакционных блокировках). В SQL Server для этих целей существует динамическое представление sys.dm_tran_locks. Оно возвращает сведения об активных в данный момент в SQL Server ресурсах диспетчера блокировок.
Следующий запрос
SELECT * FROM sys.dm_tran_locks
выведет таблицу:
Нас интересуют следующие столбцы:
- resource_type - тип ресурса. Значение может быть одним из следующих: DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT или ALLOCATION_UNIT. Основные из них это - DATABASE - сама база данных; OBJECT - таблица или любой другой объект; PAGE - страница файла БД; KEY - указатель на ключ индекса; EXTENT - экстент файла БД; RID - физическая строка в куче.
- resource_database_id - идентификатор базы данных.
- resource_description - описание ресурса. Для ресурса типа PAGE в этом поле хранится адрес страницы в формате <file_id>:<page_in_file>; для KEY будет указан хэш ключевых столбцов; для RID адрес строки вида <file_id>:<page_in_file>:<row_on_page>.
- resource_associated_entity_id - идентификатор сущности, с которой связан ресурс. Для OBJECT - это ИД объекта; для KEY, PAGE и RID - идентификатор HoBt.
- request_mode - тип блокировки. Как раз в этой колонке указано, какая блокировка наложена на ресурс - S, U, X и пр.
Смотрим какие объекты заблокированы
Представление sys.dm_tran_locks выдает результат в неудобном для чтения виде. Одни идентификаторы и ничего более. Чтобы получить более наглядное отображение, необходимо воспользоваться еще несколькими инструментами SQL Server.
Название базы данных из ее идентификатора можно получить через функцию DB_NAME(database_id). Для обратного преобразования - DB_ID('database_name'). Для объектов существуют аналогичные функции OBJECT_NAME(object_id) и OBJECT_ID( 'database_name.object_name').
Чтобы посмотреть, какие данные хранятся на странице, достаточно ИД файла и ИД страницы. При чтении будет указан и объект, к которому эта страница относится, и сами данные. Но для индексов необходимо узнать еще имя самого индекса, чтобы можно было выполнить запрос.
Узнать, к какому объекту относится элемент блокировки, у которого указан только HoBt, можно по представлению sys.partitions. Оно содержит нужные нам колонки:
- object_id - идентификатор объекта, к которому относится сущность.
- index_id - идентификатор индекса объекта. Нужен для получения названия индекса для типа ресурса KEY. 0 - означает кучу, 1 - кластерный индекс, все остальные значения относятся к некластерным индексам.
- hobt_id - идентификатор сущности.
По index_id и object_id уже можно узнать имя индекса, по ключу которого установлена блокировка. Для этого воспользуемся еще одним представлением - sys.indexes. От него нам нужны следующие колонки:
- object_id - идентификатор объекта, к которому относится индекс.
- index_id - идентификатор индекса. Его мы знаем из таблицы sys.partitions.
- name - имя индекса. Если тип индекса 0 (куча), то имя будет Null.
Итоговый запрос, который покажет заблокированные объекты, может выглядеть следующим образом:
SELECT
CASE locks.resource_type
WHEN N'OBJECT' THEN OBJECT_NAME(locks.resource_associated_entity_id)
WHEN N'KEY'THEN (SELECT OBJECT_NAME(object_id) FROM sys.partitions WHERE hobt_id = locks.resource_associated_entity_id)
WHEN N'PAGE' THEN (SELECT OBJECT_NAME(object_id) FROM sys.partitions WHERE hobt_id = locks.resource_associated_entity_id)
WHEN N'HOBT' THEN (SELECT OBJECT_NAME(object_id) FROM sys.partitions WHERE hobt_id = locks.resource_associated_entity_id)
WHEN N'RID' THEN (SELECT OBJECT_NAME(object_id) FROM sys.partitions WHERE hobt_id = locks.resource_associated_entity_id)
ELSE N'Unknown'
END AS objectName,
CASE locks.resource_type
WHEN N'KEY' THEN (SELECT indexes.name
FROM sys.partitions JOIN sys.indexes
ON partitions.object_id = indexes.object_id AND partitions.index_id = indexes.index_id
WHERE partitions.hobt_id = locks.resource_associated_entity_id)
ELSE N'Unknown'
END AS IndexName,
locks.resource_type,
DB_NAME(locks.resource_database_id) AS database_name,
locks.resource_description,
locks.resource_associated_entity_id,
locks.request_mode
FROM sys.dm_tran_locks AS locks
WHERE locks.resource_database_id = DB_ID(N'database_name')
Смотрим, какие данные заблокированы. Тип ресурса KEY, RID
Для типа ресурса KEY в поле resource_description в представлении sys.dm_tran_locks хранится хэш ключевых столбцов индекса. Для каждой записи индекса SQL Server вычисляет хэш. Он не повторяется, даже если все значения в колонках одинаковы, т.к. для каждой строки добавляется уникальный идентификатор, который участвует в формировании хэша. Посмотреть хэши строк таблицы (если есть кластерный индекс) или записей индекса можно с помощью виртуальной колонки %%lockres%%. Если кластерного индекса нет, то вместо хэша выведется адрес строки вида <file_id>:<page_in_file>:<row_on_page> (который нам нужен для типа ресурса RID). Выводится в запросе как и обычная колонка:
SELECT %%lockres%% AS lockres, * FROM dbo.table_name (NOLOCK)
Результат вывода для таблицы без кластерного индекса:
Для таблицы с кластерным индексом:
Чтобы посмотреть хэши по записям индекса, необходимо выполнить запрос:
SELECT
%%lockres%% AS lockres,
*
FROM
dbo.table_name WITH (INDEX (index_name) NOLOCK)
Теперь, чтобы получить ключ индекса, достаточно выполнить запрос с условием по виртуальной колонке.
Например, для типа ресурса KEY:
SELECT
%%lockres%% AS lockres,
*
FROM
dbo._AccumRg7528 WITH (INDEX (_AccumR7528_ByProperty7546_RTRN) NOLOCK)
WHERE
%%lockres%% = '(143f95858242)'
Для RID такой:
SELECT
%%lockres%% AS lockres,
*
FROM
dbo._InfoRg6407 (NOLOCK)
WHERE
%%lockres%% = '1:115919:16'
Смотрим, какие данные заблокированы. Тип ресурса PAGE
Чтобы посмотреть, что хранится в странице файла базы данных, воспользуемся функцией DBCC PAGE (). Принимает параметры: DBCC PAGE(db_name|db_id, file_id, page_id, print_option). Последним параметром указывается формат вывода из следующих доступных значений:
- 0 — только заголовок;
- 1 — заголовок, дампы и индекс слотов;
- 2 — заголовок и полный дамп;
- 3 — заголовок и максимальная детализация для каждого слота.
Нам интересен формат вывода 3. Перед использованием функции необходимо включить флаг трассировки: DBCC TRACEON(3604), иначе вместо результата увидите только надпись, что команда выполнилась, и ничего более.
Если выполнить команду:
DBCC PAGE('database_name', 1, 423, 3)
то мы скорее всего увидим результат в виде текста:
Это не очень удобно, поэтому команду DBCC PAGE удобнее всего выполнять с опцией WITH TABLE RESULT:
DBCC PAGE('database_name', 1, 423, 3) WITH TABLERESULTS
Теперь та же информация представлена в виде таблицы:
Каждая страница (в общем виде) состоит из заголовка, раздела с данными и таблицы смещений. В заголовке стоит обратить внимание на поле m_type - тип страницы. 1 - означает страница с данными или кластерный индекс; 2 - страница с записями некластерного индекса; 10 - карта распределения индекса. После заголовка идут данные, распределенные по "слотам" (результат для страницы m_type = 1):
И вот здесь мы уже видим наши данные, которые хранятся в самой таблице. В моем случае это регистр сведений, у которого два измерения с типом Строка (поля _Fld11, _Fld12) и ресурс с типом Число (_Fld13).
Если на странице расположены записи индекса (m_type = 2), то в таблице ключей записи мы не увидим. Они выведутся в другой таблице уже без дополнительной информации (заголовков и пр.):
Получение структуры хранения базы данных
Чтобы получить размещение базы данных по страницам, можно воспользоваться функцией-представлением sys.dm_db_database_page_allocations. Принимает следующие аргументы: sys.dm_db_database_page_allocations(db_id , table_id , index_id , partidion_id , mode_option). В последнем аргументе передается формат вывода: ‘DETAILED’ или ‘LIMITED’.
Использовать в обычном SELECT-запросе:
SELECT
OBJECT_NAME(pages.object_id) AS object,
*
FROM
sys.dm_db_database_page_allocations(DB_ID('DBCC_PAGE'), NULL ,NULL,NULL,'DETAILED') AS pages
Результат:
В колонке allocated_page_file_id будет указан адрес страницы.
