SQL SERVER 2016 – DBCC Command Enhancement with MAXDOP

When you want to be successful, you need to run alone and fast. But if you want to run longer, you want to run together. I have personally seen multiple instances where this statement holds true to every word. When working with customers and in various consulting engagements, I have seen it is not enough to just solve the problem. It is important to enable the team working on the project and make them aware of the reasons why it did happen in the first place. I always felt the art of working together will take us a long way. Even in performance engagements, I try to get the team involved as I go through the process of solving their problems. Let us learn in this blog post DBCC Command Enhancement with MAXDOP.

Coming back to today’s topic. I am not sure if you have ever heard the new enhancement announced with SQL Server 2016 around the ability to add the MAXDOP settings with DBCC commands. I personally felt this was a nice little addition that gets unnoticed in the bigger scheme of things. When I was at SQLPass in October, I did have a conversation with some of you and just noticed that I haven’t written about it over my blogs.

In short, now with SQL Server 2016 – you can add MAXDOP as shown in the command below:

Solarwinds
DBCC CHECKDB(Adventureworks2016)
WITH MAXDOP = 1

SQL SERVER 2016 - DBCC Command Enhancement with MAXDOP dbcc-maxdop-sql2016-01-800x198

There is nothing fancy about this, in the above command – I have run the DBCC in a single threaded mode. I am sure you are going to play around with the various values and will be able to find what value suits your database on a given server.

One of the observations I had when running with DBCC command is that the checks are omitted for InMemory OLTP tables. There is a note on the output that states the same:

Object ID 2039678314 (object ‘Production.Product_inmem’): The operation is not supported with memory optimized tables. This object has been skipped and will not be processed.
Object ID 1895677801 (object ‘Sales.SpecialOffer_inmem’): The operation is not supported with memory optimized tables. This object has been skipped and will not be processed.

I am sure you are going to play around with these settings and let me know what your observations are. Would love to hear from you via the comments below.

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

Solarwinds
, , , ,
Previous Post
SQL SERVER – FIX: Incorrect Syntax Near the Keyword ‘PROCEDURE’
Next Post
SQL SERVER – Best Practices About SQL Server Service Account and Password Management

Related Posts

2 Comments. Leave new

  • Please don’t ever run with MAXDOP = 1 as illustrated here – it will give you the worst CHECKDB performance.

    Reply
    • Thank you Paul Sir,

      I agree with your point. Thanks for letting us know. I have yet to do performance test, however now as you have said it, there is no need to do any further tests.

      Your words are final when it is about SQL Server.

      Reply

Leave a Reply

Menu