Shulou(Shulou.com)06/01 Report--

Most people do not understand the knowledge points of this article "what is the method of SQL Server index optimization?", so the editor summarizes the following, detailed contents, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "what is the method of SQL Server index optimization" article.

Type of index

If column holds highly relevant data and is often accessed sequentially, it is best to use clustered indexes, because if clustered indexes are used, SQL Server will physically rearrange the data columns in ascending or descending order, so that the queried data can be found quickly. Similarly, when the search control is within a certain range, it is best to use the clustered index for these column. This is because there is only one clustered index on each table because the data is physically rearranged.

Contrary to the above, if the data contained in columns is poorly correlated, you can use nonculstered indexes. You can use up to 249 nonclustered indexes in a table-although I can't imagine using so many indexes in a real application.

When the table uses the primary keyword (primary keys), by default SQL Server automatically establishes a unique cluster index on the column (s) that contains the keyword. Obviously, building a unique index on these column (s) means the uniqueness of the primary keyword. When building a foreign key relationship, if you plan to use it frequently, it is a good idea to build an nonclustered index on the outer keyword cloumn. If the table has an clustered index, it uses a linked list to maintain the relationship between the data pages. Conversely, if the table does not have an clustered index, SQL Server will save the data page in a stack.

Data page

When the index is established, SQLServer creates a datapage, which is a pointer to speed up the search. When the index is established, its corresponding fill factor is set. The fill factor is set to indicate the percentage of data pages in the index. Over time, updates to the database consume existing free space, which causes the page to be split. The consequence of page splitting is that the performance of the index is degraded, so queries that use the index can cause fragmentation of the data store. When an index is created, the fill factor of the index is set, so the fill factor cannot be maintained dynamically.

In order to update the fill factor in the data page, we can stop the old index and rebuild the index, and reset the fill factor (note: this will affect the operation of the current database, please use it carefully on important occasions). DBCC INDEXDEFRAG and DBCC DBREINDEX are two commands to clear the fragmentation of clustered and nonculstered indexes. INDEXDEFRAG is an online operation (that is, it does not block other table actions, such as queries), while DBREINDEX physically rebuilds the index. In most cases, rebuilding the index can eliminate fragmentation better, but this advantage is obtained at the expense of blocking other actions that currently occur on the table where the index is located. When a large fragmented index occurs, INDEXDEFRAG takes a long time because the command is run on a small transactional block.

Filling factor

When you perform any of the above measures, the database engine can return indexed data more efficiently. The topic of the fill factor (fillfactor) is beyond the scope of this article, but I would like to remind you of the tables that you intend to index using the fill factor.

When executing the query, SQL Server dynamically chooses which index to use. To do this, SQL Server decides which index to use based on the statistics distributed over the keyword on each index. It is worth noting that after daily database activities (such as inserting, deleting, and updating tables), these statistics used by SQL Server may be "out of date" and need to be updated. You can check the status of the statistics by executing DBCC SHOWCONTIG. When you think the statistics are "out of date", you can execute the table's UPDATE STATISTICS command so that SQL Server refreshes the information about the index.

Establish a database maintenance plan

SQL Server provides a tool to simplify and automatically maintain databases. This tool, called the Database maintenance Plan Wizard (Database Maintenance Plan Wizard, DMPW), also includes index optimization. If you run this wizard, you will see statistics about the index in the database, which work as logs and are updated regularly, thus reducing the workload of manually rebuilding the index. If you do not want to automatically refresh index statistics on a regular basis, you can also choose to reorganize data and data pages in DMPW, which will stop the old index and rebuild the index by a specific fill factor.

The above is about the content of this article on "what is the method of SQL Server index optimization". I believe we all have a certain understanding. I hope the content shared by the editor will be helpful to you. If you want to know more related knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.