Blog Post

My Partitioning And Compression Quest! (Continued)

,

Thank you, David C, for your comment!  Please see below to the answers to

your questions.

The table structure is:

CREATE TABLE [dbo].[Table_NoChange] (
    [ID] INT NOT NULL,
    [Time]

SMALLDATETIME NOT NULL,
    [Currency] SMALLMONEY,
    [Flag]

SMALLINT,
    [Currency_Max] SMALLMONEY,
    [Note] VARCHAR(500))

There is only one index:

ALTER TABLE [dbo].[Table_NoChange] ADD CONSTRAINT [PK_Table_NOCHANGE] PRIMARY

KEY CLUSTERED ([ID], [Time])

Originally, I did not create the table using the same partition scheme as the

index.  Thank you for pointing that out.  I have made the update to the

partitioning (added ON PS_Table([Time]) to the end of the table creation

statement).  The index creation statement already had the ON PS_Table ([Time])

option.

These are my results (Executed DBCC DROPCLEANBUFFERS before each Query

script):

QueryDescriptionCPUElapsedScanLogicalPhysicalRead-AheadQuery CostExecution Count
Query1No Change01615227%
Query1Partitioned By Year (Page) and Month (Row)01541523143279%
Query1Page02814386%
Query1Row01915328%
Query2No Change01816237%11,077
Query2Partitioned By Year (Page) and Month (Row)0301524143279%11,077
Query2Page01915326%11,077
Query2Row01915328%11,077
Query3No Change49616484101892448254524%2
Query3Partitioned By Year (Page) and Month (Row)2656514615095401320888027%2
Query3Page9319874101739493251924%2
Query3Row20424124101826588245524%2
Query4No Change5955174425786744767824936%20
Query4Partitioned By Year (Page) and Month (Row)8392333127327442093226120%20
Query4Page1448089625315322643091816%20
Query4Row7787139925622825916121729%20
Query5No Change3976173725800424847827335%4,053
Query5Partitioned By Year (Page) and Month (Row)6083266234341162243226122%4,053
Query5Page844275125329042963091815%4,053
Query5Row5348135225636426186121628%4,053
Query6No Change01303206%1,594,377
Query6Partitioned By Year (Page) and Month (Row)0161151714083%1,594,377
Query6Page02603306%1,594,377
Query6Row02203306%1,594,377

Testing led me to think that Row Compression is still best for this table. 

Partitioning continues to be more costly to implement. 

To those kind enough to leave me their input, would you agree with my

conclusion?  Do you have any other thoughts/observations from my testing?

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating