How can we improve Microsoft Azure SQL Data Warehouse ?

Automatic Generation and Updating of table statistic

It would be great if table statistics were automatically created and updated in Azure Data Warehouse.

41 votes
Sign in
Sign in with: oidc
Signed in as (Sign out)

We’ll send you updates on this idea

Nathan Land shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →


Sign in
Sign in with: oidc
Signed in as (Sign out)
  • Sajjan commented  ·   ·  Flag as inappropriate

    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
    DROP TABLE #stats_ddl;
    CREATE TABLE #stats_ddl
    WITH ( DISTRIBUTION = HASH([seq_nmbr])
    Select 'UPDATE STATISTICS ' + [schema_name] + '.'+ [table_name] +'(' + [stats_name] + ');' As update_stat_ddl, ROW_NUMBER()
    OVER(ORDER BY (SELECT NULL)) AS [seq_nmbr]
    from (
    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]
    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
    SET @s=(SELECT update_stat_ddl FROM #stats_ddl WHERE seq_nmbr = @i);

    PRINT @s
    EXEC sp_executesql @s
    SET @i+=1;

    DROP TABLE #stats_ddl;

  • SQLmojoe commented  ·   ·  Flag as inappropriate

    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?

Feedback and Knowledge Base