Happy Weekend folks. Today let me discuss about trace flag 2528 which will disable parallelism on maintenance DBCC. By using this trace flag it disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE commands. By default parallelism is enabled and it will look for max degree of parallelism to utilize parallel processors for all queries however in some scenarios if you want to turn off parallelism for maintenance DBCC commands then you can utilize this trace flag so that you can utilize other processors for your adhoc queries and other operations
Let’s do a test on this, I’m going to run DBCC CHECKDB on a database without enabling this trace flag. From the image below you can see that it’s utilizing all the processors available in my system.
Now lets enable the trace flag 2528 in startup parameter and then lets do the DBCC statement again. To know how to enable trace flag check the article “Enable \ Disable Trace Flag in SQL Server”.
From the image above you can see now it’s utilizing only one processor for maintenance task thus it disabled parallelism for maintenance task. However still SQL Server can utilize parallelism for other operations running in SQL Server.