Hot answers tagged compression
8
In SQL Server, shrinking a database/datafile and compression are not the same. Shrinking a file (which, by the way, isn't really recommended) is the process of removing unused space from data files in your database. When files are created, SQL Server "reserves" space by sizing files out (depending on how the file is created), even if it doesn't actually ...
7
From: http://msdn.microsoft.com/en-us/library/cc280449%28v=sql.105%29.aspx
The compression setting of a table is not automatically applied to its nonclustered indexes. Each index must be set individually.
6
The data you are looking to compress is that sent over the wire via TDS. There is some minor compression here but nowhere near the type of compression you get with page/row compression, backup compression or ColumnStore compression.
It has been asked for before:
...
6
This is really a question for the DBA site but yes, it will work. Compression is transparent when restoring, and the RESTORE command doesn't need any parameter to tell it that a backup is compressed.
Of course you can easily test this yourself.
5
You can also take a look at this whitepaper written by the SQL CAT team. Take note of who all reviewed that document too, it is very well written.
The whitepaper will explain that since we are talking about data compression there is some data that will compress better than other. I believe the section on Application Workload has information for some of your ...
5
Offline ALTER ... REBUILD takes a big fat schema modification lock on the table with absolutely 0 concurrency (not even dirty reads can scan the table). Online ALTER ... REBUILD, as the name suggests, allows for any concurrent query or DML operation. The MSDN article How Online Index Operations Work describes the three phases (prepare, build and ...
4
I tested the scenario by running
ALTER TABLE test_tbl REBUILD WITH (DATA_COMPRESSION=PAGE,ONLINE=ON)
In a Transaction, it takes exclusive locks on the pages, so yes there is potential for blocking, however it will be very less for a big table since it compresses page-by-page, so only large full-table-scan selects can get blocked, not inserts or updates, ...
3
Which db to choose? I would choose PostgreSQL having worked with MySQL and PostgreSQL but it is worth noting how different the databases are. I will say I have frequently been impressed (and only rarely disappointed) by what sort of abuse I can throw at PostgreSQL only to watch things be handled gracefully.
In your specific case, however, there may be ...
3
How Online Index Operations Work:
Temporary mapping index
Online index operations that create, drop, or rebuild a clustered
index also require a temporary mapping index. This temporary index is
used by concurrent transactions to determine which records to delete
in the new indexes that are being built when rows in the underlying
table are ...
3
Backup compression was introduced in SQL 2008 Enterprise, and in SQL2008R2 and later, added to Standard Edition.
When creating a backup, you can specify the WITH COMPRESSION keyword, which will ensure that the database backup size is compressed to approximately a similar size as a zipped 'normal' backup file.
For SQL2005 or older, the best way really ...
3
Just my 2cents from my own experiments on 1-2 year old hardware:
Read-only operations (DW-style scans, sorts etc) on page-compressed tables (~80rows/page) I've found to break-even at compression size reduction of ~ 3x.
I.e. if the tables fit into memory anyway, page compression only benefits performance if the data size has shrunk by over 3x. You scan ...
2
The first thing I generally recommend doing is introducing differential backups to the backup strategy. If the amount of data churn is moderate or low, this can work extremely well for saving space and time because you only back up changes rather than everything. This does add some complexity to restore scenarios, and you must make sure you know what ...
1
In version 10.1, the logs are internally compressed, that means that more information is stored in those files.
But if you want to compress them in order to use less space in your disks, I do not see this like a very good practice, because logs are the only part where the latest data is stored in, and if you want to do a restore, you will have to use the ...
Only top voted, non community-wiki answers of a minimum length are eligible
