Automatic Generation and Updating of table statistic
It would be great if table statistics were automatically created and updated in Azure Data Warehouse.
Nathan Land
shared this idea
Working on now. Should be out in the next few months!
5 comments
-
KFC
commented
any updates?
-
Sajjan
commented
here you go untill Microsoft not come with auto update.
CREATE PROC [DQM].[prc_sqldw_update_stats] AS
IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN;
DROP TABLE #stats_ddl;
END;
CREATE TABLE #stats_ddl
WITH ( DISTRIBUTION = HASH([seq_nmbr])
)
AS
Select 'UPDATE STATISTICS ' + [schema_name] + '.'+ [table_name] +'(' + [stats_name] + ');' As update_stat_ddl, ROW_NUMBER()
OVER(ORDER BY (SELECT NULL)) AS [seq_nmbr]
from (
SELECT
sm.[name] AS [schema_name],
tb.[name] AS [table_name],
co.[name] AS [stats_column_name],
st.[name] AS [stats_name],
STATS_DATE(st.[object_id],st.[stats_id]) AS [stats_last_updated_date]
FROM
sys.objects ob
JOIN sys.stats st
ON ob.[object_id] = st.[object_id]
JOIN sys.stats_columns sc
ON st.[stats_id] = sc.[stats_id]
AND st.[object_id] = sc.[object_id]
JOIN sys.columns co
ON sc.[column_id] = co.[column_id]
AND sc.[object_id] = co.[object_id]
JOIN sys.types ty
ON co.[user_type_id] = ty.[user_type_id]
JOIN sys.tables tb
ON co.[object_id] = tb.[object_id]
JOIN sys.schemas sm
ON tb.[schema_id] = sm.[schema_id]
where st.[name] not like 'Clu%'
--order by STATS_DATE(st.[object_id],st.[stats_id]) desc
) x;DECLARE @i INT = 1
, @t INT = (SELECT COUNT(*) FROM #stats_ddl)
, @s NVARCHAR(4000) = N''
;WHILE @i <= @t
BEGIN
SET @s=(SELECT update_stat_ddl FROM #stats_ddl WHERE seq_nmbr = @i);PRINT @s
EXEC sp_executesql @s
SET @i+=1;
ENDDROP TABLE #stats_ddl;
-
sdoubleday
commented
Can we get an ETA?
-
Chris Thompson (m1nkeh) commented
very good, keep em coming! :)
-
SQLmojoe
commented
Definitely on the radar. Given the different impact for the different default behaviors/settings, it'd be very helpful if you can express your preference and reasons. Should create be enabled by default? What about update? Sync or async by default?