cluster表的顺序位置有谁决定？
The clustered key ordering of the rows is indicated by the slot number of the row, not by the physical position on the page
含有聚集索引表的行记录存储顺序的实现方式：依赖行偏移数组
The row offset array indicates the logical order of rows on a page. For example, if a table has a clustered index, SQL Server stores the rows in the order of the clustered index key. This doesn't mean the rows are physically stored on the page in the order of the clustered index key. Rather, slot 0 in the offset array refers to the first row in the clustered index key order, slot 1 refers to the second row, and so forth. As we'll see shortly when we examine an actual page, the physical location of these rows can be anywhere on the page.
聚集索引 page linkage，各页之间的关系
SQL Server 2005 doesn't connect the individual data pages of a table in a doubly linked list unless the table has a clustered index. Pages at each level of an index are linked together, and because the data is considered the leaf level of a clustered index, SQL Server does maintain the linkage. However, for a heap, there is no such linked list connecting the pages to each other. The only way that SQL Server determines which pages belong to a table is by inspecting the Index Allocation Maps (IAMs) for the table.
聚集索引观察各页的操作
If the table has a clustered index, you can use the M_nextPage and M_prevPage values in the page header information to determine the ordering of pages in the list. Alternatively, you can use the DBCC IND command to get a list of all pages that belong to an index, along with a column showing the next page and the previous page, for each page
在增加新行时，索引更新时使用的所情况：此时使用latch
This means that while the index is being searched on an insert, the index is protected in anticipation of possibly being updated. The protection mechanism is a latch, which you can think of as something like a lock.
如何避免分页
You can avoid system disruption during busy times by reserving some space on pages using the FILLFACTOR clause when you're creating the clustered index on existing data
这需要定期执行，比较麻烦。是否有一劳永逸的办法
阅读(1350) | 评论(0) | 转发(0) |
