Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the difference between sqlserver index reconstruction and index reorganization". In the daily operation, I believe that many people have doubts about the difference between sqlserver index reconstruction and index reorganization. Xiaobian consulted all kinds of data and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the question of "what is the difference between sqlserver index reconstruction and index reorganization". Next, please follow the editor to study!

ALTER INDEX REORGANIZE reorganizes the index with the least system resources and is an online operation. That is, no long-term blocking table lock is retained, and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction.

ALTER INDEX REBUILD rebuilds the index to delete and recreate the index. This can be done online or offline, regenerating the index for online execution (ON), and the data in this table can be queried and modified during the index operation. The default is OFF.

Rebuild all indexes on the table

Alter index all on table_name rebuild with (>

Rebuild an index on a table

Alter index index_name on table_name rebuild with (>

Reorganize all indexes on the table

Alter index all on table_name reorganize

Reorganize an index on a table

Alter index index_name on table_name reorganize

Summary:

1. Sqlserve recommends using ALTER INDEX statements to rebuild or reorganize indexes, but DBCC INDEXDEFRAG and DBCC DBREINDEX are no longer recommended.

2. Reorganizing the index is to reorganize the Index online and will not lock the Table. Rebuilding the index will lock the Table. Of course, add > during the rebuilding of the index.

3. The 100% progress of reorganizing the index can be seen through the field percent_complete of sys.dm_exec_requests. Rebuilding the index cannot be seen through this method.

Https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-indexdefrag-transact-sql?view=sql-server-2017

DBCC INDEXDEFRAG

(

{database_name | database_id | 0}

, {table_name | table_id | view_name | view_id}

[, {index_name | index_id} [, {partition_number | 0}]]

)

[WITH NO_INFOMSGS]

For example, DBCC INDEXDEFRAG (DB1, TABLE1, INDEX1) WITH NO_INFOMSGS

Database_name | database_id | 0

The database that contains the index to defragment. If 0 is specified, the current database is used.

Table_name | table_id | view_name | view_id

The table or view that contains the index to defragment.

Index_name | index_id

The name or ID of the index to defragment. If not specified, the statement will defragment all indexes of the specified table or view.

Partition_number | 0

The partition number of the index to be defragmented. If no or 0 is specified, the statement defragments all partitions of the specified index.

DBCC INDEXDEFRAG defragments the leaf level of the index so that the physical order of the pages matches the logical order of the leaf nodes from left to right, thus improving index scanning performance.

Unlike DBCC DBREINDEX (or the usual index generation operation), DBCC INDEXDEFRAG is an online operation. It doesn't keep the lock for a long time. Therefore, DBCC INDEXDEFRAG does not block running queries or updates. Because the time required for defragmentation is related to the level of defragmentation, if the index has relatively little fragmentation, the index is defragmented faster than generating a new index. Destructing an index with too much fragmentation may take more time than rebuilding the index.

Https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-dbreindex-transact-sql?view=sql-server-2017

DBCC DBREINDEX (table_name [, index_name [, fillfactor]]) [WITH NO_INFOMSGS]

Such as DBCC DBREINDEX (TABLE1,'', 0)

Table_name

The name of the table containing the specified index to be regenerated.

Index_name

The index name to regenerate. The index name must conform to the rules for identifiers. If index_name is specified, table_name must be specified. If index_name is not specified or the value is "", all indexes of the table are regenerated.

Fillfactor

The percentage of space on each index page used to store data when the index is created or rebuilt. After the index is created, fillfactor replaces the fill factor to become the new default for that index and any other nonclustered indexes that are rebuilt (because the clustered index is rebuilt).

When fillfactor is 0, DBCC DBREINDEX uses the fill factor value last specified for the index. This value is stored in the sys.indexes catalog view.

If fillfactor is specified, index_name must be specified. If fillfactor is not specified, the default fill factor of 100 is used.

DBCC DBREINDEX rebuilds one index of the table or all indexes defined for the table. By allowing indexes to be dynamically rebuilt, indexes that enforce PRIMARY KEY or UNIQUE constraints can be rebuilt without having to delete and recreate those constraints. This means that the index can be rebuilt without knowing the structure of the table or its constraints. This may occur after the data is bulk copied into the table.

DBCC DBREINDEX can regenerate all indexes of a table in a single statement. This is easier than coding multiple DROP INDEX and CREATE INDEX statements. Because this work is done through a single statement, DBCC DBREINDEX automatically becomes atomic, while individual DROP INDEX and CREATE INDEX statements must be included in a transaction to become atomic. In addition, DBCC DBREINDEX provides more optimized performance than individual DROP INDEX and CREATE INDEX statements.

Unlike DBCC INDEXDEFRAG or ALTER INDEX with the REORGANIZE option, DBCC DBREINDEX is an offline operation. If the nonclustered index is rebuilt, the related table holds a shared lock for the duration of the operation. This prevents changes to the table. If the clustered index is rebuilt, the exclusive table lock is held. This prevents access to any table, so it can effectively take the table offline. To perform an online index rebuild, or to control the degree of parallelism during an index rebuild operation, you can use an ALTER INDEX REBUILD statement with the ONLINE option.

At this point, the study on "what is the difference between sqlserver index reconstruction and index reorganization" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.