In a large database, changes in data are very frequent.
The indexes built on these data also need to be maintained frequently.
Otherwise, these data indexes do not play a role. Can even become a burden on the database itself.
We have to maintain the index of the database on a regular basis. I found this script on MSDN, but there are some minor issues I've fixed. You can use this script to maintain the database's index on a daily basis
Copy Code code as follows:
SET NOCOUNT on;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command varchar (8000);
DECLARE @dbId int;
--Ensure the temporary table does not exist
IF EXISTS (SELECT name from sys.objects WHERE name = ' Work_to_do ')
DROP TABLE Work_to_do;
--Conditionally Select from the function, converting object and index IDs to names.
Set @dbId =db_id ();
SELECT
object_id as Objectid,
index_id as IndexID,
Partition_number as Partitionnum,
Avg_fragmentation_in_percent as Frag
Into Work_to_do from sys.dm_db_index_physical_stats (@dbId, NULL, NULL, NULL, ' LIMITED ')
WHERE avg_fragmentation_in_percent > 10.0 and index_id > 0;
--Declare the cursor for the "List of partitions to be processed."
DECLARE partitions CURSOR for SELECT * from WORK_TO_DO;
--Open the cursor.
OPEN partitions;
--Loop through the partitions.
FETCH NEXT
From partitions
Into @objectid, @indexid, @partitionnum, @frag;
While @ @FETCH_STATUS = 0
BEGIN;
SELECT @objectname = o.name, @schemaname = S.name
From sys.objects as O
JOIN Sys.schemas as s on s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = Name
From sys.indexes
WHERE object_id = @objectid and index_id = @indexid;
SELECT @partitioncount = count (*)
From sys.partitions
WHERE object_id = @objectid and index_id = @indexid;
--is a arbitrary decision point in which to switch between reorganizing and rebuilding
IF @frag < 30.0
BEGIN;
SELECT @command = ' ALTER INDEX [' + @indexname + '] on ' + @schemaname + '. [' + @objectname + '] REORGANIZE ';
IF @partitioncount > 1
SELECT @command = @command + ' partition= ' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
End;
IF @frag >= 30.0
BEGIN;
SELECT @command = ' ALTER INDEX [' + @indexname + '] on ' + @schemaname + '. [' + @objectname + '] REBUILD ';
IF @partitioncount > 1
SELECT @command = @command + ' partition= ' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
End;
PRINT ' executed ' + @command;
FETCH NEXT from partitions into @objectid, @indexid, @partitionnum, @frag;
End;
--Close and deallocate the cursor.
Close partitions;
DEALLOCATE partitions;
--Drop the temporary table
IF EXISTS (SELECT name from sys.objects WHERE name = ' Work_to_do ')
DROP TABLE Work_to_do;
Go
This script will create a table at run time Work_to_do will automatically delete this table after finishing. If you do not like this, you can also use a temporary table to solve.
