看这篇文章之前可以先看一下鬼影记录
SQLSERVER的鬼影记录
SQLSERVER的鬼影记录翻译一
SQLSERVER的鬼影记录翻译二
当删除表中的某一条记录的时候，索引页面的对应记录并不是马上删除，而是标记为鬼影，当提交事务的时候才真正删除索引记录，
或者回滚事务，鬼影索引记录才会恢复为正常索引记录，这样做的目的就是提高了性能
鬼影索引只会出现在非聚集索引页，聚集索引页是没有鬼影索引的
建立环境
1 --ghost index record
2 USE [pratice]
3 GO
4 --建表
5 CREATE TABLE testghostindexnoncluster(id INT IDENTITY(1,1),NAME VARCHAR(20))
6 GO
7 --插入记录
8 INSERT INTO testghostindexnoncluster(name)
9 SELECT '1' UNION ALL
10 SELECT '2'
11 GO
12
13 CREATE INDEX IX_testghostindexnoncluster ON testghostindexnoncluster([Id] ASC)
14
15 SELECT * FROM [dbo].[testghostindexnoncluster]
16 GO
1 --TRUNCATE TABLE DBCCResult
2 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,testghostindexnoncluster,-1) ')
3
4 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC
5
6 DBCC TRACEON(3604,-1)
7 GO
8 DBCC PAGE([pratice],1,15663,1) --索引页
9 GO正常情况下的索引页面,Record Type = INDEX_RECORD
1 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
2
3 PAGE: (1:15663)
4
5
6 BUFFER:
7
8
9 BUF @0x03E53304
10
11 bpage = 0x1A468000 bhash = 0x00000000 bpageno = (1:15663)
12 bdbid = 5 breferences = 0 bUse1 = 7847
13 bstat = 0xc0000b blog = 0x1212121b bnext = 0x00000000
14
15 PAGE HEADER:
16
17
18 Page @0x1A468000
19
20 m_pageId = (1:15663) m_headerVersion = 1 m_type = 2
21 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x4000
22 m_objId (AllocUnitId.idObj) = 539 m_indexId (AllocUnitId.idInd) = 256
23 Metadata: AllocUnitId = 72057594073251840
24 Metadata: PartitionId = 72057594061062144 Metadata: IndexId = 2
25 Metadata: ObjectId = 1463676262 m_prevPage = (0:0) m_nextPage = (0:0)
26 pminlen = 13 m_slotCnt = 2 m_freeCnt = 8066
27 m_freeData = 135 m_reservedCnt = 0 m_lsn = (3046:350:12)
28 m_xactReserved = 0 m_xdesId = (0:11665608) m_ghostRecCnt = 0
29 m_tornBits = 0
30
31 Allocation Status
32
33 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
34 PFS (1:8088) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
35 ML (1:7) = NOT MIN_LOGGED
36
37 DATA:
38
39
40 Slot 0, Offset 0x7a, Length 13, DumpStyle BYTE
41
42 Record Type = INDEX_RECORD Record Attributes =
43 Memory Dump @0x0849C07A
44
45 00000000: 06010000 00b52000 00010000 00††††††††...... ......
46
47 Slot 1, Offset 0x6d, Length 13, DumpStyle BYTE
48
49 Record Type = INDEX_RECORD Record Attributes =
50 Memory Dump @0x0849C06D
51
52 00000000: 06020000 00b52000 00010001 00††††††††...... ......
53
54 OFFSET TABLE:
55
56 Row - Offset
57 1 (0x1) - 109 (0x6d)
58 0 (0x0) - 122 (0x7a)
59
60
61 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
我们删除id=1的记录
1 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
2 GO
3 BEGIN TRAN
4 DELETE FROM testghostindexnoncluster WHERE [id]=1
5 --ROLLBACK TRAN再看一下索引页面
1 DBCC TRACEON(3604,-1)
2 GO
3 DBCC PAGE([pratice],1,15663,1) --索引页
4 GO 
1 DATA:
2
3
4 Slot 0, Offset 0x7a, Length 13, DumpStyle BYTE
5
6 Record Type = GHOST_INDEX_RECORD Record Attributes =
7 Memory Dump @0x0849C07A
8
9 00000000: 0a010000 00b52000 00010000 00††††††††...... ......
10
11 Slot 1, Offset 0x6d, Length 13, DumpStyle BYTE
12
13 Record Type = INDEX_RECORD Record Attributes =
14 Memory Dump @0x0849C06D
15
16 00000000: 06020000 00b52000 00010001 00††††††††...... ......
17
18 OFFSET TABLE:
19
20 Row - Offset
21 1 (0x1) - 109 (0x6d)
22 0 (0x0) - 122 (0x7a)
23
24
25 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
可以看到第一行记录被标记为Record Type = GHOST_INDEX_RECORD
我们回滚事务
1 ROLLBACK TRAN恢复正常了，索引页面中第一行记录的Record Type = INDEX_RECORD
1 DATA:
2
3
4 Slot 0, Offset 0x94, Length 13, DumpStyle BYTE
5
6 Record Type = INDEX_RECORD Record Attributes =
7 Memory Dump @0x0849C094
8
9 00000000: 06010000 00b52000 00010000 00††††††††...... ......
10
11 Slot 1, Offset 0x6d, Length 13, DumpStyle BYTE
12
13 Record Type = INDEX_RECORD Record Attributes =
14 Memory Dump @0x0849C06D
15
16 00000000: 06020000 00b52000 00010001 00††††††††...... ......
17
18 OFFSET TABLE:
19
20 Row - Offset
21 1 (0x1) - 109 (0x6d)
22 0 (0x0) - 148 (0x94)
23
24
25 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
1 DROP TABLE testghostindexnoncluster
2 GO
如有不对的地方，欢迎大家拍砖o(∩_∩)o
本站文章版权归原作者及原出处所有 。内容为作者个人观点， 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台，并不用于任何商业目的，如果有任何问题，请及时联系我们，我们将根据著作权人的要求，立即更正或者删除有关内容。本站拥有对此声明的最终解释权。