Tag Info

Hot answers tagged

11

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

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


9

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


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.


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

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

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


5

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


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


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


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

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

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

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


4

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


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


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

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


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


3

A scientific approach is based on routinely collecting process and procedure performance information, either using a commercial package or a more home-grown solution. You could also start completely from scratch collecting information from Profiler or Extended Events. The important thing is to capture data regularly, and to make it easily consumable (e.g. ...


3

If you know for sure that these 35 programmables are the culprits a combination of Profile/Events for elapsed time and cached plans, plus sys.dm_exec_query_stats should get you a long way to understanding where about the pain lies. @Paul's comment about recording a baseline is important, though.


3

Here are some thoughts on this: Do you know that you should get better compression on the strings that you have tested with? Have you tested those same strings by gzipping outside of .NET? Such as on Linux / CygWin -- DOS port of UNIX utilities / PHP / etc? If you have updated your system with .NET 4.5, then you are using the updated GZipStream. This is ...


3

This online compression is extra cost option. And moreover usually online compression has worse compression ratio, then offline one. It's because the 1st one work on row level while the latter one works on block level. So if you really want to spare some space in the database and your old data are mostly read-only you should use: ALTER TABLE table_name MOVE ...


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


2

Upgrade to SQL Server 2008 Enterprise and higher or SQL Server 2008 R2 Standard and higher to get it natively. Or use a 3rd party tools (as per Mark Storey-Smith's answer).


2

Negative, if you open up your indexes, right click, go to storage, and compression. Notice that it shows "none" as the compression type. Also, each partition needs to be compressed. By default the data and index files have only 1 partition though.



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