CODEWITHSUNDEEP
sql-serverindexing
user3519275
user3519275

Reputation: 370

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:

https://blogs.msdn.microsoft.com/sqlserverfaq/2011/08/30/another-reason-of-index-logical-fragmentation/

Upvotes: 0

Views: 191

Answers (2)

Alex Ayscough
Alex Ayscough

Reputation: 111

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.

Upvotes: 0

M84
M84

Reputation: 745

here you have two options:

Very basic information.

  1. 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.

  2. 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!

Upvotes: 0

Related Questions