SQL SERVER – DBCC DBREINDEX and MAXDOP Not Possible

Yes, you read it right it is not possible to use DBCC DBREINDEX with MAXDOP hint as far as I know. If there is any method to use it and I am not familiar with it, you are welcome to correct me and I will be happy to get corrected.

SQL SERVER - DBCC DBREINDEX and MAXDOP Not Possible DBCCDBREINDEX-800x218

Recently one of my clients of Comprehensive Database Performance Health Check found out that their index rebuilding activities are going slow and they figured out they were not following my advice of using the newer syntax for rebuilding the index.

DBCC DBREINDEX

There are many reasons to not use DBCC DBREINDEX and I believe one of the reasons is that it is not scalable. There are a few more reasons to not use DBREINDEX and they are as following:

DBCC DBREINDEX does not support a) online rebuilding of indexes, b) resumable indexes, c) data compression.

If you want to use MAXDOP for few indexes, it is possible to use them with the ALTER INDEX command.

Syntax of ALTER INDEX

Here is the syntax of the ALTER INDEX Rebuilding index.

ALTER INDEX IndexName ON TableName REBUILD;

It is a very simple syntax. Here is another syntax for reorganizing the index.

ALTER INDEX IndexName ON TableName REORGANIZE;

ALTER INDEX with MAXDOP

ALTER INDEX IndexName ON TableName REBUILD WITH (MAXDOP=8);

If you have any further questions, you can connect with me on Twitter.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Index, SQL Server DBCC
Previous Post
SQL SERVER – Attach an In-Memory Database with T-SQL
Next Post
SQL SERVER – sp_who2 Parameters

Related Posts

Leave a Reply