Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1875
Members Online:
3
Guests Online:
21

Total Post History
Posts:
81447
Topics:
18714

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
konam534As

Other

FAQ
All times are UTC [ DST ]

Google Ads

Partitioning Design

Please wait...

Partitioning Design

Postby onpnt on Mon Feb 11, 2013 11:22 pm

Looking for a second opinion or just a discussion on a design. Details: one table with a daily ETL process that loads around 150MB of data at one time (ETL process has been redesigned from 30 minutes to 30 seconds so that is a nonissue). The table is around 90GB and has a large potential for growth over the next few years.

The table is based on one date - current day. Each row bulk loaded on that day retains the date it was loaded.

I've partitioned the table by month. Each month contains, of course, days for that month. So it is RIGHT on '2013/01/01' and so on.

The partitions of course means there are many file groups and files, which I like for future IO tuning and moving old months off to old disk. This was the real reason for the partitioning. Right now, performance based on some good indexing and filtered indexing on months, is good but I'm also thinking about the partitioning for archiving the months into year based tables. Although, I'm not sure I can do that unless someone knows how. Take 12 partitions and switch out to one table. I tired but couldn't get that to work yet.

So, bad design? good design? poor use of partitioning? best practice of partitioning is to manage IO for true performance gains but the archiving strategy would make it even more of a best practice where partitioning benefits this table design.
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI
Unrated

Re: Partitioning Design

Postby SQLDenis on Tue Feb 12, 2013 12:03 am

Post some DDL and I'll take a stab
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Partitioning Design

Postby philhege on Fri Dec 13, 2013 10:58 pm

Woohoo, late to this party, too.

Ted, did you plan on a windowing scheme? Did you discover that your partitions need to match your load population for minimal interruption performance?

I tossed partitioning when I discovered that I'd have to create 730 partitions to implement a staging/SWITCH routine for two rolling years of data. Yuck.


HILL!???!? WHAT hill? I don't remember any %*@&) hill!
User avatar
philhege
LTD Senior Moderator
LTD Senior Moderator
LTD Bronze - Rating: 9
 
Posts: 95
Joined: Tue Oct 09, 2007 5:55 pm
Location: Western New York
Unrated

Re: Partitioning Design

Postby onpnt on Tue Dec 17, 2013 2:21 pm

I automated the entire deal and it has worked pretty much as good as anything else I tried for tuning the table. Even better when I worked it right on the disk subsystem side.
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI
Unrated

Re: Partitioning Design

Postby philhege on Mon Dec 23, 2013 9:08 pm

Good deal. Over here in la-la land, we get tossed a VM with no way to manage the disks/partitions. One day they could be up the street; the next they could be in Delaware. The files could be on different spindles one minute, and on the same the next.

Good times....


HILL!???!? WHAT hill? I don't remember any %*@&) hill!
User avatar
philhege
LTD Senior Moderator
LTD Senior Moderator
LTD Bronze - Rating: 9
 
Posts: 95
Joined: Tue Oct 09, 2007 5:55 pm
Location: Western New York
Unrated