Microsoft SQL Server Internals Reading Notes-sixth chapter Indexes:internals and Management (1)

Microsoft SQL Server Internals Index directory:

"Microsoft SQL Server Internals" Reading notes-Catalog Index

In the fifth chapter, we mainly study the internal storage structure of table, and the seventh chapter "Special storage" will continue to study the storage mechanism in depth, which will be the most difficult chapter in this book. The sixth chapter mainly introduces the basic knowledge of index, the eighth chapter is "Query Optimizer", the following we first take a look at the sixth chapter: Indexes:internals and Management. This chapter is divided into three parts:

1, introduce the use of index, concepts and internal structure, you will also understand how the index is stored and how they are retrieved.

2. In-depth understanding of what happens to internal storage when data is modified, how it happens, and how SQL Server ensures consistency (consistence). You'll also learn about the potential impact of modifying the index (on performance) of the data, such as grooming.

3, the management and maintenance of the index.

Foreword: The advantage of index is self-evident. A good index may drop your query request from millions of I/O down to few or less. Similarly, an excessive index design (over-indexing) can be more serious than not using indexes. Therefore, mastering the necessary index physical storage and storage engines, policies, and optimization knowledge is critical for a SQL designer.

First, let's learn the first part:

Indexes are divided into clustered indexes (clustered index) and nonclustered indexes (nonclustered index), where table data is logically stored according to the clustered key in the clustered index table. When you find the data you want, the search is done at the same time. Rather than a clustered index in the table. The index structure is completely separate from the data itself. When you start looking for an index, you have to get the actual data in the order of some reference pointers (Reference Pointer).

For more information about how to create an index, see MSDN:
Http://msdn.microsoft.com/zh-cn/library/ms188783.aspx

SQL Server Index b-tree

In SQL Server, the index is organized according to the B-TREE structure, B-tree (balanced-tree), and SQL Server uses a special b+tree structure. Unlike the usual tree, B-tree always pours (inverted), its root root (single page) at the top, leaf (leaf) at the bottom. The level of the intermediate levels depends on a number of factors. B-tree is a word that is overloaded (overload) on different occasions in this book. It means the entire index structure, as shown in:

Importantly, we need to understand how b-tree is built in SQL Server (constructed), and what is included in each level. We start with a few simple concepts.

First, the index has two very basic components: a leaf level, one or more non-leaf levels (non-leaf levels). The latter is primarily used for leaf-level navigation. In addition, the first intermediate level is also used to collate analysis and drive pre-reading (Read-ahead) in large sequence index queries.

The presence of non-page level (non-leaf levels) is primarily intended to help quickly navigate to a row of schemas at the leaf level, rather than directly to the data itself. Each non-page level is stored bottom-up on each page (pages) until the root level is created. The higher the level (that is, the farther away from the leaf), the less information is stored, because each row at that level contains only the lowest key value at the next level, plus a pointer. In fact, these keys (up to 900 bytes or 16 columns) help keep the index tree relatively small in SQL Server.

Let's use a leaf level for an index that contains 1,000,000 (or 1 million) "Rows" as an example. First we have to make it clear that both the leaf leave and the non-leaf level are stored in SQL Server pages (8KB pages). In this example, the Non-leaf "' line" will have 4000 bytes. That is, only two rows can be stored per page. For a million "row" table, the leaf level of our index will have 500,000 pages. Relatively speaking, this is a very wide line structure, however, we do not waste a lot of space. If we had two 3,000-byte rows on our leaf page, we would still have two "rows" per page, so we would waste 2,000 bytes of space.

Notice why the "line" is used instead of the data rows, because this page level may be a clustered index (which is naturally equal to the data row), or it is possible that these leaf-level rows are rows that are added to the index's leaf level by the inclusion of non-key values for some nonclustered indexes. When the inclusion column is used, the leaf-level page can contain a wider line (more than 900-byte or 16-column limit). In this example, when the index is created, the page level will be 4GB in size (500,000 8kb-sized page). If you use the maximum limit, the last tree that grows to root will be smaller and have a maximum of 8 levels, as follows:
Root page of Non-leaf level (level 7) =2 Rows=1 page (8 rows per page)

Intermediate page of Non-leaf level (level 6) =16 rows=2 page (8 rows per page)

Intermediate Non-leaf level (Level 5) =123 rows=16 page (8 rows per page)

Intermediate Non-leaf level (level 4) =977 rows=123 page (8 rows per page)

Intermediate Non-leaf level (Level 3) =7,813 rows=977 page (8 rows per page)

Intermediate Non-leaf level (Level 2) =6,2500 rows=7,813 page (8 rows per page)

Intermediate Non-leaf level (Level 1) =50,000 rows=6,2500 page (8 rows per page)

Leaf level (Level 0) =1,000,000 rows=500,000 page (8 rows per page)

Smaller key sizes will have a faster level, with the same data as an example, if smaller index keys will bring smaller row sizes at non-leaf levels, so more rows can be stored. If you have only 20 bytes, you will be able to store 404 rows of data each:

Root page of Non-leaf level (Level 3) =4 Rows=1 page (404 rows per page)

Intermediate Non-leaf level (Level 2) =1,238 rows=4 page (404 rows per page)

Intermediate Non-leaf level (Level 1) =50,000 rows=1,238 page (404 rows per page)

Leaf level (Level 0) =1,000,000 rows=500,000 page (2 rows per page)

Keep in mind that a narrower rather than wider key (key) will bring better efficiency to the index. Most importantly: the size of the index (that is, the number of levels) depends on the three point: 1, the index definition. 2. Whether the base table (table) has a clustered index. 3. Page number at index leaf level. Where the number of leaf pages is directly in the row size and number of rows. This is not to say that a narrow index must be used in the index. Sometimes it is appropriate to use a wide index. In addition, such as "included columns" and filtered indexes also affect the size and purpose of the index. Of course, the most important thing is to use the correct index. Isn't it?
Tool for analyzing indexes (Tools for analyzing Indexes)

first, the use of sys.dm_db_index_physical_stats

[Python]View Plaincopyprint?

2. SELECT * from  sys.dm_db_index_physical_stats (db_id (' TestDB '), null,null,null,null);

For more use of sys.dm_db_index_physical_stats, see MSDN:
Http://msdn.microsoft.com/zh-cn/library/ms188917.aspx

Second, use the DBCC IND command, which is an MSDN unpublished command.

[Python]View Plaincopyprint?

2. exec (' DBCC IND (testdb,[dbo. fixed],-1) ')

The next section will continue to learn about the physical index structure (physical index Structure).

Invitation Month Note: This article copyright by invitation month and CSDN common all, reprint please indicate source.

Helping people equals self-help! [Email protected]

Transferred from: http://blog.csdn.net/downmoon/article/details/5280152