12

Situation

  • Data warehouse on Sql Server 2008 Enterprise
  • 36+ million row heap (don't ask), with 60+ columns
  • 750k added monthly
  • No primary key defined (I have now identified one)
  • No compression

What I'm thinking of doing (in this order)

  • Add page level compression
  • Add the PK
  • Add a number of non-clustered indexes
  • Do this as quickly as possible

Question


What I'm leaning towards at the moment

-- Add page level compression
alter table     dbo.TableName
rebuild with    (data_compression = page)
; 
go

-- Add primary key
alter table             dbo.TableName
add constraint          PK_TableName
primary key clustered   (<Columns>)
;
go

-- Add NC_IXs here
...
...

Ive looked here (PK creation documentation) and here (ALTER TABLE documentation), but can't see anything definitive about whether or not any indexes inherit table compression settings. The answer to this particular question is "No, compression is not inherited", found here on dba.stackexchange

migrated from stackoverflow.com Oct 16 '13 at 22:11

This question came from our site for professional and enthusiast programmers.

10

The clustered index is in fact the table. On the assumption that your primary key is clustered then I would create a clustered primary key with page level compression rather than trying to do it in two steps.

-- Add primary key
ALTER TABLE             dbo.TableName
ADD CONSTRAINT          PK_TableName
PRIMARY KEY CLUSTERED   (<Columns>)
WITH (DATA_COMPRESSION = PAGE)
;

I would also copy about 100k rows to a temporary (temporary physical not #temporary) table and run some tests. Try running compression first, clustered key first, try doing them as one step. See what runs fastest. I would guess it will be one step personally :).

2

Either way, page compression is going to shuffle data around a LOT. I would estimate doing the compression first would result in less overall I/O since the clustering operation will be reading compressed pages.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.