Introduction to reorganizing and re-generating indexes sp_RefreshIndex

Start:

--------------------------------------------------------------------------------

Last week, the customer reported a system problem. when processing a large amount of data, the network timed out. Later, we tracked and tested the network timeout caused by the large number of index fragments.

The solution is to re-organize and re-generate indexes. Here, I wrote a stored procedure sp_RefreshIndex for implementation.

Stored Procedure sp_RefreshIndex:
Copy codeThe Code is as follows:
Use master
Go
If object_id ('SP _ refreshindex') Is not null
Drop Proc sp_RefreshIndex
Go
Create proc sp_RefreshIndex
(
@ Reorganize_Fragmentation_Percent smallint = 5 -- when the logical fragment percentage is greater than 5%, re-organize the index.
, @ Rebuild_Fragmentation_Percent smallint = 30 -- when the percentage of logical fragments is greater than 30%, the index is regenerated.
)
As
Begin
/* Call method:
. For all databases in the current instance: exec sys. sp_MSforeachdb 'use ?; Exec sp_RefreshIndex'
. For the current database: exec sp_RefreshIndex
*/

-- The system database is not re-organized or re-indexed
If (db_name () in ('master', 'model', 'msdb', 'tempdb') return;

-- If the percentage of logical fragments (unordered pages in the index) is less than 5%, the index will not be re-organized or re-generated.
If not exists (select 1 from sys. Loads (db_id (), null) a where a. index_id> 0 and a. avg_fragmentation_in_percent> @ Reorganize_Fragmentation_Percent) return


Print replicate ('-', 60) + char (13) + char (10) + replicate ('', 14) + N' pair database '+ quotename (db_name ()) + N' index optimization '+ replicate ('', 20) + char (13) + char (10)

Declare @ SQL nvarchar (2000), @ str nvarchar (2000)

Declare cur_x cursor
Select 'alter Index' + quotename (. name) + 'on' + quotename (object_schema_name (. object_id) + '. '+ quotename (object_name (. object_id) + case when B. avg_fragmentation_in_percent <= @ Rebuild_Fragmentation_Percent then 'reorganize; 'else' rebuild; 'end as [SQL]
, Case when B. avg_fragmentation_in_percent <= @ Rebuild_Fragmentation_Percent then n' re-organizing the index: 'else' re-generating the index: 'End + quotename (object_schema_name (. object_id) + '. '+ quotename (object_name (. object_id) + '. '+ quotename (. name) as [str]
From sys. indexes
Inner join sys. dm_db_index_physical_stats (db_id (), null) B on B. object_id = a. object_id
And B. index_id = a. index_id
Where a. index_id> 0
And B. avg_fragmentation_in_percent> @ Reorganize_Fragmentation_Percent
Order by object_name (a. object_id), a. index_id

Open cur_x
Fetch next from cur_x into @ SQL, @ str

While (@ fetch_status = 0)
Begin

Exec (@ SQL)
Print @ str
Fetch next from cur_x into @ SQL, @ str

End
Close cur_x
Deallocate cur_x

End
Go
Exec sp_ms_marksystemobject 'SP _ refreshindex'
Go

Call method:
Copy codeThe Code is as follows:
Use master
Go
Exec sys. sp_MSforeachdb 'use ?; Exec sp_RefreshIndex'
Go

Note: Modify the values of @ Reorganize_Fragmentation_Percent and @ Rebuild_Fragmentation_Percent based on the actual environment.

The storage process sp_RefreshIndex has passed the test in the following environment:

SQL Server 2005 (SP4)/2008/2008 R2/2012

Extension:

--------------------------------------------------------------------------------

We can write the preceding SQL code into a Job. Run the job once every month or two through the SQL Agent service.