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


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


3

How does SQL Server calculate the initial size of a (compressed) backup? From BOL : For compressed backups, the size of the final backup file depends on how compressible the data is, and this is unknown before the backup operation finishes. Therefore, by default, when backing up a database using compression, the Database Engine uses a pre-allocation ...


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

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


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

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

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

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.


2

Community Wiki answer generated from comments on the question by QueueMann and Aaron Bertrand♦ Database backup files throws away any unused space in your data files in the backup process. TDE doesn't have to encrypt empty pages, and the backup process doesn't have to include the space in the backup, but they are still empty pages contributing to data file ...


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

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

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

"bytes currently in the cache" : 6009288998 (5.5 GB), "maximum bytes The number of bytes currently in wiredTiger cache. configured" : 7516192768 (7 GB), Maximum size cache is configured (by default half of physical memory on the server). "bytes read into cache" : 18380155206 (17 GB), Cumulative number of bytes that have been read into the ...



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