Basic Process of SQL Server database sorting and optimization (3)

SQL Server
Basic Process of database sorting and optimization (3)

Gao Jiangang

 

Section 2 Index

 

Section 3 index Maintenance

 

The engine automatically maintains indexes when the database performs insert, update, and delete operations on data. With the accumulation of time, these operations will cause data discontinuity, that is, the fragmentation of the index. With the generation of fragments, the query performance is reduced and reflected in the foreground program, that is, the user experience is poor, and the whole system runs slowly. In this case, we need to create an index or re-organize the index to maintain the index. As to the specific method used, we need to combine the index to generate the fragmentation severity.

It is worth noting that the DDL in SQL server2005 has added more abundant statements for index maintenance. Earlier versions often require DBCC to handle this, which is not only hard to understand, the reason cannot be analyzed from the principle.

First, let's take a look at how to find the index fragmentation caused by data discontinuity.

There are two main types of data discontinuity: one is the discontinuity of the internal data storage page, and many spaces do not record data; the other is that the pages of data stored on an external hard disk are inconsistent with the range, that is, indexes or data tables are scattered in multiple ranges, or the pages for storing indexes or data tables are not continuously put together. Of course, the Data Order is different from the paging order for storing instances on the hard disk, leading to inefficient hard disk reading, after reading the data, you need to organize the data again and reorganize it.

So how can we view the index's degree of discontinuity? Microsoft provides DBCC showcomfig indicators to help us find that their usage is not very complex, and the results are easy to understand.

DBCC showcontig [({table_name | table_id | view_name | view_id} [, index_name | index_id])] [With {[, [all_indexes] [, [tableresults] [, [Fast] [, [all_levels] [no_infomsgs]}]
Use an example to view the data presented by this indicator after work: