May 28, 2019 at 1:16 pm
How do you UN-COMPRESS SQL Server compressed tables and indexes? (we have approx 28 TB compressed down to 7 TB -- on 1 large SQL Server 2014 instance w/ a backend EMC VNX 5300 housing the data)
Over the last 3 years, we've been compressing LARGE tables and indexes on a SQL Server 2014 Enterprise instance.
Now, we've been advised by our storage team that they are installing a new PURE STORAGE ARRAY which recommends SQL Server data and indexes be uncompressed --- as PURE apparently does a much better job at compressing the stored data.
Thoughts on the most efficient way to UN-COMPRESS our data?
May 28, 2019 at 1:35 pm
The answer is simple, just alter the table and set compression = NONE
😎
Quick question, have you seen any evidence supporting this claim? "Better at the job" does not necessarily mean better performance 😉
May 28, 2019 at 1:40 pm
thanks! that worked perfectly. My test results:
To decompress/uncompress a table, I simply changed the DATA_COMPRESSION attribute from PAGE back to NONE:
-- to Compress:
--
USE [my_dbname]
ALTER TABLE [dbo].[my_tablename] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
GO
-- to Un-Compress:
--
USE [my_dbname]
ALTER TABLE [dbo].[my_tablename] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE)
GO
May 28, 2019 at 1:44 pm
Good stuff and thanks for the feedback!
😎
Has the storage team provided any evidence on PURE STORAGE ARRAY efficiency and edge over the native SQL Server data compression? Would be very interested in seeing that!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply