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


9

Try the following query. It first creates a local temporary table and then populates it with the AllocationUnitID-to-FileID associations found in sys.dm_db_database_page_allocations, an undocumented Dynamic Management Function (DMF) introduced in SQL Server 2012 (for versions prior to 2012, you can get this info from DBCC IND()). That local temp table is ...


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

There are (now) potentially two ways to accomplish custom compression: Starting in SQL Server 2016 there are built-in functions for COMPRESS and DECOMPRESS. These functions use the GZip algorithm. Use SQLCLR to implement any algorithm you choose (as @Remus mentioned in his answer). This option is available in versions prior to SQL Server 2016, going all ...


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


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


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

No ! But, you can : 1 script our your database schema and use BCP out and bcp in method or 2 restore the database as a copy on EE or Dev edition, remove all enterprise features and backup the database and restore it on standard edition.


1

Do I have to write SQL for that manually? Yes, but it's not that hard: create table original (id integer, url text); insert into original values (1,'VeryLongURLText'), (2,'VeryLongURLText'), (3,'LoooongURLText'), (4,'LoooongURLText'), (5,'LoooongURLText'); create the dictionary create table dictionary (id serial, url text); insert into dictionary ...


1

Remus Rusanu, on May 21, 2013, provided an answer to this question: One filegroup, multiple datafiles, how to get list of tables in each file His response was: An object in a filegroup will use all datafiles in the filegroup. Any table in FG1 resides equally on Datafile1, Datafile2 and Datafile3. If you need to control placement you need to create ...


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

"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