Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

update statistics of column store index

Avatar of marrowyung
marrowyung asked on
Microsoft SQL ServerSQL* SQL SERVER 2017* Microsoft SQL Server 2019
31 Comments1 Solution50 ViewsLast Modified:
right now we are receiving a job failure message from an index statistics job:

UPDATE STATISTICS [attachment][_dta_index_attachment_186_2004202190__col__]
Msg 35337, Level 16, State 1, Line 6
UPDATE STATISTICS failed because statistics cannot be updated on a columnstore index.  UPDATE STATISTICS is valid only when used with the STATS_STREAM option.

Completion time: 2021-03-17T09:57:28.7312610+00:00


and the statistics update job we use is:

SET NOCOUNT ON 
DECLARE @columnname VARCHAR(MAX) 
DECLARE @tablename SYSNAME 
DECLARE @statsname SYSNAME 
DECLARE @sql NVARCHAR(4000) 
DECLARE @NAME VARCHAR(MAX) 
declare @i INT 
declare @j INT 
create table #temp 
( 
tablename varchar(1000), 
statsname varchar(1000), 
columnname varchar(1000) 
) 
insert #temp(tablename,statsname,columnname) 
SELECT DISTINCT 
OBJECT_NAME(s.[object_id]), 
s.name AS StatName, 
COALESCE(@NAME+ ', ', '')+c.name 
FROM sys.stats s JOIN sys.stats_columns sc ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id 
JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id 
JOIN INFORMATION_SCHEMA.COLUMNS D ON D.[COLUMN_NAME]= C.[NAME] 
JOIN sys.partitions par ON par.[object_id] = s.[object_id] 
JOIN sys.objects obj ON par.[object_id] = obj.[object_id] 
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1 
create table #temp1 
( 
id int identity(1,1), 
tablename varchar(8000), 
statsname varchar(8000), 
columnname varchar(8000) 
) 
insert #temp1(tablename,statsname,columnname) 
select tablename,statsname,stuff( 
( 
    select ','+ [columnname] from #temp where 
statsname = t.statsname for XML path('') 
),1,1,'') 
from (select distinct tablename,statsname from #temp )t 
SELECT @i=1 
SELECT @j=MAX(ID) FROM #temp1 
WHILE(@I<=@J) 
BEGIN 
SELECT @statsname = statsname from #temp1 where id = @i 
SELECT @tablename = tablename from #temp1 where id = @i 
SELECT @columnname = columnname from #temp1 where id = @i 
SET @sql = N'UPDATE STATISTICS '+QUOTENAME(@tablename)+QUOTENAME(@statsname) 
PRINT @sql 
EXEC sp_executesql @sql 
SET @i = @i+1 
END 
DROP TABLE #temp 
DROP TABLE #temp1 


any way to modify the script a bit to also update statistic for column store index?


ASKER CERTIFIED SOLUTION
Avatar of pcelba
pcelbaFlag of Czechia image

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 31 Comments.
See Answers