ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как заставить сервер блокировать только выбранные секции?
18 сообщений из 18, страница 1 из 1
Как заставить сервер блокировать только выбранные секции?
    #40001250
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Уважаемые коллеги, приветствую!

Есть две таблицы, совершенно аналогичных по структуре, за исключением индексов и статистик.
Обе - секционированные.
Пытаюсь выполнить:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
INSERT INTO [load].[USL]
WITH (PAGLOCK)
SELECT *
FROM [act].[USL] a WITH (NOLOCK)
INNER JOIN #act b ON a.regstamp = b.regstamp
	AND a.regstamp BETWEEN 37000000
		AND 37009999
	AND a.ID_ACT = b.ID_ACT
OPTION (
	RECOMPILE
	,QUERYTRACEON 8649
	,HASH JOIN
	)


В процессе вставки load.USL лочится целиком, не смотря на то, что для нее стоит LOCK_ESCALATION = AUTO, да и подсказку PAGLOCK (от безысходности) уже используем.
Секционирование ведется по полю regstamp, секций в такой insert попадает десятка 3, из 4000 возможных.
Но лок получается на всю таблицу.
В таблице load.ACT - нет ни индексов, ни статистик. Только кластерный индекс (не primary key!) по ID_ACT, regstamp.

Почему так? Хочется, чтобы он лочил только те секции, которые указаны в диапазоне.
В темповой таблице regstamp находится в том же диапазоне.
...
Рейтинг: 0 / 0
22.09.2020, 12:45
| Ответить | Цитировать | Написать  
Как заставить сервер блокировать только выбранные секции?
    #40001265
Агрох
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрох
Участник
Когда то давно и очень вскользь касался темы секционирования, потому просто предположение: т.к. у тебя секционирование по одному полю, а индекс и фильтрация по двум, то планировщик не может понять, что ID_ACT-ы надо искать только в тех секциях, которые фильтруются через regstamp BETWEEN 37000000 AND 37009999 и он пытается найти их в прочих секциях.
Попробуй отрубить индекс для теста и посмотри что будет или замени на индекс только по regstamp.
...
Рейтинг: 0 / 0
22.09.2020, 13:10
| Ответить | Цитировать | Написать  
Как заставить сервер блокировать только выбранные секции?
    #40001269
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
uaggster,

план запроса приложить можете?
...
Рейтинг: 0 / 0
22.09.2020, 13:16
| Ответить | Цитировать | Написать  
Как заставить сервер блокировать только выбранные секции?
    #40001444
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
uaggster
Но лок получается на всю таблицу.
А есть такой уровень грануляности - секция?
...
Рейтинг: 0 / 0
22.09.2020, 18:35
| Ответить | Цитировать | Написать  
Как заставить сервер блокировать только выбранные секции?
    #40001450
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
alexeyvg,

ну это не совсем "уровень гранулярности" но в целом да.

если таблица секционирована то там возможно повышение до HoBT что можно считать увеличением до уровня секции
...
Рейтинг: 0 / 0
22.09.2020, 19:14
| Ответить | Цитировать | Написать  
Как заставить сервер блокировать только выбранные секции?
    #40001805
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
felix_ff
uaggster,

план запроса приложить можете?

Вот так примерно.
...
Рейтинг: 0 / 0
23.09.2020, 18:12
| Ответить | Цитировать | Написать  
Как заставить сервер блокировать только выбранные секции?
    #40001813
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
uaggster,

план надо выкладывать в формате .sqlplan
здесь интересна не сама картинка плана а предположения оптимизатора какие он секции выбрал для исключения и кардинальность таблицы.

у вас скорее всего срабатывает укрупнение блокировки по алгоритму достижения граничной планки кол-ва блокировок на объект для одной инструкции.

посмотреть это кстати можно настроив xevent-сессиию на событие lock_escalation

ну и сразу в догонку по мимо плана приложите еще результат:
Код: sql
1.
select * from sys.partitions where [object_id] = object_id('load.USL')
...
Рейтинг: 0 / 0
23.09.2020, 18:19
| Ответить | Цитировать | Написать  
Как заставить сервер блокировать только выбранные секции?
    #40001852
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
felix_ff , а если это так (не могу пока проверить), то что с этим можно поделать?
Т.е., как оптимизатору сообщить, в каких секциях расположены данные? Точнее - будут располагаться данные?
Одновременно вставляются данные не более, чем 30 секций из имеющихся 4000, обычно - 3-5.
Обе таблицы построены на одной и той же функции секционирования.
И, как мне кажется, я сообщаю оптимизатору, какие секции я собираюсь затронуть. Я же ограничиваю то поле, по которому производится секционирование в запросе явным образом!
...
Рейтинг: 0 / 0
23.09.2020, 19:04
| Ответить | Цитировать | Написать  
Как заставить сервер блокировать только выбранные секции?
    #40001903
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
uaggster,

хм... ну посмотрите сначала на
Код: sql
1.
select [lock_escalation] from sys.tables where [name] = 'USL' and [schema_id] = schema_id('load');



должно быть 2, если так то снимайте трассу/x-cобытие на lock_escalation и выкладываете сюда.
...
Рейтинг: 0 / 0
23.09.2020, 22:08
| Ответить | Цитировать | Написать  
Как заставить сервер блокировать только выбранные секции?
    #40001906
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
uaggster
Я же ограничиваю то поле, по которому производится секционирование в запросе явным образом!
А как вы это делаете?
...
Рейтинг: 0 / 0
23.09.2020, 23:00
| Ответить | Цитировать | Написать  
Как заставить сервер блокировать только выбранные секции?
    #40001908
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
alexeyvg
uaggster
Я же ограничиваю то поле, по которому производится секционирование в запросе явным образом!
А как вы это делаете?


ну предикат у него там на диапазон
Код: sql
1.
AND a.regstamp BETWEEN 37000000 AND 37009999



а схема секционирования сопоставлена на колонку regstamp.

Раз ТС божится что лочится вся таблица целиком а не HoBT, ставлю на то что у ТС на таблице окажется уровень lock_escalation = TABLE (ибо оно используется по умолчанию, если вручную не меняли)
...
Рейтинг: 0 / 0
23.09.2020, 23:32
| Ответить | Цитировать | Написать  
Как заставить сервер блокировать только выбранные секции?
    #40001935
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
felix_ff
alexeyvg
пропущено...
А как вы это делаете?


ну предикат у него там на диапазон
Код: sql
1.
AND a.regstamp BETWEEN 37000000 AND 37009999



а схема секционирования сопоставлена на колонку regstamp.

Раз ТС божится что лочится вся таблица целиком а не HoBT, ставлю на то что у ТС на таблице окажется уровень lock_escalation = TABLE (ибо оно используется по умолчанию, если вручную не меняли)

Да нет же!
По обеим схемам у таблиц LOCK_ESCALATION = AUTO.
Сейчас перепроверил, именно так.
...
Рейтинг: 0 / 0
24.09.2020, 05:52
| Ответить | Цитировать | Написать  
Как заставить сервер блокировать только выбранные секции?
    #40002055
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
felix_ff
ну предикат у него там на диапазон
Код: sql
1.
AND a.regstamp BETWEEN 37000000 AND 37009999

Это же на другую таблицу предикат

Запрос в топике демонстрационный, такой не может выполниться без ошибки.
Нужно что то более приближенное к реальности.
...
Рейтинг: 0 / 0
24.09.2020, 12:07
| Ответить | Цитировать | Написать  
Как заставить сервер блокировать только выбранные секции?
    #40002067
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
alexeyvg,

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

но по сути даже, так у него при lock_escalation=auto на таблице load.USL должны лочится hobt - экслюзивно а сама таблица на IX


uaggster,
попробуйте убрать with(nolock) с таблицы act.USL
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
INSERT INTO [load].[USL]
SELECT *
FROM [act].[USL] a
INNER JOIN #act b ON a.regstamp = b.regstamp
	AND a.regstamp BETWEEN 37000000
		AND 37009999
	AND a.ID_ACT = b.ID_ACT
OPTION (
	RECOMPILE
	,QUERYTRACEON 8649
	,HASH JOIN
	)
...
Рейтинг: 0 / 0
24.09.2020, 12:20
| Ответить | Цитировать | Написать  
Как заставить сервер блокировать только выбранные секции?
    #40002109
Фотография Критик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Критик
Участник
uaggster,

У меня все работает, см картинку. У вас какая версия?
...
Рейтинг: 0 / 0
24.09.2020, 13:18
| Ответить | Цитировать | Написать  
Как заставить сервер блокировать только выбранные секции?
    #40002391
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Вот такой:
Microsoft SQL Server 2019 (RTM-CU7) (KB4570012) - 15.0.4063.15 (X64) Aug 15 2020 10:48:11 Copyright (C) 2019 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)
Про CU7 уже прочитал.

Вопрос: А если в темповой таблице создать констрейнт по regstamp - это может помочь?
...
Рейтинг: 0 / 0
25.09.2020, 07:09
| Ответить | Цитировать | Написать  
Как заставить сервер блокировать только выбранные секции?
    #40002399
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
uaggster
Вопрос: А если в темповой таблице создать констрейнт по regstamp - это может помочь?
У Критик нет констрейна на темповой таблице, и всё работает.

Либо ваш запрос/модель данных запрещают лок секций, либо сервер считает ,что для такого количество данных/секций блокировка таблиц эффективнее.
...
Рейтинг: 0 / 0
25.09.2020, 07:50
| Ответить | Цитировать | Написать  
Как заставить сервер блокировать только выбранные секции?
    #40002459
Фотография Критик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Критик
Участник
uaggster,

я бы попробовал постепенно убирать из запроса "лишнее":
- избавится от подсказок, глянуть, как будет работать
- слить данные во времянку, а потом вставлять в целевую таблицу
- попробовать поиграться с объемом вставляемых данных, чтобы найти границу, после которой изменяется модель блокирования
...
Рейтинг: 0 / 0
25.09.2020, 11:32
| Ответить | Цитировать | Написать  
18 сообщений из 18, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как заставить сервер блокировать только выбранные секции?
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&gotonew=1&tid=1685606]:
 0ms
get settings:
 12ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 43ms
get topic data:
 14ms
get first new msg:
 8ms
get forum data:
 3ms
get page messages:
 65ms
get tp. blocked users:
 2ms
others: 262ms
total:  429ms
созд. / загр.: 429ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]