Add MAXDOP parameter to Update Stats - by Javier Villegas

Status : 

 


87
0
Sign in
to vote
ID 628971 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 12/6/2010 11:45:39 AM
Access Restriction Public

Description

I'd like to have an extra parameter in UPDATE STATS command to be able to specify the desired MAXDOP value.
Sign in to post a comment.
Posted by AaronBertrand on 7/1/2016 at 1:18 PM
Full agree with Michael - would be great to have OPTION (MAXDOP) support for manual operations, and a DATABASE SCOPED CONFIGURATION to fall back on for automatic. The justification would match those used to add MAXDOP controls for DBCC CHECK operations: you don't want those taking over the box, and can't use RG or instance-wide MAXDOP.

http://sqlperformance.com/2016/07/sql-statistics/statistics-maxdop
Posted by Michael K Campbell on 7/1/2016 at 1:05 PM
I think the business justification for the need to control the degree of parallelism should be pretty straight-forward:

A. The ability to now have Stats Updates (manual or auto) use Parallelism for SAMPLED stats is a HUGE win in 2016.
B. There are already DB-Scoped config options that lets us control whether Auto-Stats are created/updated or not. (Meaning DBAs should be (and are) allowed to control stats updates/creation - but just not DOP at this point).
C. Just because I have 24 cores and a 3TB table where stats need to be created/updated, doesn't mean I necessarily want all 48 cores going nuts creating stats to satisfy some ad-hoc query. I DO, potentially, want stats on that table - but if I do, I'd also want/need to control MAXDOP for those operations (per database) to avoid trashing the entire server.

Ideally, then, there would be an OPTION (MAXDOP #) for manual UPDATE STATS operations, and a db-scoped config of MAXDOP value for Auto-Stats operations.

--Mike
Posted by akelly on 7/1/2016 at 1:00 PM
We too have very large DW table that are in the hundreds of GB to several TB range per table. As such FULL scan is simply not an option. SQL2016 brings the ability for sampled stats to use parallelism which is great but there is no control over what MAXDOP is used. Because we have some very custom processes to update the stats with lots of variables we cannot count on Resource Governor to properly set MADOP level. Adding the ability to set MAXDOP anywhere (up to the 2016 limit of 16) regardless of instance level MAXDOP would allow me to speed up the processes when needed and still be able to control total DOP when multiple jobs are running in parallel so as to not severely impact overall user performance.
Posted by Laurent Pasquier on 2/19/2016 at 2:23 AM
MAXDOP hint on update statistics statement would be very interesting.

Our serveur (80 logical cores, 2To RAM, 6 * 24To LUN full SSD).
For example, on a table with 1392625705 rows in clustered columnStore ARCHIVE compress with month partitionning i have the following results :

> UPDATE STATISTICS dbo.FactEvenementsPages(ST_DateEnr) WITH FULLSCAN, INCREMENTAL=ON; => Complete in 59 seconds -> maxDop = 16
> UPDATE STATISTICS dbo.FactEvenementsPages(ST_DateEnr) WITH INCREMENTAL=ON; => Complete in 569 seconds -> maxDop = 1

The maxDop is selected automatically by sql server engine StatMan instruction

Détails:

> UPDATE STATISTICS dbo.FactEvenementsPages(ST_DateEnr) WITH FULLSCAN, INCREMENTAL=ON
give the folowing statement :
SELECT StatMan([SC0]) FROM (SELECT TOP 100 PERCENT [DateEnr] AS [SC0] FROM [dbo].[FactEvenementsPages] WITH (READUNCOMMITTED) WHERE $PARTITION.fn_PartMonthly([DateEnr]) = 14 ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 16)

AND

> UPDATE STATISTICS dbo.FactEvenementsPages(ST_DateEnr) WITH INCREMENTAL=ON
give the folowing statement :

SELECT StatMan([SC0], [SB0000]) FROM
(SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM (SELECT [DateEnr] AS [SC0] FROM [dbo].[FactEvenementsPages] TABLESAMPLE SYSTEM (5.796610e+001 PERCENT) WITH (READUNCOMMITTED) WHERE $PARTITION.fn_PartMonthly([DateEnr]) = 14) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)

Only update stats with FULLSCAN are maxdop.
Posted by AaronBertrand on 11/12/2015 at 1:32 PM
Javier, can you update this item to explicitly call out the need for control over parallelism used by auto stats as well?
Posted by Mike Lawell on 12/11/2014 at 2:17 PM
And actually, it seems to vary on what MAXDOP is used...

SELECT StatMan([SC0], [SC1], [SC2], [SC3], [SC4], [SC5], [SC6], [SC7], [SC8], [SC9], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], [SC1], [SC2], [SC3], [SC4], [SC5], [SC6], [SC7], [SC8], [SC9], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM (SELECT [Merchant] AS [SC0], [Terminal] AS [SC1], [TransactionTypeId] AS [SC2], [ResponseCodeId] AS [SC3], [Reversed] AS [SC4], [Settled] AS [SC5], [Voided] AS [SC6], [Acquirer] AS [SC7], [AuthorizationId] AS [SC8], [TransactionId] AS [SC9] FROM [dbo].[Transactions] TABLESAMPLE SYSTEM (2.000000e+001 PERCENT) WITH (READUNCOMMITTED) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SC1], [SC2], [SC3], [SC4], [SC5], [SC6], [SC7], [SC8], [SC9], [SB0000] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)
Posted by Mike Lawell on 12/11/2014 at 2:04 PM
Ditto what others are saying... I would have expected this to be implemented in SQL Server 2014. Especially since it is obviously using the maxdop setting for the parallelism. We should be able to control the degrees of parallelism from command line as we can with an index rebuild.

There are two use cases for this, 1) want to use more DOP than specified in MAXDOP to burn through it quickly 2) want to limit the impact on other processes running by limiting it (and yes, I know we can control this through resource governor).
Posted by Rob Volk1 on 8/12/2011 at 11:22 AM
Definitely a good suggestion. It's frustrating to have UPDATE STATISTICS decrease performance due to CXPACKET waits and thread exhaustion. Sorry guys, Resource Governor may be a solution, but Enterprise Edition is not.

Also, this should also add a database-level setting for auto-update statistics maxdop.
Posted by Jon Morisi on 4/18/2011 at 2:41 PM
+1 for maxdop hint for update statistics.
Posted by Microsoft on 12/15/2010 at 4:32 PM
Ok. Thanks again. We'll consider this for a future release.

Best regards,
Eric
Posted by Javier Villegas on 12/15/2010 at 10:17 AM
Resource Governor can help for this issue. But I still think that having the MAXDOP option for UPDATE STATS will be very helpful

Regards
Javier Villegas
Posted by Microsoft on 12/9/2010 at 5:32 PM
Did you try to use resource governor to solve this problem? That could be done in Enterprise Edition and higher.

Thanks,
Eric
Posted by Microsoft on 12/9/2010 at 5:32 PM
Did you try to use resource governor to solve this problem? That could be done in Enterprise Edition and higher.

Thanks,
Eric
Posted by Microsoft on 12/9/2010 at 5:32 PM
Did you try to use resource governor to solve this problem? That could be done in Enterprise Edition and higher.

Thanks,
Eric
Posted by Javier Villegas on 12/9/2010 at 1:12 PM
The configured MAXDOP on our production server is 4. We perform update stats with FULL SCAN as part of our weekly maintenance process (currently taking ~ 9 hours) so I'd like to speed up the process by using MAXDOP = 0 just for this maintenance process.

Regards
Javier
Posted by Microsoft on 12/9/2010 at 11:27 AM
Thanks for your feedback. You can accomplish the same thing with SQL Server resource governor. Can you use that to solve your problem?

Can you elaborate on why you want this? What is the problem scenario? Do you want to speed up stats gathering or do you want to make it use less resources to interfere less with other work? Your example used the default, sampled approach to gathering statistics, which is usually pretty fast already.

Best regards,
Eric Hanson
SQL Server Query Processing Team