'SQL Server - Index maintenance for index with uniqueidentifier?
I got some non-clustered indexes (unique) with uniqueidentifier (GUID) as column. The index gets a lot of fragmentation all the time.
How should I solve this with Ola Hallengren´s maintenance script?
Skip reorg/rebuild of these index?
The problem is described here:
Solution 1:[1]
here you have two options:
Very basic information.
DBCC DBReindex:locks up the tables and users may not be able to access the data until the reindex is done. Bottom line - this drops the indexes and creates them from scratch. You have brand new indexes when this is done, so they are in the 'best state' possible. Again, it ties up the database tables. This is an all or nothing action. If you stop the process, everything has to rollback.
DBCC INDEXDEFRAG:Does not lock up the tables as much. Users can still access the data. The indexes still exist, they are just being 'fixed'. If this is stopped, it doesn't rollback everything. So the index will be less defragged than when you started.If you run DBReindex, you don't need to run INDEXDEFRAG. There's nothing to defrag when you have brand new indexes.
hope this help!
Solution 2:[2]
I think in this instance you should exclude these from Ola Hallengren's maintenance script. Also Guids should not be part of any clustered index.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | M84 |
| Solution 2 | Alex Ayscough |