SQL Server 2000 Storage and Access Methods 再m叫 Don vilen Program Manager SQL Server Development Team
SQL Server 2000 Storage and Access Methods Don Vilen Program Manager SQL Server Development Team
Agenda SQL Server Overview a SQL Server Architecture u Storage and Access Methods a Query Processing and optimization a Transaction Processing Other Topics
Agenda ◼ SQL Server Overview ◼ SQL Server Architecture ◼ Storage and Access Methods ◼ Query Processing and Optimization ◼ Transaction Processing ◼ Other Topics
Storage and Access Methods
Storage and Access Methods
Storage and Access Methods u Heap-a table with no Clustered Index General Index structure Clustered and Non-clustered indexes Covering Indexes Index intersection Fillfactor and performance u Index Reorganization a Locking and Indexes
Storage and Access Methods ◼ Heap – A table with no Clustered Index ◼ General Index Structure ◼ Clustered and Non-clustered Indexes ◼ Covering Indexes ◼ Index Intersection ◼ Fillfactor and Performance ◼ Index Reorganization ◼ Locking and Indexes
Heap-A Table with no Clustered Index sysindexes id indid =0 First AM Extent Bit Map 176 Heap Extent 160 Extent 168 Extent 176 Extent 184 01Con 01 Dunn SEattle 01Graff 02Funk 02 Randall paris 02Bacon 03White 03 Ota 03 Koch 04Durkin 04 Slichter 05 Lang 05LaBrie
Heap – A Table with no Clustered Index Heap Extent 160 Extent 168 Extent 176 01 02 03 … … Smith Ota Jones … ... … … … ... ... 01 02 03 04 … Akhtar Funk Smith Martin ... … … … ... ... 01 02 03 … … Rudd White Barr ... ... … … … ... ... 01 02 03 … … Con Funk White ... ... … … … ... ... 01 02 03 … … Smith Ota Jones … ... … … … ... ... 01 02 03 04 … Akhtar Funk Smith Martin ... … … … ... ... 01 02 03 … … Rudd White Barr ... ... … … … ... ... 01 02 03 04 05 Con Funk White Durkin Lang … … … ... ... 01 02 03 … … Smith Ota Jones … ... … … … ... ... 01 02 03 04 … Akhtar Funk Smith Martin ... … … … ... ... 01 02 03 … … Rudd White Barr ... ... … … … ... ... 01 02 03 … … Con Funk White ... ... … … … ... ... 01 02 03 … … Smith Ota Jones … ... … … … ... ... 01 02 03 04 … Akhtar Funk Smith Martin ... … … … ... ... 01 02 03 … … Rudd White Barr ... ... … … … ... ... 01 02 03 04 05 Dunn Randall Ota Slichter LaBrie … … … ... ... 01 02 03 … … Smith Ota Jones … ... … … … ... ... 01 02 03 04 … Akhtar Funk Smith Martin ... … … … ... ... 01 02 03 … … Rudd White Barr ... ... … … … ... ... 01 02 03 … … Con Funk White ... ... … … … ... ... 01 02 03 … … Smith Ota Jones … ... … … … ... ... 01 02 03 04 … Akhtar Funk Smith Martin ... … … … ... ... 01 02 03 … … Rudd White Barr ... ... … … … ... ... 01 02 03 … … Smith Ota Jones … ... … … … ... ... 01 02 03 04 … Akhtar Funk Smith Martin ... … … … ... ... 01 02 03 … … Rudd White Barr ... ... … … … ... ... 01 02 03 … … Con Funk White ... ... … … … ... ... 01 02 03 … … Smith Ota Jones … ... … … … ... ... 01 02 03 04 … Akhtar Funk Smith Martin ... … … … ... ... 01 02 03 … … Rudd White Barr ... ... … … … ... ... 01 02 03 … … Graff Bacon Koch ... ... … … … ... ... Extent 184 01 02 03 04 … Seattle Paris Tokyo Atlanta ... … … … ... ... IAM … 160 1 168 1 176 0 184 1 … Extent Bit Map id indid = 0 First IAM sysindexes
General Index structure sysindexes id indid =X root Akhtar Node Pages Martin Akhtar Page 140-Root Martin Ganio Smith Page 141 Page 145 Leaf Akhtar 2334. Ganio 7678.mArtin 1234.SMith 1434 Pages Barr 5678 al8078 Martin 7778 Smith 5778 Con2534 Jones2434 ota|5878 Smith7978 1334 Jones 5978 Phua7878 White 2234 1534 Jones2634 Rudd 6078 White 1634 Page 100 Page 110 Page 120 Page 130
General Index Structure Page 140 - Root Page 141 Page 145 Akhtar Ganio … Akhtar … Martin Martin Smith … Node Pages sysindexes id indid = x root Page 100 Page 120 Page 130 Akhtar Barr Con Funk Funk ... 2334 5678 2534 1334 1534 ... ... ... ... ... ... ... Martin Martin Ota Phua Rudd ... 1234 7778 5878 7878 6078 ... ... ... ... ... ... ... Smith Smith Smith White White ... 1434 5778 7978 2234 1634 ... ... ... ... ... ... ... Page 110 Ganio Hall Jones Jones Jones ... 7678 8078 2434 5978 2634 ... ... ... ... ... ... ... Leaf Pages
Clustered and nonclustered Indexes Common features u Set of pages stored in B-Tree Node levels contain pointers to pages at the next level Leaf level contains all key values Size and data distribution information is stored in sysindexes Differences a What else is stored in leaf level
Clustered and Nonclustered Indexes ◼ Common features ◼ Set of pages stored in B-Tree ◼ Node levels contain pointers to pages at the next level ◼ Leaf level contains all key values ◼ Size and data distribution information is stored in sysindexes ◼ Differences ◼ What else is stored in leaf level?
Clustered Indexes Leaf level is the data u Uniqueness is maintained in key values
Clustered Indexes ◼ Leaf level is the data ◼ Uniqueness is maintained in key values
Finding rows in a Clustered Index sysindexes id indid=1 root Clustered Index Akhtar Akhtar Page 140-Root Martin Ganio Smith Page 141 Page 145 Akhtar2334 Ganio7678 Martin/34 Smith 1434 Barr5678 Ha8078 Martin778 Smith5778 Con 2534 Jones 2434 Smith7978 Funk1334 Jones5978. Phua 7878 White2234 Funk 1534 Jones2634. Rudd 6078 White1634 Page 100 Page 110 Page 120 Page 130
Finding Rows in a Clustered Index Clustered Index Page 140 - Root Page 100 Page 120 Page 130 Page 141 Page 145 Akhtar Barr Con Funk Funk ... 2334 5678 2534 1334 1534 ... ... ... ... ... ... ... Martin Martin Ota Phua Rudd ... 1234 7778 5878 7878 6078 ... ... ... ... ... ... ... Smith Smith Smith White White ... 1434 5778 7978 2234 1634 ... ... ... ... ... ... ... Akhtar Ganio … Akhtar … Martin Martin Smith … Page 110 Ganio Hall Jones Jones Jones ... 7678 8078 2434 5978 2634 ... ... ... ... ... ... ... SELECT lastname, firstname FROM member WHERE lastname = 'Ota' Clustered Index Page 140 - Root Page 100 Page 120 Page 130 Page 141 Page 145 Akhtar Barr Con Funk Funk ... 2334 5678 2534 1334 1534 ... ... ... ... ... ... ... Martin Martin Ota Phua Rudd ... 1234 7778 5878 7878 6078 ... ... ... ... ... ... ... Smith Smith Smith White White ... 1434 5778 7978 2234 1634 ... ... ... ... ... ... ... Akhtar Ganio … Akhtar … Martin Martin Smith … Page 110 Ganio Hall Jones Jones Jones ... 7678 8078 2434 5978 2634 ... ... ... ... ... ... ... Martin Ota 5878 ... Martin sysindexes id indid = 1 root
Nonclustered indexes Leaf level contains Columns of index 」 Bookmark to data row Bookmark is either 口RID(Fe#,Page#Sot# Unique clustered index key If overlap between clustered and nonclustered keys, value is stored only once s Rows are in order by first index column A Scan can be done on the leaf level
Nonclustered Indexes ◼ Leaf level contains ◼ Columns of index ◼ Bookmark to data row ◼ Bookmark is either: ◼ RID (File #, Page #, Slot #) ◼ Unique clustered index key ◼ If overlap between clustered and nonclustered keys, value is stored only once ◼ Rows are in order by first index column ◼ A Scan can be done on the leaf level
