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?

Rate

Share

Share

Rate