Description of the issue
Update on incremental statistics fails with error NORECOMPUTE' is not a recognized UPDATE STATISTICS option.
SQL Server version and edition
Microsoft SQL Server 2017 (RTM-CU22-GDR) (KB4583457) - 14.0.3370.1 (X64) Nov 6 2020 18:19:52 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)
Version of the script
2020-12-31 18:58:56
What command are you executing?
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'Y',
@MaxDOP = 4,
@Execute = 'Y'
GO
What output are you getting?
For incremental statistics here the output that raises an error:
UPDATE STATISTICS [dbo].[mytable] [Imyindex] WITH MAXDOP = 4, RESAMPLE, NORECOMPUTE ON PARTITIONS(2)
Msg 155, Level 15, State 1, Line 57
'NORECOMPUTE' is not a recognized UPDATE STATISTICS option.
The command should be as follows to not trigger the above issue:
UPDATE STATISTICS [dbo].[mytable] [myindex] WITH RESAMPLE ON PARTITIONS(2), MAXDOP = 4, NORECOMPUTE;
But maybe we should also consider to add an exclusion of statistics with norecompute option ? I didn't find options for that
Description of the issue Update on incremental statistics fails with error NORECOMPUTE' is not a recognized UPDATE STATISTICS option.
SQL Server version and edition Microsoft SQL Server 2017 (RTM-CU22-GDR) (KB4583457) - 14.0.3370.1 (X64) Nov 6 2020 18:19:52 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)
Version of the script 2020-12-31 18:58:56
What command are you executing? EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y', @LogToTable = 'Y', @MaxDOP = 4, @Execute = 'Y' GO
What output are you getting? For incremental statistics here the output that raises an error: UPDATE STATISTICS [dbo].[mytable] [Imyindex] WITH MAXDOP = 4, RESAMPLE, NORECOMPUTE ON PARTITIONS(2)
Msg 155, Level 15, State 1, Line 57 'NORECOMPUTE' is not a recognized UPDATE STATISTICS option.
The command should be as follows to not trigger the above issue:
UPDATE STATISTICS [dbo].[mytable] [myindex] WITH RESAMPLE ON PARTITIONS(2), MAXDOP = 4, NORECOMPUTE;
But maybe we should also consider to add an exclusion of statistics with norecompute option ? I didn't find options for that
David