SQL Server Performance Tuning series (6)-index structure and Tuning

I. Preface

Index plays an important role in database performance. The advantages and disadvantages of index design directly affect the efficiency of DB execution. Therefore, when performing DB tuning, some of them will start to process the index. SQL server also provides a good tool for Database Engine Tuning Advisor and provides some advice on index creation and optimization.

 

Ii. Index Overview

In this regard, I have mentioned a lot in various blog forums. Here I will give a general summary:

1. Basic Structure of a data table

When a new table is created, the system allocates a continuous space in 8 K units to the disk. When the first 8 K is used up, the SQL Server pointer automatically allocates 8 K space. Each 8 K space becomes a data page, also known as a page or data page, and a page number ranging from 0 to 7 is allocated. The guiding information of each file on The 0th page is called the file header ); each 8 Data Pages (64 K) form an extended area (extent. A heap is formed by a combination of all data pages ).

 

2. Basic concepts of Indexes

The purpose of indexing is to improve data retrieval efficiency, database performance, and data access speed. The system table sysindexes stores important index information. Uses B-tree as the storage structure.

 

3. Data Table scanning and Indexing

When no index is available, table scan is used to access table data. The average efficiency is relatively low.

When an index is created, index scan/seek is used to access table data. The average efficiency is high.

 

4. Clustered index and non-clustered Index)

Similarities:

• A group of Data Pages stored in the B-tree storage structure
• Nodes of different levels contain data pages pointing to another level
• The subnode contains all the key values.
• You can find the index size and content distribution in sysindexes.
• Improves data query efficiency.

Differences:

• What do leaf nodes store: clustered indexes store actual data pages; non-clustered indexes store pointers

 

Note: The content stored at the leaf level is different.

 

5. Covering Index)

Index overwriting means that the index creation field exactly overwrites the fields involved in the query conditions. Note that it must begin with the first one.

 

6. dacklock)

See

Http://www.cnblogs.com/changbluesky/archive/2010/06/10/1753021.html

 

Iii. Performance)

1. Index fragmentation

1.1 query fragments

SYS. dm_db_index_physical_stats can be used to detect all indexes in a specific index, table or index view, all indexes in the database, or fragments in all indexes in all databases.

Important column:

Avg_fragmentation_in_percent	Percentage of logical fragments (unordered pages in the index)
Fragment_count	Number of shards in the index (physically consecutive leaf pages)
Avg_fragment_size_in_pages	Average number of pages of a shard in the Index

 

1.2. Rebuild and reorganize)

The SQL Server database engine automatically maintains indexes whenever basic data is inserted, updated, or deleted. Over time, these modifications may cause information in the index to be dispersed in the database (containing fragments ). When the logical sorting (based on the key value) in the page contained in the index does not match the physical sorting in the data file, fragments exist. A large number of shards of indexes may reduce query performance, resulting in slow application response. Index fragmentation can be repaired by re-organizing indexes or re-generating indexes to improve performance.

• Alter index ix_indexname on DBO. tablename rebuild with (online = on)
• Alter index ix_indexname on DBO. tablename reorganize

 

Differences between the two methods:

• Re-organizing indexes match the logical order of leaf nodes (from left to right) by physically re-sorting the leaf pages, in this way, clustering indexes on tables or views and leaf levels of non-clustered indexes are fragmented.
• Re-indexing will delete the index and create a new index. In this process, fragments will be deleted, and the disk space will be reclaimed by setting the compression page with the specified or existing fill factor, and index rows will be re-ordered on consecutive pages (new pages will be allocated as needed ). This reduces the number of page reads required to obtain the requested data and improves disk performance.

 

We recommend that you use the best solution based on the fragmentation level:

 

2. Select the correct index

2.1 main considerations

Query by range

Data that often needs to be sorted

2.2 secondary considerations

The column length must be short.

• Will affect all non-clustered Indexes
• Non-clustered indexes also contain the key values of all clustered indexes.

Data Type

 

3. indexing principles

Priority of all SQL syntaxes

Give priority to creating indexes that can be used by multiple query syntaxes.

Optimal column order when creating Indexes

 

Iv. Summary

 

Like the index in a book, indexes in a database allow you to quickly find specific information in a table or index view. An index contains keys generated from one or more columns in a table or view and pointers mapped to the storage location of the specified data. By creating well-designed indexes to support queries, You can significantly improve the performance of database queries and applications. Indexes can reduce the amount of data that must be read to return the query result set. The index also forces the rows in the table to be unique to ensure the data integrity of the table.

Well-designed indexes can reduce disk I/O operations and consume less system resources. The query optimizer can also use indexes to improve query performance.

 

>>> SQL server performance tuning portal address