Блокировки, балансировка индекса

Правильно ли я понимаю?:

Балансировки индекса срабатывает на момент вставки (не фоново)

Если при очередной вставке данных срабатывает условие балансировки индекса, то он естественно должен захватить все страницы которые попали в балансировку

Соответственно параллельные вставки блокируются этим процессом

Fill Factor кардинально уменьшает вероятность?
Нужно периодически ручками (Job) приводить его в соответствие?

Рассматриваемый случай: на фоне большого числа мелких однострочных операций запускается (Job) одна операция с большим числом строк.
Serialize чтение большого куска данных не даёт вставить новые строки: Range-S (IO wait) <- IX(N потоков) <- IX(N² потоков)
Если я правильно понял

Mnior

Правильно ли я понимаю?: Балансировки индекса срабатывает на момент вставки (не фоново) Если при очередной вставке данных срабатывает условие балансировки индекса, то он естественно должен захватить все страницы которые попали в балансировку Соответственно параллельные вставки блокируются этим процессом Fill Factor кардинально уменьшает вероятность? Нужно периодически ручками (Job) приводить его в соответствие? Рассматриваемый случай: на фоне большого числа мелких однострочных операций запускается (Job) одна операция с большим числом строк. Serialize чтение большого куска данных не даёт вставить новые строки: Range-S (IO wait) <- IX(N потоков) <- IX(N2 потоков) Если я правильно понял

балансировка - это в смысле reorganize \rebuild

при вставке индексы лучше всего отключать

1d0
балансировка - это в смысле reorganize \rebuild

Нет.
Индексы в РСУБД сбалансированны (см. красно-чёрные деревья, высота дерева, доки по физике индексов).

Mnior
Fill Factor кардинально уменьшает вероятность?

Рассуждать о том, что fillfactor уменьшит количество сплитов страниц при вставке данных (а это и есть, собственно, и вся балансировка при вставке), лучше все-таки зная характер индекса и характер вставляемых данных.

Гавриленко Сергей Алексеевич
характер индекса и характер вставляемых данных.

PK_Table по IDENTUTY(1,1).

Mnior
PK_Table по IDENTUTY(1,1).

Отвечая на вопрос по FF: он тут ничем не поможет, даже наоборот, чем меньше FF, тем больше сплитов. Но. Сплитится будет всегда последняя страница, поэтому влияние сплита на скорость вставки минимальна.

А про блокировки я как-то не понял, если честно, как вы их сюда хотите привязать.

Гавриленко Сергей Алексеевич
А про блокировки я как-то не понял, если честно, как вы их сюда хотите привязать.

Ну я так понимаю, что для операции сплит, KEY блокировка должна расширится до уровня PAGE (нескольких). Нет разве?

Mnior
Гавриленко Сергей Алексеевич А про блокировки я как-то не понял, если честно, как вы их сюда хотите привязать. Ну я так понимаю, что для операции сплит, KEY блокировка должна расширится до уровня PAGE (нескольких). Нет разве?

Я тут пример сделал, чтобы посмотреть, до чего она расширяется...

-- На страницу влезает 8 ключей, заполняем 8 страниц. При добавлении следующей записи появится 9я страница и еще 1 уровень в дереве.
if @@trancount > 0 rollback
go
if exists(select * from information_schema.tables where table_schema = 'dbo' and table_name = 'TestSplit')
	drop table dbo.TestSplit
go
create table dbo.TestSplit(
    [key]   binary (900)    not null
    , primary key (
        [key]
    )
)
go
insert dbo.TestSplit (
    [key]
)
            select 0x00
union all   select 0x01
union all   select 0x02
union all   select 0x03
union all   select 0x04
union all   select 0x05
union all   select 0x06
union all   select 0x07
union all   select 0x08
union all   select 0x09
union all   select 0x10
union all   select 0x11
union all   select 0x12
union all   select 0x13
union all   select 0x14
union all   select 0x15
union all   select 0x16
union all   select 0x17
union all   select 0x18
union all   select 0x19
union all   select 0x20
union all   select 0x21
union all   select 0x22
union all   select 0x23
union all   select 0x24
union all   select 0x25
union all   select 0x26
union all   select 0x27
union all   select 0x28
union all   select 0x29
union all   select 0x30
union all   select 0x31
union all   select 0x32
union all   select 0x33
union all   select 0x34
union all   select 0x35
union all   select 0x36
union all   select 0x37
union all   select 0x38
union all   select 0x39
union all   select 0x40
union all   select 0x41
union all   select 0x42
union all   select 0x43
union all   select 0x44
union all   select 0x45
union all   select 0x46
union all   select 0x47
union all   select 0x48
union all   select 0x49
union all   select 0x50
union all   select 0x51
union all   select 0x52
union all   select 0x53
union all   select 0x54
union all   select 0x55
union all   select 0x56
union all   select 0x57
union all   select 0x58
union all   select 0x59
union all   select 0x60
union all   select 0x61
union all   select 0x62
union all   select 0x63

go

begin tran
insert dbo.TestSplit([key]) values(0x64)
exec sp_lock
select * from sys.dm_db_index_physical_stats(DB_ID(), object_id('dbo.TestSplit'),null,null,'sampled')
--rollback

Mnior
Ну я так понимаю, что для операции сплит, KEY блокировка должна расширится до уровня PAGE (нескольких). Нет разве?

По факту, выходит, что нет. Интент на две страницы, и экслюзивная на вставленный ключ.

З.Ы. В, общем, в мой скрипт вначале надо вставить if @@trancount > 0 rollback, а то при повторном запуске скрипта фигня будет. :)

Гавриленко Сергей Алексеевич, Спасибо!

begin tran
select * from sys.dm_db_index_physical_stats(DB_ID(), object_id('dbo.TestSplit'),null,null,'sampled')
insert dbo.TestSplit([key]) values(0x64)
exec sp_lock
select * from sys.dm_db_index_physical_stats(DB_ID(), object_id('dbo.TestSplit'),null,null,'sampled')
rollback
select * from sys.dm_db_index_physical_stats(DB_ID(), object_id('dbo.TestSplit'),null,null,'sampled')

database_id object_id index_id index_depth page_count record_count avg_record_size_in_bytes 2 523057545 1 2 8 64 907

spid dbid ObjId IndId Type Resource Mode Status 72 2 0 0 DB [ENCRYPTION_SCAN] S GRANT 72 2 523057545 0 TAB IX GRANT 72 2 523057545 1 KEY (64008ccccc0d) X GRANT 72 2 523057545 1 PAG 1:163715 IX GRANT 72 2 523057545 1 PAG 1:163712 IX GRANT

database_id object_id index_id index_depth page_count record_count avg_record_size_in_bytes 2 523057545 1 3 9 65 907

database_id object_id index_id index_depth page_count record_count avg_record_size_in_bytes 2 523057545 1 3 9 64 907

Получается что неправда. Блокировки логические, и физика индекса на это не влияет. Притом хоть дофига параллельно процессов заливают, блокировок нет.

Тогда перед вставкой параллельно запустим:

begin tran
SELECT * FROM dbo.TestSplit WITH(Serializable)
WHERE [key] BETWEEN 0x00 AND 0x63

Тогда будет блокировка, хотя 0x64 не входит в диапазон. Но если заменить в BETWEEN на 0x63, то всё нормально - блокировок нет. Serializable, получается локирует краевых "соседей"(ffffffffffff):

+ sp_lock

spid dbid ObjId IndId Type Resource Mode Status 56 2 1243060110 1 KEY (570017afb7c4) RangeS-S GRANT 56 2 1243060110 1 KEY (12003976b382) RangeS-S GRANT 56 2 1243060110 1 KEY (23004a5bcb25) RangeS-S GRANT 56 2 1243060110 1 PAG 1:405 IS GRANT 56 2 1243060110 1 PAG 1:404 IS GRANT 56 2 1243060110 1 PAG 1:407 IS GRANT 56 2 1243060110 1 PAG 1:406 IS GRANT 56 2 1243060110 1 PAG 1:400 IS GRANT 56 2 1243060110 1 PAG 1:403 IS GRANT 56 2 1243060110 1 PAG 1:499 IS GRANT 56 2 1243060110 1 KEY (420071ee48e9) RangeS-S GRANT 56 2 1243060110 1 KEY (ffffffffffff) RangeS-S GRANT 56 2 1243060110 1 PAG 1:163899 IS GRANT 56 2 1243060110 1 PAG 1:163896 IS GRANT 56 2 1243060110 1 KEY (45003d9a4c6c) RangeS-S GRANT 56 2 1243060110 1 KEY (00001343482a) RangeS-S GRANT 56 2 1243060110 1 KEY (5900ce41ce15) RangeS-S GRANT 56 2 1243060110 1 KEY (3100606e308d) RangeS-S GRANT 56 2 1243060110 1 KEY (3200fc8732d4) RangeS-S GRANT 56 2 1243060110 1 KEY (4600a1734e35) RangeS-S GRANT 56 2 1243060110 1 KEY (03008faa4a73) RangeS-S GRANT 56 2 1243060110 1 KEY (27009ac6cdf9) RangeS-S GRANT 56 2 1243060110 1 KEY (6200b41fc9bf) RangeS-S GRANT 56 2 1243060110 1 KEY (5300c732b118) RangeS-S GRANT 56 2 1243060110 1 KEY (1600e9ebb55e) RangeS-S GRANT 56 2 1243060110 1 KEY (3500b0f33651) RangeS-S GRANT 56 2 1243060110 1 KEY (18003005cc8f) RangeS-S GRANT 56 2 1243060110 1 KEY (4100ed074ab0) RangeS-S GRANT 56 2 1243060110 1 KEY (29004328b428) RangeS-S GRANT 56 2 1243060110 1 KEY (0400c3de4ef6) RangeS-S GRANT 56 2 1243060110 1 KEY (4800789d37e4) RangeS-S GRANT 56 2 1243060110 1 KEY (2000d6b2c97c) RangeS-S GRANT 56 2 1243060110 1 KEY (54008b46b59d) RangeS-S GRANT 56 2 1243060110 1 KEY (1100a59fb1db) RangeS-S GRANT 56 2 1243060110 1 KEY (6300c0b8c888) RangeS-S GRANT 56 2 1243060110 1 KEY (2600ee61ccce) RangeS-S GRANT 56 2 1243060110 1 KEY (17009d4cb469) RangeS-S GRANT 56 2 1243060110 1 KEY (5200b395b02f) RangeS-S GRANT 56 2 1243060110 1 KEY (07005f374caf) RangeS-S GRANT 56 2 1243060110 1 KEY (36002c1a3408) RangeS-S GRANT 56 2 1243060110 1 KEY (50005bdbb341) RangeS-S GRANT 56 2 1243060110 1 KEY (3800f5f44dd9) RangeS-S GRANT 56 2 1243060110 1 KEY (15007502b707) RangeS-S GRANT 56 2 1243060110 1 KEY (2400062fcfa0) RangeS-S GRANT 56 2 1243060110 1 KEY (090086d9357e) RangeS-S GRANT 56 2 1243060110 1 KEY (610028f6cbe6) RangeS-S GRANT 56 2 1243060110 1 KEY (3300882033e3) RangeS-S GRANT 56 2 1243060110 1 KEY (0200fb0d4b44) RangeS-S GRANT 56 2 1243060110 1 KEY (4700d5d44f02) RangeS-S GRANT 56 2 1243060110 1 KEY (49000c3a36d3) RangeS-S GRANT 56 2 1243060110 1 KEY (2100a215c84b) RangeS-S GRANT 56 2 1243060110 1 KEY (1000d138b0ec) RangeS-S GRANT 56 2 1243060110 1 KEY (5500ffe1b4aa) RangeS-S GRANT 56 2 1243060110 1 KEY (190044a2cdb8) RangeS-S GRANT 56 2 1243060110 1 KEY (3400c4543766) RangeS-S GRANT 56 2 1243060110 1 KEY (0500b7794fc1) RangeS-S GRANT 56 2 1243060110 1 KEY (400099a04b87) RangeS-S GRANT 56 2 1243060110 1 KEY (2800378fb51f) RangeS-S GRANT 56 2 1243060110 1 KEY (06002b904d98) RangeS-S GRANT 56 2 1243060110 0 TAB IS GRANT 56 2 1243060110 1 KEY (4300054949de) RangeS-S GRANT 56 2 1243060110 1 KEY (370058bd353f) RangeS-S GRANT 56 2 1243060110 1 KEY (13004dd1b2b5) RangeS-S GRANT 56 2 1243060110 1 KEY (56006308b6f3) RangeS-S GRANT 56 2 1243060110 1 KEY (22003efcca12) RangeS-S GRANT 56 2 1243060110 1 KEY (010067e4491d) RangeS-S GRANT 56 2 1243060110 1 KEY (4400493d4d5b) RangeS-S GRANT 56 2 1243060110 1 KEY (5800bae6cf22) RangeS-S GRANT 56 2 1243060110 1 KEY (300014c931ba) RangeS-S GRANT 56 2 1243060110 1 KEY (140001a5b630) RangeS-S GRANT 56 2 1243060110 1 KEY (51002f7cb276) RangeS-S GRANT 56 2 1243060110 1 KEY (390081534cee) RangeS-S GRANT 56 2 1243060110 1 KEY (0800f27e3449) RangeS-S GRANT 56 2 1243060110 1 KEY (60005c51cad1) RangeS-S GRANT 56 2 1243060110 1 KEY (25007288ce97) RangeS-S GRANT 60 2 0 0 DB [ENCRYPTION_SCAN] S GRANT 72 2 1243060110 1 KEY (ffffffffffff) RangeIn- WAIT 72 2 1243060110 0 TAB IX GRANT 72 2 1243060110 1 PAG 1:163899 IX GRANT

Ok. Буду копать дальше по своей ситуации (добыть больше инфы по блокировкам).