I have five hot tables to partition (total around 1.8 TB worth of data) two of the tables contain around 8 billion rows. I have tested partitioning on my test server and it will take a few weeks to partition the warehouse tables. I was wondering could I run partitioning scripts on two tables or multiple tables at the same time to quicken things up or will it take the same amount of time when running the scripts one after the one. I am creating monthly partitions for the past three years and then yearly partitions which I plan to switch into archive database. I know the warehouse log is going to grow very rapidly too. Any advice would be great at this stage.. thanks
In this case, the I/O thoughput is the critical factor, or better: have you spread your datafiles on multiple disks? If these are on the same disks, you'll probably get IO contention when running multiple scripts at the same time. But I assume 1.8TB is stored on a SAN with a RAID level? In that case, you'll have to measure how fast partioning scripts works when running a single script, when running 2 scripts etc. Make sure you also put an eye on waitstats, in order to determine IO bottlenecks.
,Hello. Thank you for your response. We are working on a virtual server environment. I have done further testing and dropping the clustered indexes and then recreating them on as newly partition scheme seems to be a much better quicker option than using the split range option and doesn't use as much log space. Have you any thoughts on this method? Thanks