Suggest an idea for a new or improved feature!

← Azure Synapse Analytics

Automatic Generation and Updating of table statistic

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

55 votes
Sign in
(thinking…)
Sign in with: Microsoft
Forgot password?
Create a password
Signed in as (Sign out)
Close
Close

We’ll send you updates on this idea

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

5 comments

Sign in
(thinking…)
Sign in with: Microsoft
Forgot password?
Create a password
Signed in as (Sign out)
Close
Close
Submitting...
An error occurred while saving the comment
  • 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
    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;
    END

    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?

Azure Synapse Analytics: SQL/Management

Categories

Feedback and Knowledge Base

(thinking…)
Hosted by UserVoice