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

learn more… | top users | synonyms

10
votes
2answers
3k views

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 ...
9
votes
1answer
370 views

What is the different between compression on the PK vs. on the Table?

Data compression can be set on the table: CREATE TABLE dbo.SomeTable( SomeId [bigint] NOT NULL, OtherId [bigint] NOT NULL, IsActive [bit] NOT NULL, CONSTRAINT [PK_Some] PRIMARY KEY ...
9
votes
1answer
3k views

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 ...
7
votes
2answers
1k views

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 ...
7
votes
2answers
2k views

Clustered index compression vs table compression - are they the same thing?

If I set compression (either page or row) on the clustered index of a table, is that the same as setting compression on the table? SQL Server provides options for doing both, which suggests that they ...
7
votes
2answers
2k views

PAGE compression in SQL Server effect on running queries

I am planning to apply PAGE compression to some of the big tables in my database (DW to be precise). These tables are fairly big with over 15 Billion rows. When I applied the compression in test ...
7
votes
2answers
90 views

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 ...
7
votes
1answer
181 views

Effect of changing data types with row compression enabled

The company I work for has a couple of SQL Server databases with tables containing +- 500.000.000 rows. We are running Enterprise editions of SQL Server 2008R2 and 2014. Big data types When I look ...
6
votes
1answer
2k views

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 ...
5
votes
1answer
2k views

Compress backup file using T-SQL

Is it possible to compress a backup file to .zip (or another) in a stored procedure invoked from a maintenance plan? My maintenance plan contains these steps: check DB (consistency, re_index, maybe ...
5
votes
2answers
522 views

When should tables NOT be compressed

We have two servers. One we just compressed, but I took over this little project and this guy says some tables are not good candidates. As with many others, performance is -critical-. Is it accurate ...
5
votes
1answer
1k views

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?
5
votes
2answers
1k views

TokuDB optimize table does not reclaim disk space

Was running an optimize table command against one of my tables about 11Gig in size, surprisingly used disk space grew by 2Gig immediately after optimization process finished. I would expect the ...
5
votes
1answer
560 views

SQL 2008 Merge Replication and table-level data compression

What would happen if you enable data compression (either row or page-level) on a replicated table (merge replication) where the publisher is SQL 2008 Enterprise (Supports data compression) and the ...
5
votes
1answer
173 views

How does SQL Server calculate the initial size of a (compressed) backup?

When a backup is created, SQL Server guesses(?) the a size of the intial backup file. Later on, maybe when it appends the log the size will be re-adjusted, sometimes multiple times until the final ...
5
votes
1answer
846 views

How does table compression affect SQL Server's buffer pool?

I'm curious as to how SQL Server's buffer pool is affected by compressed data tables. Is the data in memory compressed, just as it is on disk, or is it fully decompressed? If the data is stored ...
5
votes
1answer
124 views

Postgres: Is migrating old records to separate table and creating new aggregated records a good strategy to improve search speed?

I have a table with 1M+ records. New records are created and updated each day each time an event occurs for each type of event (which there are many). I frequently need to find sums across many ...
4
votes
3answers
3k views

Set Backup Compression on SQL Server 2008R2 on by default

I can't seem to find the option to set the server property that will enable compression on SQL database backup to be ON by default, so I don't have to manually set it every time. Can anyone point me ...
4
votes
1answer
891 views

Compressions vs shrinking a database

Is compression and shrinking a database in SQL Server same? If I compress a table, will it decrease query performance and DML?
4
votes
2answers
66 views

Determining the tables and impact of a large number of SPs

I'm trying to track down what is causing some response times to increase when data is compressed by page compression, when the system is not CPU bound and 95% of responses see a decrease in response ...
4
votes
1answer
759 views

GZipStream/DeflateStream compressions alternatives

I have created a simple CLR functions for compressing/decompressing NVARCHAR columns: [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)] public static SqlBinary Compress( string ...
4
votes
1answer
220 views

IO request time and less write latency

I am facing issues related to constantly growing log file due to which I am getting error. When I checked SQL Log I found below messages (error log is filled of these messages almost 90%) SQL ...
4
votes
1answer
182 views

Page compression not working?

I have created a table where I have around 100 partitions (per month), all of them are configured to use page compression. After that I'm inserting data to this table month by month. I now would ...
3
votes
2answers
2k views

Does adding page compression to a table also compress existing indexes?

I am creating a series of new tables that will have page level compression enabled. Will this affect the indexes as well, or will I have to also specify compression when creating the indexes? create ...
3
votes
2answers
129 views

will table partitioning and compression help heavily used OLTP database?

We have a very big table which is over 300gb with over half billion rows in it. This table is used for audit purpose for all critical applications. A lot of inserts are happening per seconds. Due to ...
3
votes
1answer
195 views

sp_estimate_data_compression_savings for column store compressions

Is there a way to get compressions saving for column store and column store column store archival compression? In order to check these compressions saving I have to drop all clustered and non ...
3
votes
1answer
71 views

Moving Clustered Index to new filegroup results in excessive filegrowth

I've Moved the clustered index on large tables to new filegroups to defrag, compress, and to recover space more easily from the original "primary" filegroup. Problem: A 315GB compressed Clustered ...
3
votes
2answers
302 views

SQL database backups methods-tools-procedures

Currently, I am investigating some way to more efficiently backup 4 Production SQL Databases (3 different servers/instances) and would like to know if anyone else has had the problems we are having ...
2
votes
2answers
239 views

Can I restore an uncompressed differential backup after a compressed full backup?

If we enable compression on database backups (because the database backup size is huge) but don't enable it on differential which are relatively small in size, will a restore work? Because the full ...
2
votes
3answers
4k views

Backup Compression in SQL Server 2005

I noticed that simply zipping my backup files reduces the space used by 70% My idea was to, initially, backup the databases to a local server; then, using Bacula or Cobian (I don't have a budget), ...
2
votes
2answers
470 views

Export a table and zip with password

I would like, from TSQL, to use a view to extract data from a table, output it with BCP, then use non 3rd party tools to ZIP it with a password and AES encryption. Optionnally, I could use a 3rd party ...
2
votes
1answer
190 views

Are string columns with very few different entries automatically compressed?

We have a table which contains a column with only a dozen different strings in it. Is there anything I need to do in order to get the DB to compress this information? We don't have too much disk space ...
2
votes
1answer
299 views

Why 2 allocation units during online index rebuild to different file group?

I'm in the process of rebuilding the clustered index of a big table to a different file group. I'm monitoring the rebuild process using sys.system_internals_allocation_units. I'm 3h in but not done ...
2
votes
1answer
155 views

Backup File Size Almost 50% of Database File Size with TDE Enabled

I receive a full backup of a very large database once per week from a vendor. It contains transactional data which we then load into our data warehouse. Fully restored, the database is around 707GB. ...
2
votes
1answer
119 views

Best Practice in storage allocation for compressed and partitioned tables

I have a fairly large database that basically consists of one table, this table is paritioned by month and all partitions are compressed with page compression. After the initial load of the data there ...
2
votes
2answers
1k views

Restoring database from a server that uses data compression

In our environment our production server is using MS SQL Server 2008 Enterprise Edition. We have a development and test server that runs MS SQL Server Standard Edition and the table structure between ...
2
votes
1answer
428 views

Does Windows Server File Compression slow Oracle DB performance?

I'm starting a new a new contract and I get to troubleshoot their Oracle instance... I'm more apps and sql, but I'll be trying to figure out their crashes and slow performance. Go figure. I can ...
2
votes
1answer
1k views

SQL Server 2012 Page Compression - Determine compression ratio?

We're using SQL Server 2012 and have page compression turned on for a number of our larger tables. Is there a way we can determine the compression ratio we're getting? We'd like to be able to ...
2
votes
1answer
425 views

myisampack running really slow for large DBs

I was going through compressing a bunch of archival myisam tables using myisampack It was making decent progress until it got to the most recent tables. The overall database is a set of archives for ...
2
votes
0answers
896 views

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 ...
2
votes
0answers
735 views

Understanding how sys.dm_exec_requests represents queries being rolled back

I'm not sure whether this was an actual problem or just a normal situation where I needed to wait for rollback to finish. In either case, some insight would be appreciated. Background details I ...
1
vote
2answers
13k views

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

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 ...
1
vote
2answers
550 views

T-SQL script to perform a compressed backup with maximum compression level [duplicate]

I have a database with full backup of almost 2GB. When I perform a full backup (without compression) and then compress it with winrar it become 140MB, but when I backup WITH COMPRESSION syntax the ...
1
vote
1answer
258 views

Data compression with in memory OLTP

Will having data compression enabled decrease the amount of memory used by an in memory OLTP table? If yes I assume the CPU workload will increase.
1
vote
2answers
1k views

Compressing partition - what about index?

To save space on disk I want to compress some partitions of my tables: alter table TEST_TAB move partition TEST_TAB_2014 compress for all operations; I have also indexes on this partition. Should I ...
1
vote
1answer
523 views

Which Database to choose: MySQL or Oracle

I have an application generating time series data (32 channels, 22KHz, 6 secs) once a minute. These have to be stored in a database. At the moment I'm using MySQL with InnoDB tables ...
1
vote
1answer
26 views

Are toast columns compressed also in shared_buffers?

Reading here I cannot find a clear answer: http://www.postgresql.org/docs/9.1/static/storage-toast.html I need to know if setting storage to EXTERNAL for a particular column, I can gain a little ...
1
vote
1answer
81 views

How can I create a database where entries are compressed files searchable by index? [closed]

I am trying to design a database solution for logging where the search indices are dates (days), and the values are zipped files. I am doing this because the sheer number of logs I am processing is ...
1
vote
1answer
219 views

Impact of replication compression & encryption on performance, traffic?

What is the impact of MySQL compressed-slave-protocol on replication traffic? According to High performance MySQL it can lower the traffic to a third of the same replication without the compression. ...
1
vote
1answer
3k views

Compressing Base64 Encoded Image in Database Field. Table Size too Large

Database Info Database Type - Percona MySQL. Table engine - InnoDB. I have a database table that is currently using 17GB at 400,000 rows. This size is mainly because I'm storing a 40kb base64 ...