November 22, 2010 at 3:01 am
Hi
I'm using SQL Server 2008 Enterprise.
I've got a huge transaction table approx 200 million rows, which is estimated to grow by this amount annually. Running a database maintance script consisting of reindex/reorganise/update statistics on this db, especially this huge table is taking hours (> 8). I have one dedicated drive (RAID 10) for the database files.
Will partitioning this table (say by quarter) improve performance although I won't be able to split the data across multiple filegroups/physical drives? Will I see an improvement in my database maintance operation? I'm new to partitioning and I'm not sure if this approach will help performance on this huge table.
Regards
Brian
Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -
November 22, 2010 at 8:08 am
If the index you are rebuilding is a partitioned index you could only rebuild the index of a specific partition (See ALTER INDEX in BOL).
If you use 2 tables instead of 1 (with the help of partitioning to move old partition to the archive table), you can use more indexes on recent data and less indexes on archived data.
All that could help you lower the time it takes to reindex.
November 23, 2010 at 1:10 am
Oliiii (11/22/2010)
If the index you are rebuilding is a partitioned index you could only rebuild the index of a specific partition (See ALTER INDEX in BOL).If you use 2 tables instead of 1 (with the help of partitioning to move old partition to the archive table), you can use more indexes on recent data and less indexes on archived data.
All that could help you lower the time it takes to reindex.
Thanks! It seems that partitioning the table and index will therefore speed up the rebuilding process.
Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply