SQL Server 2008 Page Compression: Using multiple processors

re: SQL Server 2008 Page Compression: Using multiple processors

Greg Duncan — Mon, 05 May 2008 16:14:30 GMT

Great post... thank you.

But one data point I'd like to see is the time for "ALTER TABLE customer REBUILD WITH (DATA_COMPRESSION=PAGE);" (i.e. without the MAXDOP option).

Pretty much I'm looking for what is the default number of processors used, if MAXDOP is not specified? 1? 0? ?

Again thanks for this post,

Greg

re: SQL Server 2008 Page Compression: Using multiple processors

Linchi Shea — Mon, 05 May 2008 17:53:54 GMT

That data point is dependent on the sp_configure 'max degree of parallelism' setting. Basically, if you don't set MAXDOP explicitly in ALTER TABLE REBUILD WITH(), it'll use the sp_configure setting.

re: SQL Server 2008 Page Compression: Using multiple processors

Greg Duncan — Tue, 06 May 2008 14:05:15 GMT

Got it.... Thank you.

re: SQL Server 2008 Page Compression: Using multiple processors

TheSQLGuru — Tue, 06 May 2008 17:49:25 GMT

Seems like it will be a Best Practice to limit the number of CPUs allowed if the system is active to prevent all CPUs from getting hammered. Looks like between 2 and 1/2 available would be appropriate depending on the I/O capabilities of the server.

SQL Server 2008 Page Compression: Compression ratios with real-world databases

Linchi Shea — Sun, 11 May 2008 23:37:27 GMT

In my previous post on data compression, I looked at how rebuilding a table with page compression works

SQL Server 2008 Page Compression: Performance impact on table scans

Linchi Shea — Sat, 17 May 2008 01:38:16 GMT

My previous post shows that data compression may not come for free, although hopefully by the RTM time

re: SQL Server 2008 Page Compression: Using multiple processors

Mike H — Mon, 19 May 2008 17:39:05 GMT

What affect, if any will compression have when taken into account with the overly large size of hard drives when trying to optimize I/O. Do you need to re-evaluate your stripe size when dealing with compressed databases?

i.e. Ten 15,000rpm rpm drives in a Raid 0 array. You have a 150gb database, and the drives are 250gb, or even 500gb each.

Could we actually loose performance if our 150gb database now only uses 100gb on the same array? Would we want to adjust our stripe size to improve the allocation of our databases across the disks?

A year in review, The 31 best blog posts on SQLBlog for 2008

Denis Gobo — Wed, 31 Dec 2008 15:37:56 GMT

Wow, it has been already a year since I wrote A year in review, The 21 + 1 best blog posts on SQLBlog

re: SQL Server 2008 Page Compression: Using multiple processors

Eric — Tue, 23 Nov 2010 16:19:16 GMT

If you want an estimate_data_compression_savings on ALL tables within a db a the same time!!!!

---------------------------------------------------------

use [your_database_name]

--drop table #toto;

create table #toto(

object_name varchar(60),

schema_name varchar(60),

index_id integer,

partition_number integer,

[size_with_current_compression_setting(KB)] integer,

[size_with_resquested_compression_setting(KB)] integer,

[sample_size_with_current_compression_setting(KB)] integer,

[sample_size_with_requested_compression_setting(KB)] integer );

exec sp_MSforeachtable

@command1 = 'Declare @tableName Varchar(63) = SUBSTRING(''?'', 8, LEN(''?'') - 8);

insert into #toto exec sp_estimate_data_compression_savings ''dbo'', @tableName, NULL, NULL, ''PAGE'';';

select a.*, ROUND((1 -

[sample_size_with_requested_compression_setting(KB)] / CASE [sample_size_with_current_compression_setting(KB)] WHEN 0 THEN 1.0 ELSE Convert(float,

[sample_size_with_current_compression_setting(KB)]) END) * 100.0, 2) AS '% freed' from #toto as a order by 1,2,3,4;