if object_id( 'Testing123' ) is not null
drop table dbo.Testing123;
go
create table Testing123 ( ordinal int identity(1,1) primary key clustered, data varchar(max) )
go
insert into Testing123 ( data )
select o1.name + ' - ' + o2.name
from sys.objects o1
cross join sys.objects o2
go
--Now we'll look at the count of rows, and the pages that belong to the table.
select count(*) from dbo.Testing123;
go
dbcc ind('Test', 'Testing123', -1);
go
--We get a count of 504 rows and the following pages:
/*
(3025 row(s) affected)
-----------
3025
(1 row(s) affected)
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1 154 NULL NULL 21575115 1 1 72057594038845440 In-row data 10 NULL 0 0 0 0
1 153 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 156 0 0
1 155 1 154 21575115 1 1 72057594038845440 In-row data 2 1 0 0 0 0
1 156 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 157 1 153
1 157 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 158 1 156
1 158 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 159 1 157
1 159 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 168 1 158
1 168 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 169 1 159
1 169 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 176 1 168
1 176 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 177 1 169
1 177 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 178 1 176
1 178 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 179 1 177
1 179 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 180 1 178
1 180 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 181 1 179
1 181 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 182 1 180
1 182 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 183 1 181
1 183 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 184 1 182
1 184 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 185 1 183
1 185 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 186 1 184
1 186 1 154 21575115 1 1 72057594038845440 In-row data 1 0 0 0 1 185
(20 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/
--Next, start a transaction and truncate the table. Verify that DBCC IND shows no pages belonging to the table and the count is 0. Then, look at the locks.
begin tran
truncate table dbo.Testing123;
insert into Testing123 ( data )
select o1.name
from sys.objects o1
select count(*) from dbo.Testing123;
dbcc ind('Test', 'Testing123', -1);
dbcc extentinfo('Test', 'Testing123', -1);
select resource_type, resource_description, request_mode
from sys.dm_tran_locks
where resource_type in ('EXTENT', 'PAGE')
and resource_database_id = db_id('Test');
--You should see 2 rows from DBCC IND, and 55 rows from count(*). But the locks info should return something like:
/*
(55 row(s) affected)
-----------
55
(1 row(s) affected)
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1 155 NULL NULL 149575571 1 1 72057594038976512 In-row data 10 NULL 0 0 0 0
1 154 1 155 149575571 1 1 72057594038976512 In-row data 1 0 0 0 0 0
(2 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
file_id page_id pg_alloc ext_size object_id index_id partition_number partition_id iam_chain_type pfs_bytes
----------- ----------- ----------- ----------- ----------- ----------- ---------------- -------------------- -------------------- ------------------
1 154 1 1 149575571 1 1 72057594038976512 In-row data 0x6000000000000000
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
resource_type resource_description request_mode
------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------
PAGE 1:155 X
PAGE 1:153 X
PAGE 1:182 X
PAGE 1:183 X
PAGE 1:180 X
PAGE 1:181 X
PAGE 1:178 X
PAGE 1:179 X
PAGE 1:176 X
PAGE 1:177 X
EXTENT 1:176 X
EXTENT 1:216 X
EXTENT 1:208 X
(13 row(s) affected)
*/
--So the extent and page locks include all the pages that we saw in the DBCC IND output. (Remember, an extent is 8 contiguous pages, starting at the one that describes the extent.)
--Only after you ROLLBACK the transaction will the locks be released, and you should see all the rows and pages back in the table again.
rollback tran;
go
select count(*) from dbo.Testing123;
dbcc ind('Test', 'Testing123', -1);
go
/*
-----------
3025
(1 row(s) affected)
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1 154 NULL NULL 21575115 1 1 72057594038845440 In-row data 10 NULL 0 0 0 0
1 153 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 156 0 0
1 155 1 154 21575115 1 1 72057594038845440 In-row data 2 1 0 0 0 0
1 156 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 157 1 153
1 157 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 158 1 156
1 158 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 159 1 157
1 159 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 168 1 158
1 168 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 169 1 159
1 169 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 176 1 168
1 176 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 177 1 169
1 177 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 178 1 176
1 178 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 179 1 177
1 179 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 180 1 178
1 180 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 181 1 179
1 181 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 182 1 180
1 182 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 183 1 181
1 183 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 184 1 182
1 184 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 185 1 183
1 185 1 154 21575115 1 1 72057594038845440 In-row data 1 0 1 186 1 184
1 186 1 154 21575115 1 1 72057594038845440 In-row data 1 0 0 0 1 185
(20 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/
This entry was posted
on Friday, November 05, 2010
and is filed under
SQL Server,
Tools
.
You can leave a response
and follow any responses to this entry through the
Subscribe to:
Post Comments (Atom)
.
?l'ta?m
0 comments