Using indexes to improve the efficiency of SQL Server data processing

Based on good database design, efficient use of indexes is the basis for SQL Server to achieve high performance, SQL Server employs a cost-based optimization model, which queries each submitted table, determines whether to use the index or the index. Because most of the overhead of query execution is disk I/O, one of the primary goals of using index to perform performance is to avoid full table scans, because full table scans require every page of the table to be read from disk, and if an index points to a data value, the query only needs to read a few disks. So if a reasonable index is established, the optimizer can use the index to speed up the query process of the data. However, the index does not always improve the performance of the system, in addition, deletion, the operation of the index will increase the presence of a certain amount of work, so, in the appropriate place to add the appropriate index and never reasonable place to remove the suboptimal index, will help optimize those poor performance of SQL Server applications. The practice shows that the reasonable index design is based on the analysis and prediction of various queries, and only if the index is combined with the program correctly can the optimal scheme be produced. This article makes some analysis and practice on the performance of SQL Server indexes.

First, the use of clustered index (clustered indexes)

A clustered index is a type of actual data on a disk that is organized to sort by the values of one or more of the specified columns. Because the indexed page pointer of a clustered index points to a data page, using a clustered index to find data is almost always faster than using a nonclustered index. Only one clustered index can be built per table, and a clustered index requires at least the additional space of the table 120% to hold a copy of the table and index the middle page. The idea of establishing a clustered index is:

1, most tables should have a clustered index or use partitioning to reduce the competition for the end of the table, in a high transaction environment, the last page of the blockade seriously affect the system's throughput.

2. Under the clustered index, the data is physically ranked on the data page, and the duplicate values are grouped together, so that when the query that contains the scope check (between, <, <=, >, >=) or the group by or order by is used, Once a row with the first key value in the range is found, rows with subsequent index values are guaranteed to be physically contiguous without further searching, avoiding a wide range of scans and greatly improving query speed.