Hot answers tagged

14

It's not a matter of putting compression on a primary key, but instead a matter of putting the compression on the clustered index. For SQL Server, a clustered index is organizing the table's physical structure on that index. Or, in shorter form, the clustered index is the table. This means that compressing the clustered index and compressing the table are ...


12

Both page and row compression do not compress BLOBs. Because of their size, large-value data types are sometimes stored separately from the normal row data on special purpose pages. Data compression is not available for the data that is stored separately. If you want to compress BLOBs you need to store them as VARBINARY(MAX) and apply your stream ...


10

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: ...


10

I've been doing some testing of different methods for compressing and storing MS SQL Backups (using MS SQL 2008 R2 Enterprise edition), and I'm wondering what the most effective compression algorithm is for long term storage of those backups, outside of SQL's internal compression algorithms. Since you are using SQL 2008 R2 Enterprise edition, you ...


9

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 ...


8

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? Yes. From MSDN: Use the REBUILD WITH syntax to rebuild an entire table including all the partitions in a partitioned table. If the table has a clustered index, the REBUILD option rebuilds the clustered index. SQL ...


8

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.


8

Regarding "Dynamic", the non-compressed Barracuda-only format, very little has changed from compact, mainly on how blobs (and any very dynamic fields) are stored. I have never had any issues with compact vs. dynamic, so I can safely recommend Barracuda's dynamic. Remember that Barracuda also supports old redundant and compact row formats. The article you ...


7

Technet Online says: To view or configure the backup compression default option In Object Explorer, right-click a server and select Properties. Click the Database settings node. Under Backup and restore, Compress backup shows the current setting of the backup compression default option. This setting determines the server-level default ...


6

In terms of backup compression, I did (a couple of years ago) make a comparison of the backup compression options provided by Red Gate's SQL Backup, Quests's LiteSpeed for SQL Server, and Idera's SQLSafe, benchmarking the three products. The differences in a typical backup at maximum compression was about a 5% spread between the three for time taken, and a ...


6

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 ...


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

EXEC sys.sp_configure N'backup compression default', N'1' GO RECONFIGURE WITH OVERRIDE GO See backup compression default Option in the documentation. This method can be easily included in a SQL Server setup script.


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 ...


5

SQL Server's built-in backup compression only has one level of compression (as Mark and Adrian have already said). The most likely reason that you are getting a smaller final file by compressing the uncompressed backup is that it will contain large amounts of plain text. WinRAR and other traditional compression utilities are very good at compressing plain ...


5

Try the following query. It first creates a local temporary table and then populates it with the AllocationUnitID-to-FileID associations found in sys.dm_db_database_page_allocations, an undocumented Dynamic Management Function (DMF) introduced in SQL Server 2012 (for versions prior to 2012, you can get this info from DBCC IND()). That local temp table is ...


4

As the documentation you alredy have linked states, ROW compression uses only the bytes that are needed. Once ROW compression is in use, the CPU cycles used to convert from/to int or bigint are the same: I would not worry about that. BTW, if you're not sure whether int/bigint has an impact on the database size or not (it hasn't), you can see for yourself ...


4

Looking at the CREATE TABLE definition for 2014 and scrolling down to the box headed "--Memory optimized CREATE TABLE Syntax", there is no mention of compression. Ergo, compression is not available for in-memory tables. But yes, compression uses more CPU. Our experience is that query elapsed times are still shorter because the extra compute time is more ...


4

There is no equivalent proc so what I have been doing recently is creating a copy of the table in tempdb using SELECT...INTO, eg SELECT TOP 10 PERCENT * INTO tempdb.dbo.yourTable FROM dbo.yourTable then apply the clustered columnstore. If your table is too large to fit in your tempdb (as I might expect for a datawarehouse table), transfer a percentage ...


4

COMPACT is format supported by Antilope. It stores first 768 bytes of BLOB in case its value doesn't fit in page. DYNAMIC is almost the same as COMPACT except only 20 bytes for each BLOB field is used. Benefits - more BLOB fields are possible in a record. COMPRESSED is used for compressed tables. Hence its benefits.


4

It will fail if you try to save replicate including compression, but if you set it to only "replicate the partition scheme but not compress the data on the Subscriber." The Distribution Agent does not check for down-level Subscribers when it applies scripts. If the replication of compression is selected, creating the table on down-level Subscribers will ...


4

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 ...


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, ...


4

The clustered index is the table. So, setting it on either is the same. You can validate this by comparing both cases here: SELECT OBJECT_NAME(p.object_id),* FROM sys.partitions AS p INNER Join sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id WHERE p.data_compression > 0; SQL Server also provides several different ways ...


3

No, SQL Server does not do anything like this automatically. You can achieve some of what you're after with data compression, but its highest granularity is at the page level. So you'll get some good compression in the index pages if you have an index on only this column (or at least with this column as the leading key column), but assuming the clustered ...


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

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

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.)


3

As far as I know, there is only one compression level in SQL Server and subjective observation puts it at about 30-40% of uncompressed data volume. Have seen SQL Server compressed .bak files being further compressed by 7-Zip



Only top voted, non community-wiki answers of a minimum length are eligible