Do I need to update statistics after compressing table?

Question

Do I need to update statistics after compressing table?

VoldemarG

Hall of Fame

Points: 3883
More actions
April 7, 2020 at 12:22 am

#3740921

I page-compressed a table with one Clustered and 4 non-clustered indexes, 5 million rows. Do I need to do any update statistics , rebuild, etc. ? Before the table is fully ready for production operations. Or is it ready to use right after compression?

Likes to play Chess

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

VoldemarG wrote:

I page-compressed a table with one Clustered and 4 non-clustered indexes, 5 million rows. Do I need to do any update statistics , rebuild, etc. ? Before the table is fully ready for production operations. Or is it ready to use right after compression?

How did you do this? Usually I'd disable the nonclustered indexes, rebuild the clustered index as compressed, then rebuild the nonclustered indexes as compressed. Since all indexes are rebuilt, no stats update is necessary.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

VoldemarG Hall of Fame Points: 3883 More actions · Answer 2

I simply did it in one of these two ways: (isn't it the same as compression Clustered and non-clustered indexes separately? I thought the below statements do that)

create table tab74 (c1 int primary key clustered, c2 int) with (data_compression = page)

OR

--Compression can be added by ALTER TABLE, too (same result? ) :

/* ALTER TABLE tab73 REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW);

GO

ALTER TABLE tab74 REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);

GO

*/

I really appreciate your input on this.

Likes to play Chess

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

If you're creating a table from scratch and adding indexes to that table, then you won't have to worry about stats from a compression change point of view because they'll be fresh when the empty table is built. Once you've added data to that table, then the usual rules covering stats updates should be observed. As far as I know, page/row/no compression makes no difference to stats.

If you're changing an existing table from uncompressed to compressed, then you have to rebuild the indexes - including the clustered index. This will of course update stats.

If you're working with a partitioned table which already contains data then as you've shown, the data compression commands are slightly different but the same rules apply. You can also change the data compression type at the partition level.

This reply was modified 4 years, 7 months ago by ChrisM@Work. Reason: Added comment about partition-level changes

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden