Solution to do maintenance the large table

Question

Solution to do maintenance the large table

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

Sagar-636902 (11/25/2014)
Thanks both for the Reply,it is sql server 2012 enterprise edition sp1

Then there's no reason why you couldn't delve into the useful if not slightly painful realm of using Partitioned Tables. One of the best reasons in the world to use partitioned tables is to solve the very problems that you're having with your indexes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sagar-636902 SSCommitted Points: 1651 More actions · Answer 2

great, thats the plan to do next for the partition.. quick question though since we have minimum down time and if we like create db with partition in multiple file group based on year/month, can we replicate the data with that newily created partition table to minimize the cut over period having said one production and another mirror to the database which has the partition on it. sorry for too many questions.. this things are giving so much hard time.

Thanks

Sagar

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

Sagar-636902 (11/25/2014)
great, thats the plan to do next for the partition.. quick question though since we have minimum down time and if we like create db with partition in multiple file group based on year/month, can we replicate the data with that newily created partition table to minimize the cut over period having said one production and another mirror to the database which has the partition on it. sorry for too many questions.. this things are giving so much hard time.
Thanks
Sagar

I'll admit that my table was only 384GB that I just got done with today but I wrote a proc to build 1 new partition at a time on a new table and copy just one month's worth of data to it at a time. I'll also tell you that I put that new table in a new database with 1 FileGroup per month and 1 file per FileGroup. None of that had any effect on production except to slow things down a tiny bit.

When I went to do the final month, I first copied it like I did all the other partitions. Once that was done, I set everything to single user mode, did a true up between what I just copied and what was in the source, renamed the old table, added a synonym named the same as the old table to point to the new table, and then went back to multiuser. Total "downtime" was subsecond.

After that and once you've verified that everything is working fine through the synonym, then you can drop the original (now renamed) table, set that other database to full recovery, and start taking FileGroup backups. If you set the older filegroups to READ ONLY, you only back those FileGroups up once and then you only have to worry about backing up the "current" month FileGroup, "previous" month FileGroup, and the Primary File Group (which should be damned near empty on that other database). If you have 5 years of data, your backups, index maintenance, and a whole lot more will drop to at least 1/60th of what they were before.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

BTW... I don't mean to make partitioning sound so easy. It can be a real booger to setup especially if you have a bunch of indexes that aren't temporal in nature. And, if you have any other tables pointing at the partitioned table, you could be in deep Kimchie. Like I teach in my SQL Saturday presentation on the subject, "If you think partitioning is easy, then you're doing something wrong".

Once it's setup and you have your monthly closure procs in place, then it gets easy and that's what it's all about.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sagar-636902 SSCommitted Points: 1651 More actions · Answer 5

Sagar-636902

SSCommitted

Points: 1651

November 25, 2014 at 10:13 pm

#1760969

Thanks, i will have to test on that..