August 20, 2014 at 2:26 am
Hello Experts,
I had done a page compression on an archive Table on my Dev Server.
And it significantly reduced the size of the table.
I have a non clustered index and a clustered index on that table
After doing a page compression on the table, if I rebuild my index wouldn't it be same as compressing individual indexes
August 20, 2014 at 2:56 am
Depends whether you specify compression in the rebuild statement.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2014 at 3:03 am
er.mayankshukla (8/20/2014)
Hello Experts,I had done a page compression on an archive Table on my Dev Server.
And it significantly reduced the size of the table.
I have a non clustered index and a clustered index on that table
After doing a page compression on the table, if I rebuild my index wouldn't it be same as compressing individual indexes
Further on Gail's answer
😎
USE [DATABASE NAME]
GO
ALTER INDEX [INDEX NAME] ON [SCHEMA].
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE) -- [OTHER OPTIONS])
GO
August 20, 2014 at 3:43 am
I am doing page level compression on table using wizard which does :
ALTER TABLE [SchemaName].[Tablename] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = PAGE
)
Hence I guess, it always rebuilds while compressing.
Since the pages will be compressed and these pages are a part of leaf level in a clustered index. Shouldn't the Clustered index be also compressed after performing table compression.
Though I agree Non Clustered Index size may not be affected by this.
Any thoughts on this ??
August 20, 2014 at 4:00 am
I have tested it.
The page compression on Table reduces the size of table as well as size of Clustered Index without any change in size to NonClustered Index.
Now My next question is If I am specifying Data Compression at the time of table creation, what it means ?
Does it mean the table will do an automatic compression without me to explicitly compressing the Table ??
August 20, 2014 at 6:22 am
Hello Experts,
Any comments for my question
August 20, 2014 at 6:24 am
er.mayankshukla (8/20/2014)
I have tested it.The page compression on Table reduces the size of table as well as size of Clustered Index without any change in size to NonClustered Index.
Now My next question is If I am specifying Data Compression at the time of table creation, what it means ?
Does it mean the table will do an automatic compression without me to explicitly compressing the Table ??
Yes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 20, 2014 at 6:28 am
Thanks,
I have searched a lot but didn't got a proper response.
If specifying Compression option in Create Table statement means an automatic compression in the table, then what will the threshold point when compression in fired.
I guess it won't compress the data after every dml operation ?
August 20, 2014 at 6:50 am
er.mayankshukla (8/20/2014)
Thanks,I have searched a lot but didn't got a proper response.
If specifying Compression option in Create Table statement means an automatic compression in the table, then what will the threshold point when compression in fired.
I guess it won't compress the data after every dml operation ?
http://technet.microsoft.com/en-us/library/dd894051%28v=sql.100%29.aspx
Manipulating Compressed Data
This section explains what happens when compressed data is changed: new rows inserted, rows deleted or updated.
Newly-Inserted Rows
With row compression, newly inserted rows are row-compressed. With page compression, a newly inserted row is row-compressed or page-compressed, depending upon the following:
The table organization: heap or clustered index
How and where the new row is inserted
Table 3 summarizes the compression state of the newly inserted rows into a compressed table.
*Table is a heap
ROW - The newly inserted row is row-compressed.
PAGE - The newly inserted row is page-compressed:
· if new row goes to an existing page with page compression
· if the new row is inserted through BULK INSERT with TABLOCK
· if the new row is inserted through INSERT INTO ... (TABLOCK) SELECT ... FROM
Otherwise, the row is row-compressed.
* Table has a Clustered index
ROW - The newly inserted row is row-compressed.
PAGE - The newly inserted row is page-compressed if new row goes to an existing page with page compression Otherwise, it is row compressed until the page fills up. Page compression is attempted before a page split.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply