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

My Partitioning And Compression Quest!

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:

  1. Utilizing different SAN arrays (RAID 10 and RAID 50)
  2. Index Maintenance
  3. Decreasing used disk space
  4. Lower logical/physical reads
  5. Faster query execution
  6. 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?

Comments

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.

Leave a Comment

Please register or log in to leave a comment.