Compression on monitor DB

franklinkfranklink Posts: 11
edited January 20, 2011 3:01PM in SQL Monitor Previous Versions
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

Comments

  • 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
    Test Engineer
    Red Gate
  • 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
    Test Engineer
    Red Gate
  • 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
    Chris Spencer
    Test Engineer
    Red Gate
  • 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.
Sign In or Register to comment.