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

Откуда: Москва
Сообщений: 1014
Добрый день.
Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7600: )

Столкнулся с проблемой масштабируемости при паралельной работе большого количества пользователей вызывающих определенный участок кода.
Как выяснилось вызывают они Ad-Hock запрос который имеет в теле Scalar функцию.
Данная функция и вызывает проблемы блокировки Extents and Pages содержищих таблицы не используемые в запросе (и функциях).
Причем, было замечено, что блокируются разные экстенты и страницы меняя при этом блокированные таблицы. Отчего это зависит - непонятно еще. По идее табличная переменная хранится в TempDB да и делить экстенты с другими таблицами никак нельзя.
Все читаем без использования блокировок NOLOCK.
Запрос будет перенесен в процедуру и поидее это даже не проблема.
Но с функцией ничего не сделать с ходу.

Так же опытным путем установлено, что консктрукция расположенная в той самой функции
   INSERT INTO @t_crm_activity_type (seq_act_type_id)
   SELECT cat.seq_act_type_id
   FROM   dbo.crm_activity_type cat WITH(NOLOCK)
   JOIN   dbo.crm_activity_category cac WITH(NOLOCK) ON cat.seq_act_category_id = cac.seq_act_category_id
   WHERE  cac.act_cat_code LIKE 'WELCOME%'
как раз и вызывает эти самые блокировки.

Причем блокировки есть только на во время 2-х первых вызовов (первый вызов - компиляция + еще один вызов).

В приложеном файле модно увидить как раз те самые блокировки которые были отмечены при трассировки запроса профайлером.

Индексируемых представлений нет. Есть пара GuidePlans. Статистика с автообновлением, но ее в профайлере не видно.

Как такое можно объяснить?
И как выяснить истинную природу данного явления?

Присоединяйтесь все, кому интересна тема и если у вас есть идеи дальнейшей диагностики.

К сообщению приложен файл. Размер - 43Kb
7 сен 11, 06:08    [11238276]     Ответить | Цитировать Сообщить модератору
 Re: Ненобъяснимые U and X блокировки при выполнении вставки в табличную переменную  [new]
aleks2
Guest
1. Нда... опять мутный поток сознания.
2.
>>Данная функция и вызывает проблемы блокировки Extents and Pages содержищих таблицы не используемые в запросе (и функциях).
Сказочник, да?
7 сен 11, 11:28    [11239255]     Ответить | Цитировать Сообщить модератору
 Re: Ненобъяснимые U and X блокировки при выполнении вставки в табличную переменную  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1014
Совершенно согласен с aleks2. Муть еще та. Но честно говоря я так и не могу идентифицировать обращение к объектам, хранящимся в указаных выше страницах и экстентах.

Мой вопрос и заключается в том - как это выяснит. Может отрабазщывают какие то дополнительные события, кооторызх не видно явно. Проверял все что можно - пока не нашел.

Не первый год работаю с SQL Server, но толи глаз замылился, толи что то иное.

Присоединяйтесь, если есть мысли.
8 сен 11, 03:20    [11244834]     Ответить | Цитировать Сообщить модератору
 Re: Ненобъяснимые U and X блокировки при выполнении вставки в табличную переменную  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1014
Начались проясняться детали.
А именно:
Таблицы из запроса не большие и делят экстенты с несколькими другими. И, так как экстент начал первоначально зщаполняться другой таблицей, ее и вижу, как владельца экстента (тут и появилась путаница).

Но, это не снимает главного вопроса - почему же блокируются экстенты и страница да еще и с U и X уровнями блокировок.

В приложеном изображении видно как SQL Server производит 2 сканирования таблиц из запроса (или их частей).
Хотя в тоже время план показывает использование поиска по индексу.
Статистика при выполнении запроса не обновляется (данная настройка отключена на сервере).
Эскалации блокировок не видно.
Запрос выполнялся в однопоточном режиме.

Есть у кого т о соображение - почему такое происходит?

К сообщению приложен файл. Размер - 44Kb
8 сен 11, 05:12    [11244874]     Ответить | Цитировать Сообщить модератору
 Re: Ненобъяснимые U and X блокировки при выполнении вставки в табличную переменную  [new]
_Ultimate
Guest
aleksey_fomchenko,

Выполнил запрос
declare @table table (id int)

INSERT INTO @table (id)
   SELECT top 2 cat.id
   FROM   dbo.sysobjects cat WITH(NOLOCK)
	JOIN   dbo.syscolumns cac WITH(NOLOCK) ON cat.id = cac.id
   WHERE  cac.name LIKE 'Name%'

в профайлере получил такую же картинку блокировок как у вас
проверил что заблокированные страницы принадлежат нашей временной табличной переменной
(думаю что и экстенты принадлежат ей - не знаю просто как определить принадлежность экстента таблице, может DBCC page показывает какому экстенту принадлежит страница)

Открыл Кален Дилани "Microsoft SQL Server 2008 Internals"
Chapter 6 Indexes: Internals and Management
-Data Modifi cation Internals
--Inserting Rows
страница 347-348
автор
...If no pages with space are available, SQL Server tries to find unallocated pages from existing uniform extents that already belong to the object. If none exists, SQL Server must allocate a whole new extent to the table...


Я думаю мы видим блокировки накладываемые при выделении нового экстента для временной таблицы.
ИМХО - все по честному, в чем собственно вопрос то, кто и где кого блокирует ?

Вы бы раскрыли столбец database_id - какой базе принадлежит объект на который накладываются смущающие вас блокировки
я думаю вы там увидите 2 (tempdb)
8 сен 11, 13:35    [11247128]     Ответить | Цитировать Сообщить модератору
 Re: Ненобъяснимые U and X блокировки при выполнении вставки в табличную переменную  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
> Я думаю мы видим блокировки накладываемые при выделении нового экстента для временной таблицы.

угу. это все совершенно внутренние механизмы проявляются:
"Inside MS SQL 2005 Storage Engine - Locking"
Locks can be taken on extents units of disk space that are 64 kilobytes (KB) in size (eight pages of 8 KB each). This kind of locking occurs automatically when a table or an index needs to grow and a new extent must be allocated. You can think of an extent lock as another type of special purpose latch...

мешать это никак и никому не должно.
8 сен 11, 15:08    [11248137]     Ответить | Цитировать Сообщить модератору
 Re: Ненобъяснимые U and X блокировки при выполнении вставки в табличную переменную  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1014
Спасибо ребята.
Действительно глаз замылился - блокировки накладывались в TempDB (DatabaseID никто не отменял).
А так как по количеству файлов она у меня похода на рабочую базу, я не сразу смекнул.
Как я и говорил - вставка шла в смешаный экстент, что еще больше увело меня в сторону.
Вопрос снят.
9 сен 11, 02:40    [11251263]     Ответить | Цитировать Сообщить модератору
 Re: Ненобъяснимые U and X блокировки при выполнении вставки в табличную переменную  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
_Ultimate
думаю что и экстенты принадлежат ей - не знаю просто как определить принадлежность экстента таблице

DBCC EXTENTINFO
9 сен 11, 07:41    [11251345]     Ответить | Цитировать Сообщить модератору
 Re: Ненобъяснимые U and X блокировки при выполнении вставки в табличную переменную  [new]
_Ultimate
Guest
Knyazev Alexey,

Премного благодарен
+1 сокровенное знание в копилочку :-)
9 сен 11, 10:19    [11251940]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить