Compression on monitor DB

franklink · Joined: 14 Jan 2011 Posts: 8

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 · Posted: Mon Jan 17, 2011 5:11 pm Post subject:

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 · Posted: Tue Jan 18, 2011 3:22 pm Post subject:

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 · Posted: Thu Jan 20, 2011 7:09 pm Post subject: Re:

Angus C · Joined: 12 Aug 2010 Posts: 5

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.

	All times are GMT + 1 Hour
Page 1 of 1