Maximize SQL Pool Performance: Deep Dive into Statistics Accuracy in Azure Synapse Analytics

Table of Contents

Accurate statistics are paramount for generating optimal query execution plans within Azure Synapse Analytics SQL pools. To ensure peak performance, it’s crucial to evaluate statistics accuracy from two distinct perspectives: the control node’s row count accuracy and the timeliness of statistics updates in relation to data modifications.

Step 1: Verify Control Node Row Count Accuracy¶

In a dedicated SQL pool environment, the control node, serving as the primary engine for distributed query plan creation, relies on up-to-date information regarding row counts across compute nodes. Discrepancies between the control node’s perception and the actual row counts on compute nodes can lead to suboptimal query plans and degraded performance.

To identify tables exhibiting row count disparities, execute the following SQL query:

SELECT objIdsWithStats.[object_id]
    ,actualRowCounts.[schema]
    ,actualRowCounts.logical_table_name
    ,statsRowCounts.stats_row_count
    ,actualRowCounts.actual_row_count
    ,row_count_difference = CASE
        WHEN actualRowCounts.actual_row_count >= statsRowCounts.stats_row_count
            THEN actualRowCounts.actual_row_count - statsRowCounts.stats_row_count
        ELSE statsRowCounts.stats_row_count - actualRowCounts.actual_row_count
        END
    ,percent_deviation_from_actual = CASE
        WHEN actualRowCounts.actual_row_count = 0
            THEN statsRowCounts.stats_row_count
        WHEN statsRowCounts.stats_row_count = 0
            THEN actualRowCounts.actual_row_count
        WHEN actualRowCounts.actual_row_count >= statsRowCounts.stats_row_count
            THEN CONVERT(NUMERIC(18, 0), CONVERT(NUMERIC(18, 2), (actualRowCounts.actual_row_count - statsRowCounts.stats_row_count)) / CONVERT(NUMERIC(18, 2), actualRowCounts.actual_row_count) * 100)
        ELSE CONVERT(NUMERIC(18, 0), CONVERT(NUMERIC(18, 2), (statsRowCounts.stats_row_count - actualRowCounts.actual_row_count)) / CONVERT(NUMERIC(18, 2), actualRowCounts.actual_row_count) * 100)
        END
    ,'UPDATE STATISTICS ' + quotename(actualRowCounts.[schema]) + '.' + quotename(actualRowCounts.logical_table_name) + ';' as update_stats_stmt
FROM (
    SELECT DISTINCT object_id
    FROM sys.stats
    WHERE stats_id > 1
    ) objIdsWithStats
LEFT JOIN (
    SELECT object_id
        ,sum(rows) AS stats_row_count
    FROM sys.partitions
    GROUP BY object_id
    ) statsRowCounts ON objIdsWithStats.object_id = statsRowCounts.object_id
LEFT JOIN (
    SELECT sm.name [schema]
        ,tb.name logical_table_name
        ,tb.object_id object_id
        ,SUM(rg.row_count) actual_row_count
    FROM sys.schemas sm
    INNER JOIN sys.tables tb ON sm.schema_id = tb.schema_id
    INNER JOIN sys.pdw_table_mappings mp ON tb.object_id = mp.object_id
    INNER JOIN sys.pdw_nodes_tables nt ON nt.name = mp.physical_name
    INNER JOIN sys.dm_pdw_nodes_db_partition_stats rg ON rg.object_id = nt.object_id
        AND rg.pdw_node_id = nt.pdw_node_id
        AND rg.distribution_id = nt.distribution_id
    INNER JOIN sys.indexes ind on tb.object_id = ind.object_id
    WHERE rg.index_id < 2 -- In case this condition removed the number of rows will gets duplicated based on the number of index.
    AND ind.type_desc IN ('CLUSTERED COLUMNSTORE', 'HEAP') -- Switch between the CCI (Column store) and HEAP, You should at least keep one value or else the total number of rows will gets duplicated based on the number of indexes.
    GROUP BY sm.name
        ,tb.name
        ,tb.object_id
    ) actualRowCounts ON objIdsWithStats.object_id = actualRowCounts.object_id

Understanding the Query:

This query works by comparing two key row counts:

• stats_row_count: This represents the row count that the control node believes the table has, based on the statistics. It’s derived from the sys.partitions system view.
• actual_row_count: This is the real row count, calculated by aggregating row counts from all compute nodes. It’s obtained by joining several system views including sys.schemas, sys.tables, sys.pdw_table_mappings, sys.pdw_nodes_tables, and sys.dm_pdw_nodes_db_partition_stats.

The query then calculates the row_count_difference and percent_deviation_from_actual to highlight tables where the discrepancy between these two counts is significant. A high percentage deviation indicates that the statistics are likely outdated and could be negatively impacting query performance.

Interpreting the Results:

The output of this query will provide a list of tables, along with the calculated row count difference and percentage deviation. Focus on tables with a high percentage deviation. These are prime candidates for statistics updates. The update_stats_stmt column conveniently provides the exact UPDATE STATISTICS command you need to execute for each table.

Why Row Count Accuracy Matters:

The SQL pool optimizer relies heavily on statistics to estimate the cost of different query execution plans. Inaccurate row counts can lead the optimizer to choose a suboptimal plan, such as:

• Incorrect Join Order: If the optimizer underestimates the size of a table, it might choose it as the inner table in a join, leading to inefficient data shuffling.
• Inappropriate Operator Selection: Statistics influence the choice between different operators (e.g., hash join vs. merge join). Wrong row counts can cause the optimizer to select a less efficient operator.
• Memory Grant Issues: The optimizer uses row counts to estimate memory requirements for query execution. Inaccurate statistics can lead to insufficient memory grants, causing spills to disk and slowing down queries.

Step 2: Verify Statistics Update Cadence¶

Beyond row count accuracy, the timeliness of statistics updates is equally critical. Data modifications, such as inserts, updates, and deletes, can rapidly render statistics obsolete. Histograms, which are a core component of statistics, capture the data distribution within columns. As data changes, these histograms become less representative of the current data, leading to inaccurate estimations.

To assess the currency of your statistics, use the following query to determine if the last update date aligns with table modification patterns:

SELECT ob.[object_id],max(sm.[name]) AS [schema_name]
    ,max(tb.[name]) AS [table_name]
    ,st.[stats_id]
    ,max(st.[name]) AS [stats_name]
    ,string_agg(co.[name], ',') AS [stats_column_names]
    ,STATS_DATE(ob.[object_id], st.[stats_id]) AS [stats_last_updated_date]
    ,'UPDATE STATISTICS ' + quotename(max(sm.[name])) + '.' + quotename(max(tb.[name])) + ';' as [update_stats_stmt]
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.[stats_id] > 1
GROUP BY ob.[object_id], st.[stats_id]
ORDER BY stats_last_updated_date

Query Breakdown:

This query retrieves information about statistics objects in your SQL pool. It focuses on:

• stats_last_updated_date: This is the crucial column, obtained using the STATS_DATE function. It indicates when the statistics for a particular table and statistic object were last updated.
• Table and Statistic Information: The query also retrieves schema name, table name, statistic name, and the columns included in each statistic. This context helps in understanding which statistics are being examined.
• update_stats_stmt: Similar to the previous query, this provides the UPDATE STATISTICS command for convenient execution.

Analyzing the Results:

Examine the stats_last_updated_date column. Consider the data modification frequency of each table.

• Frequently Modified Tables: Tables that undergo frequent inserts, updates, or deletes should have their statistics updated more often. If the stats_last_updated_date is significantly older than the last major data modification, it’s a strong indicator that statistics are outdated.
• Statically Populated Tables: For tables that are loaded once and then rarely change, less frequent statistics updates may be sufficient.

Establishing a Statistics Maintenance Schedule:

Based on the analysis from this query, you can establish a statistics maintenance schedule. For highly volatile tables, consider more frequent updates, perhaps daily or even more often depending on the rate of change. For less frequently modified tables, weekly or monthly updates might suffice.

Updating Statistics¶

After identifying tables requiring statistics updates using the preceding queries, execute the UPDATE STATISTICS statements generated in the update_stats_stmt column.

For example, to update statistics for the table MyAwesomeTable in the dbo schema, you would run:

UPDATE STATISTICS [dbo].[MyAwesomeTable];

Post-Update Verification:

Following statistics updates, it’s essential to re-run the problematic query that initially prompted the performance investigation. Compare the execution duration before and after the statistics update. In many cases, you should observe a significant improvement in query performance as the optimizer now has more accurate information to generate an efficient execution plan.

More Resources for Statistics Maintenance¶

Maintaining accurate statistics is an ongoing process. Consider incorporating statistics update procedures into your regular database maintenance routines. Azure Synapse Analytics provides options for automated statistics management, which can further streamline this critical task. Explore the official Azure Synapse Analytics documentation for advanced strategies and best practices in statistics management to ensure sustained SQL pool performance.

By proactively monitoring and updating statistics accuracy, you can unlock the full potential of your Azure Synapse Analytics SQL pools and ensure consistently fast and efficient query execution. This proactive approach is key to maximizing the return on your data warehousing investment.

---

What are your experiences with managing statistics in Azure Synapse Analytics? Share your tips and best practices in the comments below!