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?
