· 7 years ago · Oct 10, 2018, 07:56 PM
1use tempdb
2go
3drop table if exists ps_test
4set nocount on
5
6go
7
8--8,060=4*(4+2011)
9create table ps_test(id int primary key, data char(2011) not null default '')
10
11go
12
13
14
15insert into ps_test with (paglock) (id) values (1),(2),(3),(1004),(1005),(1006),(1007),(1008),(1009),(1010),(1011),(1012),(1013),(1014),(1015),(1016)--,(1017)
16
17go
18 select page_count leaf_page_count
19 from sys.dm_db_index_physical_stats(db_id(),object_id('ps_test'),null,null,'detailed')
20 where index_level = 0
21 and index_id = 1
22
23
24go
25--return
26declare @p table(key_inserted int , page_locked varchar(20))
27
28declare @id int = 4
29while (@id < 100)
30begin
31begin tran
32 insert into ps_test with (paglock) (id)
33 values (@id);
34
35
36 insert into @p(key_inserted,page_locked)
37 select @id key_inserted, resource_description page_locked
38 from sys.dm_tran_locks
39 where request_session_id = @@spid
40 and resource_type = 'PAGE';
41
42 set @id += 1;
43commit
44end
45
46
47select *, count(*) over (partition by key_inserted) pages_locked
48from @p
49order by key_inserted
50
51leaf_page_count
52--------------------
534
54
55key_inserted page_locked pages_locked
56------------ -------------------- ------------
574 3:29 2
584 3:24 2
595 3:29 1
606 3:30 2
616 3:29 2
627 3:31 2
637 3:29 2
648 3:31 1
659 3:31 1
6610 3:31 1
6711 3:31 2
6811 9:40 2
6912 9:40 1
7013 9:40 1
7114 9:40 1
7215 9:40 2
7315 9:41 2
7416 9:41 1
7517 9:41 1
7618 9:41 1
7719 9:42 2
7819 9:41 2
7920 9:42 1
8021 9:42 1
8122 9:42 1
8223 9:42 2
8323 9:43 2
8424 9:43 1
8525 9:43 1
8626 9:43 1
8727 9:44 2
8827 9:43 2
8928 9:44 1
9029 9:44 1
9130 9:44 1
92. . .