Compression on monitor DB

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

Compression on monitor DB

Postby franklink » Fri Jan 14, 2011 7:41 pm

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
franklink
 
Posts: 8
Joined: Fri Jan 14, 2011 7:36 pm

Postby Chris Spencer » Mon Jan 17, 2011 5:11 pm

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
Chris Spencer
 
Posts: 301
Joined: Tue Aug 29, 2006 9:09 am
Location: Red Gate - Cambridge

Postby Chris Spencer » Tue Jan 18, 2011 3:22 pm

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
Chris Spencer
 
Posts: 301
Joined: Tue Aug 29, 2006 9:09 am
Location: Red Gate - Cambridge

Re:

Postby Chris Spencer » Thu Jan 20, 2011 7:09 pm

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
Chris Spencer
Test Engineer
Red Gate
Chris Spencer
 
Posts: 301
Joined: Tue Aug 29, 2006 9:09 am
Location: Red Gate - Cambridge

Postby Angus C » Thu Jan 20, 2011 9:01 pm

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.
Angus C
 
Posts: 5
Joined: Thu Aug 12, 2010 11:19 am


Return to SQL Monitor 2

Who is online

Users browsing this forum: No registered users and 0 guests

cron