Subscribe to this blog
Briefcase
Print
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?
Posted by David C on 4 May 2011
Hi James
What is the schema of this table, and what indexes do you have? Are your partitioned table and index(es) using the same partition scheme (i.e. are they aligned)? If not, then this might be the cause of your performance issue.