February 10, 2012 at 4:55 pm
Hello,
I often need to spend a lot of time to build mechanisms to manage partitioned tables. I find myself writing custom code to manage file groups, transfer data in and out of partition, automating the adding of partitions, building a sliding window functionality, etc...
How do you manage partitioned tables? Do you have a way to ease the coding burden?
February 13, 2012 at 9:01 am
I probably do much as you do. I do have server dashboards which run in SSRS so I have reports which provide info on partitioned ( and compressed ) tables/indexes.
I handle sliding window stuff with agent jobs.
Actually I'd say the most difficult part is the fact that by default SSMS does not display partitioning information and devs keep creating non partitioned indexes ( on partitioned tables ) which screws stuff up for me!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 13, 2012 at 4:46 pm
I like your approach with SSRS reports. I am just using a set of views at the moment.
Did you end up building yourself a set of generic procedures to manage your partitioned tables or do you just have custom code for each table? It bothers me to have custom code for each table but I'm not sure that the time invested in building generic code would be worth it in the end.
Do you find it hard to maintain your code (eg for sliding window) or do you find it is pretty much stable? I find I have to be careful when changing table schemas, or indexes to make sure the partitioning code still works.
February 14, 2012 at 1:16 am
We have a few template stored procedures, we then just fill in the name of the table and a couple of other bits of info like number of days to keep(generally we partition on a datetime field) on the template and it's all done.
These procs then manage all the sliding window functionality and make sure that if run accidentaly they don't slide out too much data etc.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply