SQL: Rebuild Indexes Online
In the SQL Server Enterprise and Developer versions of SQL Server you can rebuild indexes online. When rebuilding an index online long-term table locks are not used during the index rebuild operation. To rebuild an index online use the REBUILD WITH ONLINE = ON option in the index command.
Online index operations fail if the table contains LOB (Large OBject) data types as part of the index definition as key or nonkey columns. LOB (Large OBject) data types are: image (data type 34), text (data type 35), ntext (data type 99), varchar(max), nvarchar(max), varbinary(max), and xml (data type 241).
One note, rebuilding an index drops and re-creates an index. Rebuilding an index offline forces a scan of clustered indexes and may remove some inconsistency issues.
The following SQL script will rebuild the individual indexes on tables (additionally the ALL option could be used) that do not contain LOB column types, which is what is needed for my situation.
Additionally, this script could be altered to analyze the index columns and adjust the online operation accordingly.
set nocount on;
set ansi_padding on;
set ansi_nulls on;
set quoted_identifier on;
-- declare variables.
declare @objectid as int
declare @tablename as varchar(255)
declare @indexname as varchar(255)
declare @cmd as varchar(1024)
declare @rowcount as int
-- declare a cursor.
declare indexes insensitive scroll cursor
for
-- retrieve a listing of database indexes
select
i.object_id
,object_name(i.object_id) as [objectname]
,i.name as [indexname]
from
sys.indexes i
-- where
-- if desired filter for certain objects
-- i.object_id = #####
-- (object_name(i.object_id) like '%$%entry')
-- open the cursor.
open indexes
-- loop through all the indexes.
while (1=1)
begin
fetch next from indexes into @objectid, @tablename, @indexname;
if @@fetch_status <> 0 break;
-- indexes cannot be rebuilt online if the table contains large data types
-- check if the table has a large datatype field
select
@rowcount = count(c.system_type_id)
from
sys.all_objects ao left join sys.columns c
on (ao.object_id = c.object_id)
left join sys.types s
on (c.system_type_id = s.system_type_id)
where
c.system_type_id in (34,35,99,241)
and ao.object_id = @objectid;
-- if a large data type was found then do not rebuild the index
if @rowcount = 0 begin
set @cmd = 'alter index [' + rtrim(@indexname) + '] on [' + @tablename +'] rebuild partition = all with (online=on, sort_in_tempdb = on, maxdop =0);'
exec (@cmd);
print @cmd;
end else begin
set @cmd = 'index skipped for table ' + @tablename + ' index: ' + @indexname;
print @cmd;
end;
end
-- close cursor
close indexes;
deallocate indexes;
go