SQL SERVER – Update Table Statistics in Parallel with FULLSCAN

SQL Server Performance Tuning is a much simpler job if you know exactly where to look for and how to tune the configurations. Here is the real world scenario which I encountered during the recent Comprehensive Database Performance Health Check. During the performance tuning exercise, we realized that for one of the table we needed to update the statistics with the full scan as the table was a very critical table for their business. However, every time whenever we were running the update statistics job, the query was running very slow and almost never finish.

After careful investigation, we figured out that the query is running forever as we have kept the MAXDOP at the database level to 2. Now, this was very much needed for our server to perform well but it was not working against the statistics update query and was taking forever. This was really a catch 22 situation for us. Catch 22 = a dilemma or difficult circumstance from which there is no escape because of mutually conflicting or dependent conditions.

Finally, we figured out the workaround which was much simpler than we expected. As of SQL Server 2016 R2, now we can run FULLSCAN STATISTICS Update with MAXDOP keyword as well. We actually specified a higher number of MAXDOP for our FULLSCAN query and it did help us to improve the performance of that particular query.

As we had a total of 64 processors, we were able to specify MAXDOP = 8 for our stat update query.

USE AdventureWorks2014;
GO
UPDATE STATISTICS Production.Product([AK_Product_Name])
WITH FULLSCAN, MAXDOP=8;
GO 

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