Compression on monitor DB

franklink

My DB host for SQL Monitor is a 2008 R2 box. I know from my testing this DB can grow quite large. I would like to enable compression on the larger tables.

Does anyone have any experience/know of any issues with this?

Thanks,

Kenny

Chris Spencer

Hi Kenny

We haven't tested SQL Monitor with compression enabled so can't be 100% sure that there won't be any issues. However, I am currently rebuilding one of the larger tables on my local installation to use page compression and will let you know if there are any obvious issues.

I think that the table named data.Cluster_SqlServer_SqlProcess_UnstableSamples is a good candidate for compression. In my database it contains about 90% of the total data. Running sp_estimate_data_compression_savings for this table showed potential improvements of ~4% for row compression and ~12% for page.

An alternative is to set up a strict purge policy (especially for SQL Server data).

Regards
Chris

Chris Spencer

I've been running SQL Monitor for a while now after rebuilding the data.Cluster_SqlServer_SqlProcess_UnstableSamples table with page compression. The compression was about 12% as predicted by the sp_estimate_data_compression_savings stored proc.

General issues:

• It took 2 hours to rebuild a 90GB table
• I needed about 90GB of free disk space for the rebuild to complete successfully

SQL Monitor specific:

• My installation is monitoring the instance that hosts its data repository. This instance was reported as being offline until I restarted the SQL Monitor 2 Base Monitor service after the rebuild had completed (could well be a bug)

Otherwise everything seems to be working just fine.

Regards
Chris

Chris Spencer

Chris Spencer wrote:

I've been running SQL Monitor for a while now after rebuilding the data.Cluster_SqlServer_SqlProcess_UnstableSamples table with page compression. The compression was about 12% as predicted by the sp_estimate_data_compression_savings stored proc.

By comparison SQL Storage Compress compressed the data files by ~90% (142GB reduced to just under 14GB).

SQL Monitor appears to be running fine on this compressed repository without (as far as I can see) any performance degradation. So this seems to be another very good option.

Regards
Chris

Angus C

Hi Kenny,

First of all let me say that I work for Red Gate in the DBA sales team.

With that said, have you ever looked at SQL Storage Compress? This is a Red Gate product to reduce the storage footprint of your SQL Server databases.

Chris ran a quick test using SSC and he compressed the data file by 90% as compared to the 12% he saw with native SQL Server page compression.

I would be happy to talk to you about this product if it is of interest to you or others who look at this chain.

My email is angus.chudleigh@red-gate.com

Thanks,

Angus.