Error:-
If we have clustered column store index on a table and when we update statistics with traditional command like
UPDATE Statistics <TableName> (Statisticsname ) with FULLSCAN
-->The update statistics command gets fail with the below-highlighted message
Msg 35337, Level 16, State 1, Line 23
UPDATE STATISTICS failed because statistics cannot be updated on a columnstore index. UPDATE STATISTICS is valid only when used with the STATS_STREAM option.
use AdventureWork2014
go
CREATE CLUSTERED columnstore INDEX [PK_ErrorLog_ErrorLogID] ON [dbo].[Errorlog] WITH (DROP_EXISTING = OFF)
then Execute below command to retrieve
SELECT
OBJECT_NAME([sp].[object_id]) AS "Table",
[sp].[stats_id] AS "Statistic ID",
[s].[name] AS "Statistic",
[sp].[last_updated] AS "Last Updated",
[sp].[rows],
[sp].[rows_sampled],
[sp].[unfiltered_rows],
[sp].[modification_counter] AS "Modifications",
'UPDATE Statistics ' + OBJECT_NAME([sp].[object_id]) + ' ( ' + [s].[name] + ')' + ' with SAMPLE 30 percent'
FROM [sys].[stats] AS [s]
OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp]
WHERE [s].[object_id] = OBJECT_ID(N'[dbo].[Errorlog]');
go
execute the command
UPDATE Statistics ErrorLog ( PK_ErrorLog_ErrorLogID) with SAMPLE 30 percent
you will get below error.
Msg 35337, Level 16, State 1, Line 23
UPDATE STATISTICS failed because statistics cannot be updated on a columnstore index. UPDATE STATISTICS is valid only when used with the STATS_STREAM option.
Resolution:-
In order to resolve this issue:-
We have to export that table statistics to a temp table. That table looks like this. It matches the output of
DBCC SHOW_STATISTICS WITH HISTOGRAM. if exist drop # table
1.
drop table #stats_with_stream
go
2. Create a #table #stats_with_stream
CREATE TABLE #stats_with_stream
(
stream VARBINARY(MAX) NOT NULL
, rows INT NOT NULL
, pages INT NOT NULL
);
go
3. Insert stream, rows and number of pages to #stats_with_stream
EXEC ('DBCC SHOW_STATISTICS (N''Adventureworks2014.dbo.[Errorlog]'',PK_ErrorLog_ErrorLogID )
WITH STATS_STREAM,NO_INFOMSGS');
4. retrieve and check #stats_with_stream
select * from #stats_with_stream
5. The final step is to create the SQL that updates the statistics of our target table, and then execute it.
DECLARE @sql NVARCHAR(MAX);
SET @sql = (SELECT 'UPDATE STATISTICS Adventureworks2014.dbo.Errorlog(PK_ErrorLog_ErrorLogID) WITH
STATS_STREAM = 0x' + CAST('' AS XML).value('xs:hexBinary(sql:column("stream"))',
'NVARCHAR(MAX)') FROM #stats_with_stream );
--PRINT (@sql);
EXEC (@sql);
Hence in this way, we can update statistics of that index which has clustered index.
PS:- For nonclustered column store index, we do not have to do anything.
Thanks for Reading..