Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [compression]

The name given to the process of encoding data such that it uses lesser number of bits as compared to the original representation.

There are a number of techniques to compress information, with most algorithms working to compress image, speech, music or video. Encoding the same information in lesser bits results in saving some crucial resource such as transmission bandwidth or hard-disk space.

182 questions
26
votes
4 answers

Is data retrieved from SQL Server compressed for transmission?

Is data retrieved from Microsoft SQL Server compressed? If this is controlled by the connection string, is there any simple way to tell if any particular app is using it? I'm examining analysis tools, and the volume of data can take minutes to…
Jon of All Trades
  • 5,987
  • 7
  • 48
  • 63
16
votes
2 answers

Alternative way to compress NVARCHAR(MAX)?

I am trying to compress some tables that have NVARCHAR(MAX) fields. Unfortunately, the row and the page compression do not have the desire impact (only ~100/200 MB saved for 20 GB table). Also, I am not able to apply column store and column store…
gotqn
  • 4,348
  • 11
  • 52
  • 91
16
votes
2 answers

ROW_FORMAT - COMPRESSED vs COMPACT vs DYNAMIC

In MySQL InnoDB, what is the difference between COMPRESSED, COMPACT and DYNAMIC for ROW_FORMAT? What are the benefits between each other?
Nuno
  • 829
  • 1
  • 12
  • 24
16
votes
2 answers

Compress PostgreSQL Database

I have a large PostgreSQL database over 500GB in size which is too large. Is there anyway to compress the database down to a more manageable size? I have attempted to do this with SquashFS and the database compressed down to 177GB however PostgreSQL…
MH21209
  • 163
  • 1
  • 1
  • 5
16
votes
2 answers

Compression on a heap

The follow is a paragraph from Microsoft Docs: New pages allocated in a heap as part of DML operations will not use PAGE compression until the heap is rebuilt. Rebuild the heap by removing and reapplying compression, or by creating and removing a…
user99201
15
votes
1 answer

Do SQL Server compressed indexes remain compressed on rebuild without specifying data compression?

After one rebuilds their SQL Server indexes using page compression (ALTER INDEX IX1 REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)), do subsequent rebuilds (as done by some maintenance scripts past a certain fragmentation threshold) need to…
Paul-Sebastian Manole
  • 682
  • 6
  • 12
13
votes
2 answers

Does there exist a way to determine the exact file that contains an allocation unit in a filegroup of multiple files?

I was hoping to get a granular view of which database files contained which allocation units for the various HoBTs (both aligned and non-aligned) living in a database. The query I've always used (see below) has served me well until we began creating…
swasheck
  • 10,755
  • 5
  • 48
  • 89
12
votes
2 answers

Find uncompressed size of all tables in a database

In Dynamics AX there is a caching mechanism where tables can be configured to be loaded into memory and cached. This cache is limited to a certain amount of KB to prevent memory issues. The setting I'm talking about is called entiretablecache and…
Tom V
  • 15,752
  • 7
  • 66
  • 87
11
votes
1 answer

Benefits of Barracuda and Compression

I've been reading about MySQL's file formats Antelope and Barracuda a while ago, and I wonder if I could benefit with having Barracuda and Compression. My server is currently using Antelope, as it is the default of MySQL. I've had many times issues…
Nuno
  • 829
  • 1
  • 12
  • 24
11
votes
3 answers

Is SQL Server data compression categorically good for read-only databases?

Some literature on SQL Server data compression I read state that the write cost increases to about four times what would normally be required. It also seems to imply that this is the primary downside to data compression, strongly implying that for…
孔夫子
  • 4,330
  • 3
  • 30
  • 50
11
votes
4 answers

Alternatives to Network Backup

In our environment we have some servers that are in an Always On Availability Group, and some that are standalone. We normally backup to a network share, but we have recently observed that as the databases are growing bigger the time taken is…
SQL_NoExpert
  • 1,107
  • 1
  • 21
  • 37
11
votes
2 answers

What are the compression options for storing large amounts of text in Postgres?

I need to store large amounts of text in Postgres, mainly command logs and the output of long running commands and need to know if there are some settings that could help compress the data transparently with a tool like zlib, or some other native…
vfclists
  • 1,093
  • 4
  • 14
  • 21
10
votes
2 answers

What is the most effective way to compress and store a SQL Server backup?

I've been doing some testing of different methods for compressing and storing SQL Server backups (using SQL Server 2008 R2 Enterprise edition), and I'm wondering what the most effective compression algorithm is for long term storage of those…
Sean Long
  • 2,256
  • 5
  • 23
  • 32
10
votes
1 answer

How can I see if the data in a SQL Server table is page-compressed?

This is a follow-on question to a question which I asked yesterday: Can I bulk insert into an empty page-compressed table and get full compression? The answer to that question (paraphrased from Randi Vertongen's excellent answer) is yes, but it…
Caitlin M. Shaw
  • 185
  • 1
  • 2
  • 6
10
votes
1 answer

What is the row overhead when using Page Compression?

I have created a table with 650 Numeric(19,4) columns. When I switch on Page Compression, by running ALTER TABLE fct.MyTable REBUILD WITH (DATA_COMPRESSION = PAGE); I get Msg 1975, Level 16, State 1Index 'PK_Mytable' row length exceeds the…
Henrik Staun Poulsen
  • 2,249
  • 2
  • 23
  • 41
1
2 3
…
12 13