Does creating or updating statistics, assuming on a large table, create significant (or at all) transaction log churn? I would think not as it does not change the underlying data or structure.
If it does, what would be a good method to verify?
Add a comment
|
1 Answer
It doesn't really in any measurable fashion
Statistics are stored as histograms, with at most 200 entries per index/column. This is trivial, compared to the actual column or index.
See https://www.simple-talk.com/sql/performance/sql-server-statistics-questions-we-were-too-shy-to-ask/
Where are the statistics actually stored? Do they take up much space? Can I save space by only having the essential ones?
The statistics themselves are stored within your database in a series of internal tables that include sysindexes. You can view some of the information about them using the system views sys.stats and sys.indexes, but the most detail is gleaned using a function, DBCC SHOW_STATISTICS. The statistics themselves take very little space. The header is a single row of information. The density is a set of rows with only three columns, equal in the number of rows to the number of columns defining the key columns of the statistic. Then you have the histogram. The histogram is up to 200 rows and never exceeds that amount. This means statistics do not require much room at all. While you can save a little bit of space by removing unneeded statistics, the space savings are too small to ever be worthwhile .
Because the system tables are updated, then there is some minor data change that will be logged, but as I said it is trivial and not worth considering at all
