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


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.


7

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

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

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

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

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

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

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

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


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

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

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

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

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

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.


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.


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

I've had the same experience. The solution is to rebuild the table via this command: ALTER TABLE my_table ENGINE=TokuDB ROW_FORMAT=TOKUDB_SMALL; Replace TOKUDB_SMALL with your favourite compression algorithm. With InnoDB an OPTIMIZE TABLE command simply does a trivial ALTER. But in TokuDB it does not, and tablespace is not reclaimed. By forcing an ALTER ...


2

Base64 is an encoding for transferring data reliably over the wire. It usually increases a files size so I wouldn't look to store a file in this format. BLOB The first thing I would do is decode the Base64 back to the original file and store it as a BLOB field. This could shrink the files by a third. Table design You said each row has a 40kb image. Why are ...


2

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



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