Red Gate forums :: View topic - Compression on monitor DB
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Monitor 2
SQL Monitor 2 forum

Compression on monitor DB

Search in SQL Monitor 2 forum
Post new topic   Reply to topic
Jump to:  
Author Message
franklink



Joined: 14 Jan 2011
Posts: 8

PostPosted: Fri Jan 14, 2011 7:41 pm    Post subject: Compression on monitor DB Reply with quote

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
Back to top
View user's profile Send private message
Chris Spencer



Joined: 29 Aug 2006
Posts: 301
Location: Red Gate - Cambridge

PostPosted: Mon Jan 17, 2011 5:11 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Chris Spencer



Joined: 29 Aug 2006
Posts: 301
Location: Red Gate - Cambridge

PostPosted: Tue Jan 18, 2011 3:22 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Chris Spencer



Joined: 29 Aug 2006
Posts: 301
Location: Red Gate - Cambridge

PostPosted: Thu Jan 20, 2011 7:09 pm    Post subject: Re: Reply with quote

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
Back to top
View user's profile Send private message
Angus C



Joined: 12 Aug 2010
Posts: 5

PostPosted: Thu Jan 20, 2011 9:01 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group