Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
selis76
Member

Откуда:
Сообщений: 387
Столкнулся со следующей проблемой (код создан платформой 1С):
при загрузке данных в таблицу регистра сведений, 1С сначала проверяет наличие похожих записей в таблице - запросом ниже. Эту проверку конечно можно отключать, но в данном случае вопрос к поведению запроса на проверку и понимание механизма блокировок SQL .
Записи грузятся пакетами по 100-1000 штук . Если грузить с разных сессий (около 40) - запрос на проверку начинает выполнятся сильно медленней (иногда 10 сек). Самое удивительное этот запрос на проверку становится источником Lock:timeout для других запросов но уже на вставку в таблицу, а ведь записи в пакетах не пересекаются по уникальным ключам и всегда разные в разных пакетах.
Общий алгоритм у 1С такой
1) Сначала набор записей вставляется в временную таблицу напр #tt54
2) Потом идет проверка существования записей в таблице _InfoRg18847
(@P1 numeric(10))SELECT
T1._Period,
T1._Fld18848RRef,
T1._Fld19005RRef,
T1._Fld19006RRef,
T1._Fld18849_TYPE,
T1._Fld18849_N,
T1._Fld18849_S,
T1._Fld19007,
T1._Fld18850RRef,
T1._Fld18851,
T1._Fld19008,
T1._Fld19009RRef,
T1._Fld19010RRef,
T1._Fld19011RRef,
T1._Fld19012RRef,
T1._Fld19013RRef,
T1._Fld19014RRef,
T1._Fld19015RRef,
T1._Fld19016RRef,
T1._Fld19017,
T1._Fld19018,
T1._Fld19019RRef,
T1._Fld19020RRef,
T1._Fld19021_TYPE,
T1._Fld19021_N,
T1._Fld19021_S,
T1._Fld19022,
T1._Fld628
FROM #tt54 T1 WITH(NOLOCK)
INNER JOIN _InfoRg18847 T2 
ON T1._Period = T2._Period AND T1._Fld18848RRef = T2._Fld18848RRef 
AND T1._Fld19005RRef = T2._Fld19005RRef 
AND T1._Fld19006RRef = T2._Fld19006RRef 
AND T1._Fld18849_TYPE = T2._Fld18849_TYPE 
AND T1._Fld18849_N = T2._Fld18849_N 
AND T1._Fld18849_S = T2._Fld18849_S 
WHERE (T2._Fld628 = @P1)

3) Далее записи из временной таблицы #tt54 вставляются в _InfoRg18847

Я проверил планы исполнения запроса (прилагается) . Есть попадание в индекс по полю Period. Странно только что система не использует покрывающий индекс полностью, но возможно оптимизатор считает так быстрее (план исполнения прилагается) .
CREATE NONCLUSTERED INDEX [_InfoR18847_ByPeriod_TRRRN] ON [dbo].[_InfoRg18847] 
(
	[_Fld628] ASC,
	[_Period] ASC,
	[_Fld18848RRef] ASC,
	[_Fld19005RRef] ASC,
	[_Fld19006RRef] ASC,
	[_Fld18849_TYPE] ASC,
	[_Fld18849_N] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


Сделал анализ sys.dm_exec_requests sys.dm_tran_locks sys.dm_os_waiting_tasks sys.dm_exec_sessions и трассировку в Profiler:
1) все запросы исполняются в режиме изоляции Read commited
2) Select ставит блокировку типа S , Insert блокировку типа IX . Вроде ничего подозрительного .
3) Проанализировал sys.dm_tran_locks - там вроде тоже ничего подозрительного
Select блокирует в режиме request_mode - S Object таблицу _InfoRg18847 с определенным набором
resource_lock_partition в количестве примерно равном количеству строк в пакете . При этом resource_description пуст
Никаких других блокировок (за исключением временных таблиц в Tempdb) нет.

Вопрос - что же тогда может тормозить запрос select? Что анализировать дальше?
Waits идут в основном на Locks, а не на вводе выводе.
У меня есть несколько предположений
1) Фактический план запроса имеет другой Cost нежели то, что показывает dm_exec_request.plan_handle . Можно ли через динамические View или трассу профайлера посмотреть актуальный план?
2) Sql сервер сам поднимает уровни блокировок. Но в трассировке я не увидел события Lock:escalation . Как я понимаю блокировки таблицы полностью должны отражаться в sys.dm_tran_locks, где object таблица, а resource_lock_partition =0?
3) Чтото еще?

К сообщению приложен файл (select info188847 execution plan.sqlplan - 34Kb) cкачать
25 янв 17, 12:48    [20143968]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
selis76
Member

Откуда:
Сообщений: 387
Вот какая красота получается когда select тормозит другие statements

К сообщению приложен файл. Размер - 129Kb
25 янв 17, 12:51    [20143987]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
selis76,
автор
Странно только что система не использует покрывающий индекс полностью,
он не покрывающий
25 янв 17, 13:00    [20144052]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
invm
Member

Откуда: Москва
Сообщений: 9823
selis76,

IX не совместима с S, U или X такой же гранулярности.
У вас select в 164-й сессии удерживает S (или U, или X) на таблицу, в которую остальные хотят добавлять данные.
25 янв 17, 13:07    [20144091]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
selis76
Member

Откуда:
Сообщений: 387
TaPaK
selis76,
автор
Странно только что система не использует покрывающий индекс полностью,
он не покрывающий

А что там не хватает? вроде все попадает под определение
http://sqlmag.com/database-performance-tuning/covered-query-vs-covering-index
25 янв 17, 13:07    [20144093]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
selis76
Member

Откуда:
Сообщений: 387
invm
selis76,

IX не совместима с S, U или X такой же гранулярности.
У вас select в 164-й сессии удерживает S (или U, или X) на таблицу, в которую остальные хотят добавлять данные.

Да но строки то разные 100% в разных сессиях, а блокировка как понимаю идет на уровне строк
25 янв 17, 13:09    [20144108]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
selis76,

_Fld18849_S у вас лукап на него

у вас же указанный индекс в плане и так + лукап на недостающее
25 янв 17, 13:13    [20144127]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
selis76
invm
selis76,

IX не совместима с S, U или X такой же гранулярности.
У вас select в 164-й сессии удерживает S (или U, или X) на таблицу, в которую остальные хотят добавлять данные.

Да но строки то разные 100% в разных сессиях, а блокировка как понимаю идет на уровне строк


автор
3) Проанализировал sys.dm_tran_locks - там вроде тоже ничего подозрительного3) Проанализировал sys.dm_tran_locks - там вроде

вот там и посмотрите кто что и на что накладывает
25 янв 17, 13:16    [20144139]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
invm
Member

Откуда: Москва
Сообщений: 9823
selis76
Да но строки то разные 100% в разных сессиях, а блокировка как понимаю идет на уровне строк
Во-первых, IX на строки не бывает.
Во-вторых, вставляльщики ждут IX на таблицу. Это видно на вашей картинке.
Если ждем IX на таблицу, значит в блокирующей сессии есть S, U или X на эту же таблицу.
25 янв 17, 13:18    [20144149]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
selis76
Member

Откуда:
Сообщений: 387
invm
selis76
Да но строки то разные 100% в разных сессиях, а блокировка как понимаю идет на уровне строк
Во-первых, IX на строки не бывает.
Во-вторых, вставляльщики ждут IX на таблицу. Это видно на вашей картинке.
Если ждем IX на таблицу, значит в блокирующей сессии есть S, U или X на эту же таблицу.

А где это написано?
Я перерыл все по поиску, но нигде не нашел явного упоминания когда блокировка идет по строкам а когда на всю таблицу
https://msdn.microsoft.com/en-us/library/jj856598(v=sql.120).aspx
"Protects requested or acquired exclusive locks on some (but not all) resources lower in the hierarchy"
да и потом какой смысл MS SQl блокировать всю таблицу в режиме Read commited?
25 янв 17, 13:36    [20144257]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
selis76
Member

Откуда:
Сообщений: 387
TaPaK
selis76
пропущено...

Да но строки то разные 100% в разных сессиях, а блокировка как понимаю идет на уровне строк


автор
3) Проанализировал sys.dm_tran_locks - там вроде тоже ничего подозрительного3) Проанализировал sys.dm_tran_locks - там вроде

вот там и посмотрите кто что и на что накладывает

так я посмотрел "Select блокирует в режиме request_mode - S Object таблицу _InfoRg18847 с определенным набором
resource_lock_partition в количестве примерно равном количеству строк в пакете "
25 янв 17, 13:38    [20144272]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
selis76,

IX просто может быть только на страницу и выше (с вашего же комментария some (but not all) resources lower in the hierarchy)
25 янв 17, 13:39    [20144283]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
selis76,
автор
так я посмотрел "Select блокирует в режиме request_mode - S Object таблицу _InfoRg18847 с определенным набором
resource_lock_partition в количестве примерно равном количеству строк в пакете "

прям так там и написано??? примерно равном и не пересекающемся с примерным намерением X на вставке?
25 янв 17, 13:41    [20144293]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
selis76
Member

Откуда:
Сообщений: 387
TaPaK
selis76,
автор
так я посмотрел "Select блокирует в режиме request_mode - S Object таблицу _InfoRg18847 с определенным набором
resource_lock_partition в количестве примерно равном количеству строк в пакете "

прям так там и написано??? примерно равном и не пересекающемся с примерным намерением X на вставке?

Прилагаю то что заблокировал Select

К сообщению приложен файл. Размер - 137Kb
25 янв 17, 13:49    [20144359]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
selis76
Member

Откуда:
Сообщений: 387
А тут что пытается блокировать Insert - две подвисшие сессии

К сообщению приложен файл. Размер - 100Kb
25 янв 17, 13:49    [20144364]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
invm
Member

Откуда: Москва
Сообщений: 9823
selis76
А где это написано?
Что это?
selis76
Я перерыл все по поиску, но нигде не нашел явного упоминания когда блокировка идет по строкам а когда на всю таблицу
Гранулярность блокировок сервер выбирает динамически, в зависимости от...
Либо желаемую гранулярность можно задать хинтом в запросе.
selis76
да и потом какой смысл MS SQl блокировать всю таблицу в режиме Read commited?
Вы ищите некий смысл или причину проблемы?
Еще раз: у вас вставляльщики ждут IX на таблицу. На таблицу, потому что wait_resource OBJECT:*.
IX уровня таблицы конфликтует с S, U или X уровня же таблицы, либо с Sch-M.
25 янв 17, 13:52    [20144382]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
selis76
Member

Откуда:
Сообщений: 387
Там у всех сессий с Insert есть конкретная resource_lock_partition
меня может смущать только в Select нулевая resource_lock_partition на mode S. Я не знаю это означает блокировка всей таблицы или просто есть партиция 0
25 янв 17, 13:54    [20144395]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
invm
Member

Откуда: Москва
Сообщений: 9823
selis76,

И что вас смущает на картинках? Например, select имеет S на секцию 6, insert ждет IX на секцию 6.
25 янв 17, 13:56    [20144403]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
selis76,

автор
меня может смущать только в Select нулевая resource_lock_partition на mode S.
точно 0? у вас общие блокировки на select grant на insert wait
25 янв 17, 13:57    [20144414]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
selis76
Member

Откуда:
Сообщений: 387
invm
selis76
А где это написано?
Что это?
selis76
Я перерыл все по поиску, но нигде не нашел явного упоминания когда блокировка идет по строкам а когда на всю таблицу
Гранулярность блокировок сервер выбирает динамически, в зависимости от...
Либо желаемую гранулярность можно задать хинтом в запросе.
selis76
да и потом какой смысл MS SQl блокировать всю таблицу в режиме Read commited?
Вы ищите некий смысл или причину проблемы?
Еще раз: у вас вставляльщики ждут IX на таблицу. На таблицу, потому что wait_resource OBJECT:*.
IX уровня таблицы конфликтует с S, U или X уровня же таблицы, либо с Sch-M.

Мне непонятно противоречие.
Если утверждается что IX у меня уровня всей таблицы, почему тогда в sys.dm_tran_locks блокировки расписаны для отдельных resource_lock_partition а не для всего Object? Как мне в sys.dm_tran_locks отличить блокировку на всю таблицу и на отдельные resource_lock_partition
25 янв 17, 13:58    [20144417]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
invm
Member

Откуда: Москва
Сообщений: 9823
selis76
Если утверждается что IX у меня уровня всей таблицы
Утверждалось, потому что на вашей первой картинке секции нигде не упоминаются.
25 янв 17, 14:08    [20144491]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
selis76
Member

Откуда:
Сообщений: 387
invm
selis76
Если утверждается что IX у меня уровня всей таблицы
Утверждалось, потому что на вашей первой картинке секции нигде не упоминаются.

Первая картинка
это
SELECT r.session_id, r.[status], r.wait_type, r.scheduler_id, 
    SUBSTRING(qt.[text],r.statement_start_offset/2, 
                (CASE WHEN r.statement_end_offset = -1 
                    THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 
                    ELSE r.statement_end_offset 
                 END - r.statement_start_offset)/2) AS [statement_executing],
        qt.text,         
        DB_NAME(r.database_id) AS [DatabaseName],
        OBJECT_NAME(qt.objectid) AS [ObjectName],
        qt.objectid,
        r.wait_resource,r.wait_time,
        r.cpu_time, r.total_elapsed_time,r.lock_timeout, r.reads, r.writes,
        
        r.blocking_session_id ,es.program_name as 'blocking program ', es.client_interface_name 'blocking interface', es.login_name 'blocking login', 
        CASE es.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncomitted' WHEN 2 THEN 'Readcomitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END AS BLOCKINGTRANSACTION_ISOLATION_LEVEL,
        CASE ces.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncomitted' WHEN 2 THEN 'Readcomitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END AS CURRENTTRANSACTION_ISOLATION_LEVEL,
        r.logical_reads, r.plan_handle
    FROM sys.dm_exec_requests AS r
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
    LEFT JOIN sys.dm_exec_sessions es on r.blocking_session_id=es.session_id
     LEFT JOIN sys.dm_exec_sessions ces on r.session_id=ces.session_id
    WHERE r.session_id > 50
    ORDER BY r.wait_resource desc;
25 янв 17, 14:10    [20144505]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
selis76
Member

Откуда:
Сообщений: 387
Но меня навели на мысль
я отсортировал блокировки sys.dm_tran_locks по таблице и по партициям одной таблицы (прилагаю)
получается что идет перехлест блокировок не на уровне всей таблицы а по отдельным партициям

К сообщению приложен файл. Размер - 137Kb
25 янв 17, 14:12    [20144519]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
invm
Member

Откуда: Москва
Сообщений: 9823
selis76
получается что идет перехлест блокировок не на уровне всей таблицы а по отдельным партициям
20144403
25 янв 17, 14:18    [20144555]     Ответить | Цитировать Сообщить модератору
 Re: Как определить размер заблокированных ресурсов при Select в SQL server 2008  [new]
selis76
Member

Откуда:
Сообщений: 387
invm
selis76
получается что идет перехлест блокировок не на уровне всей таблицы а по отдельным партициям
20144403

Понял спасибо! Не заметил сразу.
что теперь с этим делать? С одной стороны если использовать режим загрузки 1С с проверкой существования записей, я их запрос на проверку изменить не смогу. Могу только использовать режим без проверки где этого запроса нет.
Управлять блокировками на уровни партиций тоже не могу, тут как понимаю SQL сервер сам.
Остается только потюнинговать индексы и воспользоваться советами отсюда https://www.sql.ru/forum/1235615-2/optimizator-sql-ne-ispolzuet-indeks-zadannyy-1s-v8
25 янв 17, 14:30    [20144617]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить