Periodically perform a database reorganization on all the indexes on the table in your database. This will rebuild the indexes so that the data is no longer fragmented. Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server’s performance.
From SQL Server 2005 on wards, the indexes can be re-index ONLINE. This ensures that the underlying tables and indexes are available for other users when the re-indexing is taking place. Also Standard and Workgroup editions of SQL.
The limitation for ONLINE Re-indexing is that it cannot be run on tables which have Large Object Data type columns (image, text, ntext, varchar(max), nvarchar(max), varbinary(max), xml). The Alter Index documentation on MSDN has more details on this.
There are many ways to find out LOB data types in a table, here is the one share by Pradeep Adiga in his blog.
SELECT a.name as [ColumnName],B.name as [Table Name] FROM SYS.COLUMNS A JOIN SYS.OBJECTS B ON A.object_id = B.object_id WHERE A.system_type_id in (35,34,241,99) AND B.type ='U' AND B.name <>'dtproperties' UNION SELECT a.name, B.NAME FROM SYS.COLUMNS A JOIN SYS.OBJECTS B ON A.object_id = B.object_id WHERE A.system_type_id IN (167,231,165) AND A.max_length = 8000 AND B.type ='U' AND B.name <>'dtproperties'
With this script, the LOB data type columns can be identified and is useful while trying to re-index tables online.
- Find tables with LOB data types in a DB – by Pradeep Adiga