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


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


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

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


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


2

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

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

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


1

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


1

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.


1

Rebuild the index to write out all pages freshly in the optimal way. If you want to absolutely minimize space usage, specify ONLINE = OFF. Online operations add a small amount of per-row space overhead. MAXDOP = 1 can be good to reduce fragmentation. Not sure if that can help with space usage, though. I can't think of anything that a DOP of 1 might save. ...


1

Correct, an error will be raised when attempting to RESTORE. Same thing goes if other Enterprise features, such as table partitioning, are used. For those finding this answer in search, usage of Enterprise features are listed in sys.dm_db_persisted_sku_features. What I would recommend is to use Developer Edition in your development and testing ...


1

I like this presentation by Giuseppe Bianchi. Starting on page 5 it contains a desctiption of the TLS protocol - segment size, header size, HMAC overhead. As for the handshake, the impact on replication should be negligible. It will only occur on connection, and there may be a key exchange going on every hour, depending on the configuration. As for the ...


1

Indeed it is a bad idea put oracle datafiles in a compressed folder as it uses async random IO. eg. on http://support.microsoft.com/kb/156932 we read: One obstruction to asynchronous operation is NTFS compression. The file system driver will not access compressed files asynchronously; instead all operations are just made synchronous. If you have ...


1

I'd suggest you split this large column off into its own table. While you're there take out any other columns which are only used once in a blue moon. (The smaller your row the faster your queries will run.) I'll take your current table as MyData. Rename it to, say, MyData_Crucial. Let's call the new table MyData_Sometimes. Create this view: create ...



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