When it comes to achieving success, sometimes it’s better to work alone and move quickly. However, if you want to achieve long-term success, collaboration and teamwork are essential. This principle holds true in various aspects of life, including working with customers and engaging in consulting projects.

While it’s important to solve problems efficiently, it’s equally important to involve the entire team and ensure they understand the root causes of the issues. The art of working together can take us a long way, even in performance-related tasks. In this blog post, we will explore the DBCC command enhancement with MAXDOP in SQL Server.

Have you ever heard of the new enhancement announced with SQL Server 2016? It allows you to add the MAXDOP (Maximum Degree of Parallelism) settings with DBCC commands. This addition may seem small in the grand scheme of things, but it can have a significant impact on performance.

During my conversation with some of you at SQLPass, I realized that I hadn’t written about this enhancement in my previous blogs. So, let’s dive into it now.

With SQL Server 2016, you can add the MAXDOP setting to DBCC commands. Here’s an example:

DBCC CHECKDB(Adventureworks2016) WITH MAXDOP = 1

In the above command, I have run the DBCC command in a single-threaded mode by setting MAXDOP to 1. You can experiment with different values to find the optimal setting for your database on a given server.

It’s worth noting that when running the DBCC command, checks are omitted for InMemory OLTP tables. The output will include a note stating that the operation is not supported with memory-optimized tables. For example:

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 encourage you to experiment with these settings and share your observations with me. Feel free to leave your comments below. I would love to hear from you.