Tag Info

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


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


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

Sorry to disappoint, but your boss is right on target. Some of us BI folks know about DB optimization, at least a little. =) As with any major architectural change you need to test and adapt appropriately to your unique environment, workload, servers etc. Due to heavy blocking, we can't purge data while the database is online. Table partitioning 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 ...


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

Yes, stay away from the sp_OA* OLE Automation procedures. If you want / need to handle this purely within SQL Server then you can do so using SQLCLR, which replaces the sp_OA* OLE Automation procedures. And if you aren't using xp_cmdshell for anything else, then no need to enable it just for this (although to be fair, if using SQL Agent, then a CMD step ...


2

Question 1: Though I will do in non peek hours of business but are there any chances that making these changes can corrupt my database or log file? No this cannot damage your Log file. Question 2: Does database compression mode can effect IO operation? If yes, how can I resolve it? Yes database compression affects I/O and as far as my experience ...


2

Have you tested the lz6 compression level? lz6 provides less overall compression but it has a index on values which you can pull out without going through the overhead and cost of uncompressing the whole file. I If you cannot give up any compression at all, have you tried a simple json document store DB such as Mongo? I feel using filestream in SQL ...


2

Just use pg_restore: pg_restore is a utility for restoring a PostgreSQL database from an archive created by pg_dump in one of the non-plain-text formats Using -Z5 -Fc on pg_dump does not transform the backup into a gzip format. It adjusts the compression level, but the structure of the file is still the specific custom archive format that ...


2

Yes, they are. shared_buffers is block-oriented, and stored in the same format as on-disk. This is generally good for performance, since the decompression is very fast, and the compression means that more fits in shared_buffers. The only case it might hurt is if you have enough storage to fit the whole dataset in shared_buffers uncompressed too.


1

For the portion of the question asking about compression, Dave is correct on the trade off. I would investigate adding more capacity to your system before you head down the road of compression. For the partition portion the answer is going to be it depends. Partitioning takes a lot of planning and analysis of your current queries before you can measure ...


1

it's better to use partitioning, e.g. one table per month. You can then truncate archive tables which is an instant operation which frees disk space, or move them to a tablespace on a cheaper device and/or replace them with an aggregate row in an aggregates table. The table seen by your app will usually be a view of the union of the monthly tables. You need ...


1

Below is a complete script that works as expected without the error. :setvar IsArchive "" :setvar Suffix _Partitioned CREATE SCHEMA Orders; GO CREATE SCHEMA ArchivedOrders; GO CREATE SEQUENCE Orders.NextOrderId; GO CREATE PARTITION FUNCTION OrderIdPartitionFunction(bigint) AS RANGE RIGHT FOR VALUES(); GO CREATE PARTITION SCHEME ...


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