SQL SERVER - DBCC DBREINDEX and MAXDOP Not Possible - SQL Authority with Pinal D...
source link: https://blog.sqlauthority.com/2020/12/24/sql-server-dbcc-dbreindex-and-maxdop-not-possible/?utm_campaign=sql-server-dbcc-dbreindex-and-maxdop-not-possible
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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.
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)
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK