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 ...
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
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.
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
When data is in the buffer pool it is compressed. The data within the row/column needs to be decompressed when the row/column is read. The storage engine team (the guys that wrote this stuff) did a blog post about this which has some good info in it. (Yes I stole the above line from the blog post.)
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
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 ...
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 ...
1
If you have any TEXT/BLOB fields in your MyISAM tables, you may just have bloated fields.
You verify this, go back to your older MyISAM tables and perform this:
SELECT LENGTH(TextOrBLOBField) FROM some_table;
Run this same query against more recent tables and see if the slowness is just O(n) (order n) running time based on the amount of recorded data.
1
Assuming you have the appropriate DBA-level permissions, you can try this to get some basic information on the SecureFile/LOBs in your system:
SELECT table_name, segment_name, index_name, securefile, in_row
FROM user_lobs;
You can get the size on disk for a specific table name (your table with a SecureFile/LOB) by doing this:
SELECT segment_name, ...
Only top voted, non community-wiki answers of a minimum length are eligible
