SQL Server Index Rebuild manual

Step One: Query Index fragmentation, the script is as follows, the library is relatively large when the execution time is very long, although the database has little impact, it is recommended to perform during off-peak hours. ( Select the database to query for fragmentation before executing)

Declare @dbid intselect @dbid =db_id () SELECT db_name (ps.database_id) as [Database NAME], object_name (PS. OBJECT_ID) as [Object name], i.name as [Index Name], ps.index_id, Ps.index_type_desc, Ps.avg_fragmentation_in_percent, PS . Fragment_count, Ps.page_count, I.fill_factor, I.has_filter, I.filter_definitionfrom sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL,NULL) As Psinner JOIN sys.indexes as I with (NOLOCK) on ps.[object_id] = i.[object_id] and ps.index_id = I.index_idwhere Ps.datab ase_id = db_id () and Ps.index_type_desc <> ' HEAP ' and Ps.page_count > 2500ORDER by Ps.avg_fragmentation_in_ Percent DESC OPTION (RECOMPILE);

Step Two:To filter the tables that need to be indexed for rebuilding, for example:in general,Avg_fragmentation_in_percent is larger than 60% to consider rebuilding, so the first 10 indexes in this example need to be rebuilt. Where index_id is 1, the expression is a clustered index (primary key index). Step Three: !! Important warning: Back up the whole library before operation, leaving behind is always the biggest responsibility for yourself and the enterprise. In the way of index rebuilds, we generally take the form of online rebuilds (not supported in versions prior to SQL Server 2005), so the statements are as follows:

Alter index Index_nameon table_namerebuildwith (online = on, sort_in_tempdb = on, MAXDOP = maximum degree of parallelism); Go

in this case, the maximum degree of parallelism should choose 80% of the operating system CPU core number is appropriate. In addition, index rebuilds should adhere to the following principles:

• To avoid excessive space usage, the index should be rebuilt sequentially, and it is not recommended to use the ALTER INDEX all on table_name ... The statement.
• If a table has multiple indexes that need to be rebuilt, the rebuild must be in the order of rebuilding the clustered index (that is, the primary key index) before rebuilding the nonclustered index.

Step Four:The progress of index rebuilds needs to be observed while rebuilding an online rebuild index, but the progress of the online index rebuild is not displayed in Sys.dm_exec_requests, so Microsoft provides another way to see how the progress is done, as follows:in the SQL Profiler progress report, you can monitor the online Index operation. where BigintData1 represents the number of online reconstruction records that have been completed, as long as the total number of rows compared to the table can be aware of the current progress, the output is rolling, we just have to estimate a approximate percentage of it.

SQL Server Index Rebuild manual