Note: of course it's easiest to just upgrade to standard or web edition, but in our case we wanted to first see if we can do something to satisfy the engineer in us and prolong our time on Express.
Compression, explanation and why?
Compression came in SQL Server 2008 Enterprise but since 2016
it's been available on all editions.
So what is it and how can it affect performance?
We all know the tradeoff of compression: it reduces size but
it requires compute to get the original data, so the same
applies here.
The table is smaller on disk but CPU is needed to
compress / decompress.
Now, we've all been instructed that IO is more expensive than
CPU in the past, so how can we leverage compression to speed things
up?
By reducing the amount of data stored on disk, compression can
significantly cut down on IO operations, which are typically slower
and more resource-intensive than CPU operations.
Our problem
Small project with little data and limited budget, so what are our
options?
- Cleanup data?
- Go on Web/Standard edition
- Migrate legacy data to other DB
- Try compression strategies
How to figure out if compression makes sense in your case?
EXEC sp_estimate_data_compression_savings [dbo], [TableName], 1,
NULL, ROW;
EXEC sp_estimate_data_compression_savings [dbo], [TableName], 1,
NULL, PAGE;
This script will estimate the compression for a specific index on a
table. It's IMPORTANT to note that the number 1 is the
primary index. Here you can choose if you want to compress indexes
or the whole table.
Example for index and table level compression:
ALTER INDEX [SomeIndex] ON [dbo].[TableName]
REBUILD WITH (DATA_COMPRESSION = PAGE);
ALTER TABLE [dbo].[SomeTable] REBUILD WITH (DATA_COMPRESSION = PAGE);
Open in new tab for full view, example of compression ratio for a table
What we noticed
Pros
- Reduced size of table meaning you can read the whole table like 20-40% faster depending on how big the compression is
- Reduce size of db and get more time on SQL Server Express
Cons
- CPU needed to compress/decompress the rows