So I have a pretty big database (around 1 TB) that’s getting a bit out of
hand. I inherited it and have been doing things to improve it ever since I
started this new job. And boy, there’s been some changes since I first
started. Let’s just say that this database didn’t follow the common best
practices.
So now, I’m to the point of considering partitioning and compression for the
really large tables for the following reasons:
- Utilizing different SAN arrays (RAID 10 and RAID 50)
- Index Maintenance
- Decreasing used disk space
- Lower logical/physical reads
- Faster query execution
- Archiving older, less used data
These are my results:
| Query | Description | CPU | Elapsed | Scan | Logical | Physical | Read-Ahead | Query Cost | Execution Count |
| Query1 | No Change | 0 | 25 | 1 | 5 | 3 | 2 | 9% | |
| Query1 | Partitioned By Year (Page) and Month (Row) | 0 | 157 | 15 | 26 | 15 | 24 | 77% | |
| Query1 | Page | 0 | 24 | 1 | 4 | 3 | 8 | 6% | |
| Query1 | Row | 0 | 18 | 1 | 5 | 3 | 2 | 8% | |
| Query2 | No Change | 0 | 21 | 1 | 6 | 3 | 3 | 9% | 11,077 |
| Query2 | Partitioned By Year (Page) and Month (Row) | 0 | 70 | 15 | 27 | 15 | 24 | 77% | 11,077 |
| Query2 | Page | 0 | 23 | 1 | 5 | 3 | 2 | 6% | 11,077 |
| Query2 | Row | 0 | 25 | 1 | 5 | 3 | 2 | 8% | 11,077 |
| Query3 | No Change | 95 | 3462 | 410 | 1892 | 617 | 2428 | 24% | 2 |
| Query3 | Partitioned By Year (Page) and Month (Row) | 389 | 4067 | 6150 | 10770 | 1324 | 8915 | 27% | 2 |
| Query3 | Page | 512 | 2825 | 410 | 1739 | 493 | 2519 | 24% | 2 |
| Query3 | Row | 62 | 3171 | 410 | 1826 | 588 | 2455 | 24% | 2 |
| Query4 | No Change | 5891 | 1822 | 25 | 79305 | 644 | 78308 | 36% | 20 |
| Query4 | Partitioned By Year (Page) and Month (Row) | 8533 | 1730 | 37 | 32895 | 260 | 32262 | 19% | 20 |
| Query4 | Page | 13401 | 1045 | 25 | 31533 | 284 | 30926 | 15% | 20 |
| Query4 | Row | 8069 | 1507 | 25 | 62215 | 573 | 61218 | 28% | 20 |
| Query5 | No Change | 4086 | 1881 | 25 | 80633 | 702 | 78308 | 35% | 4,053 |
| Query5 | Partitioned By Year (Page) and Month (Row) | 6147 | 1398 | 31 | 34310 | 309 | 32270 | 22% | 4,053 |
| Query5 | Page | 8455 | 851 | 25 | 32905 | 300 | 30926 | 15% | 4,053 |
| Query5 | Row | 5224 | 1571 | 25 | 63535 | 574 | 61218 | 28% | 4,053 |
| Query6 | No Change | 0 | 33 | 0 | 3 | 3 | 0 | 6% | 1,594,377 |
| Query6 | Partitioned By Year (Page) and Month (Row) | 0 | 183 | 15 | 19 | 15 | 0 | 83% | 1,594,377 |
| Query6 | Page | 0 | 20 | 0 | 3 | 3 | 0 | 6% | 1,594,377 |
| Query6 | Row | 0 | 17 | 0 | 3 | 3 | 0 | 6% | 1,594,377 |
What puzzles me is that the partitioned results are MUCH higher than if it
wasn’t partitioned at all. I thought that since the queries were using the
partitioning column used in the partition function, that SQL Server would “know”
which partition the data resides on and seek only that partition thus seeking a
smaller data set. Does anyone know why this isn’t the case in my testing?
I’m leaning on row compression for this table. What do you guys think?
