MDF? LDF ???

1
MDF? LDF ???
DaumKakao ??? ??? ?? ??? 2015? 1? 22?
2
? ??? ????? ??? ??

• SQL Server ?? ?? ??
• ??
• SQL Server? ??? ?? ???
• SQL Server ?? DBA
• Level 200

3
Speaker

• ???(Eastluck.Kang)
• DaumKakao ??? ??? ?? DBA(SQL Server, MySQL)
• (?) ?????(EA Seoul Studio) FIFA Online 3(MongoDB)
  DBA
• (?) J2M Soft Raycity, Debut(SQL Server) DBA
• ???? ??(????)
• SQL Server MVP 2012 2015
• SQLer.com
• MySQL Power Group(http//cafe.naver.com/mysqlpg)
• Facebook MongoDB Korea(https//www.facebook.com/gr
  oups/krmug)
• Email eastluck.kang_at_daumkakao.com
• Blog http//eastluck.tistory.com

4

• ??
• - SQL Server Storage
• Datafile(MDF), Extent, Page
• Extent ??
• IAM, Table Or Index Scan ??
• - Balanced Tree vs Fractal Tree
• - LDF, SQL Server Backup, BCM, DCM
• - Query Internal
• CheckPoint, Lazy Writer

5
???
6
???? ?? ???(Unplugged 4th Deadlock)

• 1?? Query? ??? ????!(????, ???? ?? ????)
• 2?? ???? ??? B-Tree(Balanced Tree)? ????
• 3?? ??? ? ??? ??? ????(DBCC IND, PAGE,
  EXTENTINFO ?)
• 4?? 1??? ????? ???? ? ???? ????.
• 5?? Lock? ???? ??, ???? ?? ?? ?? ??? ?? ????.
• 6?? SQL Server ?? ?? ??? Deep Dive!
• 7?? ??? ????

7
SQL Server Storage
8
SQL Server Storage ??
Instance
Database
master / tempdb / msdb / model / user databases
FileGroup
PRIMARY FILEGROUP / ??(LDF) ??? ??
Datafile
MDF, NDF / LDF
Extent
???? 8? Page? ??(?? ?? ??)
Page
8kb
9
SQL Server Datafile(MDF/NDF)
Page 0 File Header
Page 1 PFS
Page 2 GAM
Page 3 SGAM
Page 4 NonUsed
Page 5 NonUsed
Page 6 BCM
Page 7 DCM
PFS Page Free Space GAM Global Allocation
Map SGAM Shard Allocation Map BCM Bulked Change
Map DCM Differential Change Map
8088 Pages ??
511,230 Pages ??
10
Extent

• SQL Server ?? ?? ?? ??
• ???? 8?? ??? ??
• Mixed Extent(??? ?? 8?) ? Uniform Extent ??
• GAM ? SGAM? Mixed or Uniform Extent ??
• ?) Extent 0 0 7 Page
• Extent 1 8 15 Page
• Extent 2 16 23 Page

Page 72
Page 73
Page 74
Page 75
Page 76
Page 77
Page 78
Page 79
11
Extent(Mixed Uniform Extent)
Extent ?? ? ????
12
Extent ??
DBCC EXTENTINFO(DBName, TableName)
ext_size 1(Mixed Extent), 8(Uniform
Extent) pg_alloc ????? ??? ??? ?
13
Extent ??
8? ??(??? ??) 0000 0011 1,2? Extent ?? ? 1000
1011 1,2,4,8 Extent ?? ?
Page 0
Page 7
Extent 0

1 byte? 8?? Extent? ?? ??!
Page 8
Page 15
Extent 1

1 Page gt 8000 bytes 8000 8 64,000 Extents ??
??! 64,000 8 512,000 Pages 512,000 8
4,096,000 KB(4 GB) ???? 511,230?? ??
Page 16
Page 23
Extent 2

14
Page
Header
96 Bytes
- Header Page? ???? - Data ??? Row? ?? -
Offset Row? ???? ??? ??
Row 0
Data
Row 1
Row 2
??! 8KB ??? 2KB ?? 16KB? ???? ? ?? ????? ??? ????
8060 Bytes
36 Bytes
Offset
0
1
2
15
Row
Header
Row 0
Data
Row 1
Status
?
????
?
????
Row 2
? ?? - ???? ??? 4 ? ?? - ? ?? ? -
NULL ??? - ????? ? - ?? ??? ??? ??
???? ?? ??? ????? Page ?? ??? ??? ?????
2
Offset
0
1
16
2? ?? ???? ?? ???? ??
1) 10??? 2??? ?? 2) 0gt1, 1gt0?? ??(1? ??) 3) ???
1(0, -0? ???? ??) ?) -8? ???? ?? 8? 2??? ??
gt 0000 0000 0000 1000(0? 1?, 1? 0??) gt 1111
1111 1111 0111(??? 1) gt 1111 1111 1111
1000(16??? ??) gt F F F 8
FFF8(Big Endian) IBM ?? F8FF(Little Endian)
Intel ??
17
????? ???? ?? - 2? ??
?) -8 2 -6 -8? 2? ?? 1111 1111 1111 1000
lt- -8 0000 0000 0000 0010 lt- 2
------------------------- 1111 1111 1111
1010 0000 0000 0000 0110(6? 2??? ??) 1111 1111
1111 1001(0gt1, 1gt0) 1111 1111 1111 1010(??? 1)
18
PFS(Page Free Space)

• 1 byte? ??? ???? ?? ?? ??
• 1? PFS? 8088?? Page(64 MB)? ??? ? ??.
• bits 0-2
• 0x00 is empty
• 0x01 is 1 to 50 full
• 0x02 is 51 to 80 full
• 0x03 is 81 to 95 full
• 0x04 is 96 to 100 full
• bit 3 (0x08) is there one or more ghost records
  on the page?
• bit 4 (0x10) is the page an IAM page?
• bit 5 (0x20) is the page a mixed-page?
• bit 6 (0x40) is the page allocated?

19
PFS(Page Free Space)
Header
0
1
4
2
3
Data
5
6
7
8
9
10
11
12
13
14

8087
8086
Offset
20
Page(Row Migration)
Header
Row 0
Row 0
Row 3
Ghost Record
Data
Row 1
Row 2
Row 0
Row 0
Row 3
Offset
0
1
2
21
Page Split
Header
Header
Row 0
Data
Row 250
Data
Row 1
Row 251
Row 2
Row 252
Row 2-1


Row 2-1
Double Linked List
Row 499

Row 249
Ghost Records
Row 499
Offset
Offset
22
Page Split
Page 72
Page 73
Page 500
??? ??? ??
23
GAM SGAM IAM
24
GAM, SGAM
GAM(Global Allocation Map) - bit 1 ?? ???
Extent(empty) - bit 0 ?? ??? Extent SGAM(Share
d Global Allocation Map) - bit 1 1? ??? ????
??? ? ?? Mixed Extent - bit 0 Uniform Extent or
Mixed Extent? ?? ?? ???? ????
25
GAM, SGAM
GAM SGAM
Free 1 0
Uniform or Mixed Extent 0 0
Mixed Extent with free pages 0 1
26
Mixed Extent ?? ???

• tempdb? Mixed Extent? Page ?? ??? ??? ? ??.
• Uniform Extent? ??
• - DBCC TRACEON(-1, 1118)
• Sqlserver ?? ?? T1118
• ??? ???? ????? SGAM? ?? ????
• ?) GAM, SGAM, BCM ?

27
IAM(Index Allocation Map)

• ??? ?? ???? ??? Page? ??
• 8 Mixed Pages Uniform Extents
• 4GB? ??? ??(511,230 Page)
• ?) 1? Clustered Index, 2? NonClustered Index
• IAM gt 3?

28
IAM(Index Allocation Map)
IAM
Page
Page
Page
Page
Page
Page
Page
Mixed Extent
Uniform Extent
Uniform Extent
Uniform Extent
Data or Index
29
IAM ????
DBCC IND(DBName, TableName, Option) - Option
-2 All IAM -1 All Pages 0 Heap
1?? sys.indexes(index_id)
30
Table or Index Scan ??(Index Ordered Scan)

• Index Ordered Scan
• Btree? ??? Scan ?? ??
• Allocation Ordered Scan
• IAM? ???? Mixed Extent -gt Uniform Extent ??? Scan
  ?? ??
• ??
• 65 Pages ??
• ReadOnly or READ UNCOMMITTED
• ???? ordered ??? false

31
Table or Index Scan ??(Allocation Ordered Scan)
?? ??? ??? ? ??.
IAM
Page
Page
Page
Page
Page
Page
Page
?
Extent
Extent
Extent
?
Data or Index
32
Table or Index Scan ??(Index Ordered Scan)
Page
Page
Page
?
Page
Page
Page
Page
Page
Page


?
33
Balanced Tree Fractal Tree
34
Balanced tree vs Fractal Tree

• Balanced Tree(btree)
• ?? ?? ??? ?? ??? ??
• DML? ?? Random I/O ???? ??
• Table Fragmentation ??
• Fractal Tree
• Btree? ??? ??
• ??? Insert/Delete Node? ?? Random I/O? Sequential
  I/O? ??
• GNU General Public License(???? ???? ??)
• MySQL, MariaDB ?? ??

35
Btree(Balanced Tree)
Page
Page
Page
Page
Page
Page
Page
Page
Page


36
Fractal Tree
Insert Buffer
Page
Delete Buffer
Page
Page
Page
Page
Page
Page
Page
Page


37
???? ??
38
???? ??? ?? ?????
39
??? ???? ??? ???

• ?? ??
• ??? ??? ? ?? ??? ??? ??? ???.
• ??? ??? ? ?? ??? ??? ???.
• ??? ???? ??? ?? ??? ????
• ??? ?? ?????.(??? ?? 2??)
• ?? ??? ??
• Magic Tree House 1 28
• Marvin Redpost 1 8
• A to Z Mystery 1 26

40
LDF SQL Server Backup BCM DCM
41
LDF VLF(Virtual Log File)
???(?? ??)
??? ?? ? ??.
Transaction Log Backup
LDF ??? ???
LDF ??? ?? DBCC SHRINKFILE

Transaction Log Backup
Transaction Log Backup
42
VLF ???? ??
DBCC LOGINFO
Q) LDF ??? ?? ?? ??? ???
??? ??? ?? ??.
43
SQL Server Backup

• Full Backups
• Transaction Log Backups
• Differential Backups
• Partial Backups
• Backing Up Read-Only Databases

? ? ? ? ? ?
?
Full Backups Start
Full Backups End
Tran saction Log Backups
Tran saction Log Backups
Dfferential Backups
Tran saction Log Backups
Tran saction Log Backups
Dfferential Backups
1
2
? ? ? ?
? ? ?
?? ??? ???? ??? ?
44
DaumKakao SQL Server Backup ??

• - Full Backup ?? 0?
• Transaction Log Backup 1? 2330?(30? ??)
• ????
• ??? 1? ??(C gt RAID 1)
• MDF/LDF(D gt RAID 10)
• 2? ?? ?? ?? ? Restore(?? ??)
• 3? ?? ?? ??

45
BCM DCM

• BCM(Bulked Change Map)
• BACKUP LOG ?? ?? ??? Extent ??
• 1??? 1?? Extent? ??(4GB)
• DCM(Differential Change Map)
• ??? BACKUP DATABASE ?? ??? Extent ??
• 1??? 1?? Extent? ??(4GB)

46
Query Internal Checkpoint Lazy Writer
47
Query Internal
Buffer Cache
Query Engine Relational Storage
Update Query
CheckPoint Lazy Writer
Log Cache
Log Cache
COMMIT
MDF
LDF
48
CheckPoint Lazy Writer

• CheckPoint
• BufferPool? ?? ?? Dirty Page? ???? ??
• ?? ?? ?? ??
• Lazy Writer
• ????? ??? Dirty Page? ???? ??
• ??? Swap? ??? ?? ??
• ??

49
??

• SQL Server Storage
• Datafile, Extent, Page, PFS(Page Free
  Space)
• 2? ??, Row Migration, Page Split
• GAM, SGAM, IAM, Table Or Index Scan ??
• Balanced Tree, Fractal Tree
• LDF, SQL Server Backup, BCM, DCM
• Query Internal
• CheckPoint
• Lazy Writer

50
Q/A
51
?????.