4 ms·

Huh, I think that is happening to me. A few questions: - Does the stats update happen only for SELECT, only for DML, or both? - Any drawbacks to `async statist

Huh, I think that is happening to me. A few questions: - Does the stats update happen only for SELECT, only for DML, or both? - Any drawbacks to `async statistics update`?

plasma 5y ago
The statistics can become stale after repeated inserts, updates, and likely DML changes yes. However, the statistics are updated it seems during a SELECT if they are deemed too stale. By default, the stats are refreshed and then the query runs, but this can time out as I mentioned for large tables. The async option lets the query proceed (using old statistics) and then it updates separately in the background. Personally I’ve not seen a downside to this in practice, but you can read more at https://techcommunity.microsoft.com/t5/azure-sql/improving-concurrency-of-asynchronous-statistics-update/ba-p/1441687 https://techcommunity.microsoft.com/t5/azure-sql/improving-c...
- AdrianB1 5y ago
  SELECT queries don't trigger statistics updates. Specific table changes trigger statistic updates, not reads.
  - plasma 5y ago
    In the case of MSSQL, it can — not because SELECTs can cause stats to become stale, you are right it’s only during updates etc, but rather the SELECT happens to notice the stats are out of date due to prior writes, and have not been updated yet, and so this does trigger an update, which is the problem I mentioned. This helps clarify: “ When a query plan is compiled, if existing statistics are considered out-of-date (stale), new statistics are collected and written to the database metadata. By default, this happens synchronously with query execution, therefore the time to collect and write new statistics is added to the execution time of the query being compiled.” (From my link abode)
    - AdrianB1 5y ago
      I think that part of the article is misleading and causing confusion; statistics never became out-of-date unless changes (inserts/updates/deletes) happen without updating statistics; that happens when statistic auto-updates are turned off, changes happen and statistics are not updated, but that is case where async updates can do more harm than good: SELECT queries are supposed to be the most consistent in terms of performance, if you have random stat updates in SELECT queries this consistency is broken. Just imagine you run a small query on a 1B rows in 20ms then all of the sudden a stat update will make your query execute in 5 minutes: shoot that DBA with salt, multiple times.
AdrianB1 5y ago
The statistics are not updated on SELECT, but when: - one or more rows are added to an empty table - more than 500 rows are added to a table with less than 500 rows - more than 500 rows are added to a larger table and the number of rows added is larger than a percentage that depends on the table size (20% under 25,000 rows, 0.1% for 1 billion rows, several values in between) - when indexes are rebuilt, the associated statistic is rebuilt

Huh, I think that is happening to me. A few questions: - Does the stats update happen only for SELECT, only for DML, or both? - Any drawbacks to `async statistics update`?

plasma 5y ago

The statistics can become stale after repeated inserts, updates, and likely DML changes yes. However, the statistics are updated it seems during a SELECT if they are deemed too stale. By default, the stats are refreshed and then the query runs, but this can time out as I mentioned for large tables. The async option lets the query proceed (using old statistics) and then it updates separately in the background. Personally I’ve not seen a downside to this in practice, but you can read more at https://techcommunity.microsoft.com/t5/azure-sql/improving-concurrency-of-asynchronous-statistics-update/ba-p/1441687 https://techcommunity.microsoft.com/t5/azure-sql/improving-c...

AdrianB1 5y ago
SELECT queries don't trigger statistics updates. Specific table changes trigger statistic updates, not reads.
- plasma 5y ago
  In the case of MSSQL, it can — not because SELECTs can cause stats to become stale, you are right it’s only during updates etc, but rather the SELECT happens to notice the stats are out of date due to prior writes, and have not been updated yet, and so this does trigger an update, which is the problem I mentioned. This helps clarify: “ When a query plan is compiled, if existing statistics are considered out-of-date (stale), new statistics are collected and written to the database metadata. By default, this happens synchronously with query execution, therefore the time to collect and write new statistics is added to the execution time of the query being compiled.” (From my link abode)
  - AdrianB1 5y ago
    I think that part of the article is misleading and causing confusion; statistics never became out-of-date unless changes (inserts/updates/deletes) happen without updating statistics; that happens when statistic auto-updates are turned off, changes happen and statistics are not updated, but that is case where async updates can do more harm than good: SELECT queries are supposed to be the most consistent in terms of performance, if you have random stat updates in SELECT queries this consistency is broken. Just imagine you run a small query on a 1B rows in 20ms then all of the sudden a stat update will make your query execute in 5 minutes: shoot that DBA with salt, multiple times.

AdrianB1 5y ago

The statistics are not updated on SELECT, but when: - one or more rows are added to an empty table - more than 500 rows are added to a table with less than 500 rows - more than 500 rows are added to a larger table and the number of rows added is larger than a percentage that depends on the table size (20% under 25,000 rows, 0.1% for 1 billion rows, several values in between) - when indexes are rebuilt, the associated statistic is rebuilt