Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.