SQL Server batch index reconstruction (sorting)

When SQL Server accumulates a certain amount of data, the query efficiency is often sharply reduced, which is often intolerable. At this time, we will think of various methods to optimize the system. DBCC is a group of powerful console commands, which are often used by DBCC dbreindex. It can help us recreate the index of a specified table to improve the query speed.

This command is easy to use, for example:

DBCC dbreindex ([Customer], '', 90)

The first parameter is the name of the table to be re-indexed. The second parameter specifies the index name. If it is null, it indicates all. The third parameter is the fill factor, which indicates the data filling degree on the index page, 0 indicates that the previous value is used, and 100 indicates that each index page is filled up. In this case, the query efficiency is the highest, but other indexes are moved when the index is inserted. You can set this parameter based on the actual situation.

This command can only operate on a single table. If you want to re-index all the tables in a database, you need to writeProgram:

Use mydb; declare @ name varchar (100) declare authors_cursor cursor for select [name] From sysobjects where xtype = 'U' order by idopen authors_cursorfetch next from authors_cursor into @ namewhile @ fetch_status = 0 begin DBCC dbreindex (@ name, '', 90) Fetch next from authors_cursor into @ name enddeallocate authors_cursor