4 ms·

In case anyone’s an SQL Server user and you have a regular app query suddenly timing out every so often on a large table which otherwise takes 10ms, you’ll prob

by plasma 5y ago

In case anyone’s an SQL Server user and you have a regular app query suddenly timing out every so often on a large table which otherwise takes 10ms, you’ll probably be experiencing the statistic updates penalty. When MSSQL decides it wants to recheck statistics for a regularly performed query, it picks the Nth “lucky” execution of your otherwise fast query to instead now update table /index statistics. To observers, this query is suddenly super slow and may even time out. Very confusing. A workaround is to enable “async statistics update”, this won’t reoccur.

pjungwir 5y ago
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
defaultname 5y ago
As a sidenote on this, for databases that have low utilization periods it's a best practice to have a daily maintenance plan that updates statistics across the database. There would have to be a large amount of database churn for statistics to become stale.

by plasma 5y ago

pjungwir 5y ago

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

defaultname 5y ago

As a sidenote on this, for databases that have low utilization periods it's a best practice to have a daily maintenance plan that updates statistics across the database. There would have to be a large amount of database churn for statistics to become stale.