Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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):

Query Description CPU Elapsed Scan Logical Physical Read-Ahead Query Cost Execution Count
Query1 No Change 0 16 1 5 2 2 7%
Query1 Partitioned By Year (Page) and Month (Row) 0 154 15 23 14 32 79%
Query1 Page 0 28 1 4 3 8 6%
Query1 Row 0 19 1 5 3 2 8%
Query2 No Change 0 18 1 6 2 3 7% 11,077
Query2 Partitioned By Year (Page) and Month (Row) 0 30 15 24 14 32 79% 11,077
Query2 Page 0 19 1 5 3 2 6% 11,077
Query2 Row 0 19 1 5 3 2 8% 11,077
Query3 No Change 496 1648 410 1892 448 2545 24% 2
Query3 Partitioned By Year (Page) and Month (Row) 265 6514 6150 9540 1320 8880 27% 2
Query3 Page 93 1987 410 1739 493 2519 24% 2
Query3 Row 204 2412 410 1826 588 2455 24% 2
Query4 No Change 5955 1744 25 78674 476 78249 36% 20
Query4 Partitioned By Year (Page) and Month (Row) 8392 3331 27 32744 209 32261 20% 20
Query4 Page 14480 896 25 31532 264 30918 16% 20
Query4 Row 7787 1399 25 62282 591 61217 29% 20
Query5 No Change 3976 1737 25 80042 484 78273 35% 4,053
Query5 Partitioned By Year (Page) and Month (Row) 6083 2662 34 34116 224 32261 22% 4,053
Query5 Page 8442 751 25 32904 296 30918 15% 4,053
Query5 Row 5348 1352 25 63642 618 61216 28% 4,053
Query6 No Change 0 13 0 3 2 0 6% 1,594,377
Query6 Partitioned By Year (Page) and Month (Row) 0 161 15 17 14 0 83% 1,594,377
Query6 Page 0 26 0 3 3 0 6% 1,594,377
Query6 Row 0 22 0 3 3 0 6% 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?

Comments

Posted by Jason Brimhall on 5 May 2011

Thanks for sharing with us.

Posted by David C on 9 May 2011

Hi James,

No problem. I obviously don't know what types of queries you're testing with, but I tried creating 4 versions of your table; 1 normal table, 1 partitioned, 1 compressed (ROW), and 1 partitioned and compressed (ROW). I then populated each table with the same 100,000 rows of dummy data.

My partition function was created on your [time] column, and was partitioned by calendar year. I then added the same clustered primary key that you specified to each table.

On the partitioned tables I created them using the partition function, and obviously compressed them for the compressed tables.  I used ROW compression for no reason other than its what you said you thought performed better in your scenario.

I ran several simple queries (mostly performing range searches) and found the I/O's consistantly much lower for the partitioned & compressed table (as I would've guessed). The partitioned table was slightly higher (very marginally), with the compressed table next best at about 10% better than the normal (non-partitioned/non-compressed) table.  So in other words I got very different results to you, which is interesting.  I've got the demo setup scripts available if you'd like them.

Cheers

Dave

Posted by dajonx on 10 May 2011

Thank you David!  That is quite interesting because I thought the partitioned and compressed table would have performed better as well.  

Can you please send me the demo scripts that you've used to try out in my environment?  

Again, thank you very much for doing all of the work to validate my results.

Posted by dajonx on 10 May 2011

Oops, my email address is dajonx@gmail.com.

Leave a Comment

Please register or log in to leave a comment.