SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Partitioning - Part 4


Partitioning - Part 4

Author
Message
Andy Warren
Andy Warren
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: Moderators
Points: 45588 Visits: 2783
Comments posted to this topic are about the item Partitioning - Part 4

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
ALZDBA
ALZDBA
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60905 Visits: 9188
Nice article series w00t

btw the blog url is wrong !
It should be http://blogs.sqlservercentral.com/members/Andy-Warren.aspx

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Andy Warren
Andy Warren
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: Moderators
Points: 45588 Visits: 2783
Thanks for the comment and the blog note. Find anything of interest on the blog? I'm still exploring the medium.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
ALZDBA
ALZDBA
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60905 Visits: 9188
When I'm on the net ...
Most of the time I spend in forums.
Most of the time I tend to forget the blogs.

Maybe the SSC startpage should also have a part "most recent blogs"
(cfr most recent forum posts)
Just to catch the eye.

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
baylor
baylor
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 13
Andy,

Help me out.

in Partitions - 4, you put all partitions on primary. I thought the performance gains you receive from partitioning was from putting each partition on a different file group, with each group's file being on a different drive (multiple heads, faster reads). What, if any, are the performance gains if you put all partitions on one file group?

Thanks for your help,

Bret
Andy Warren
Andy Warren
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: Moderators
Points: 45588 Visits: 2783
You can see benefits in two different ways. One is as you suggested is to put the partitions on different filegroups (which need to be on different physical drives for it to make sense), the other is to just present less rows for the optimizer to deal with when the where clause includes the partitioning column. Think about the savings in disk IO if you scan only the partition compared to the full table.

There's also value in partitioning as part of a load/archive strategy, using some of the advanced parts of partitioning to swap data in/out as needed very quickly.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
parudl
parudl
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 66
Hi
I'm trying to use partitioning to switch data in and out of a partition on an hourly basis. I have a large amount of data that I want to switch quickly, instead of updating/inserting/deleting rows based on an update to a source system.

Is partitioning the best route, so far I've tried SQL 2000 , and it looks like attempting to do an ALTER VIEW I'll get table locks occurring if there is set of heavy queries running n the data already. I've tried 2005 but it looks like I have a similar problem.

Ideally I want to query one table, switch a load of data in and out, and not impact any SELECT queries running on the old data. Have you come across anything like this... I get the feeling I may need to queue the queries in some way, or prevent users from querying the database when the switch is to occur, as the ALTER TABLE/VIEW is practically instant.

Thanks
Andy Warren
Andy Warren
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: Moderators
Points: 45588 Visits: 2783
Ideally you need to block them for the few seconds you need. You might try issuing a DENY on the table as part of the switch code, kill any existing connections (or let them complete), then do the switch. DENY is more granular than kicking everyone out of the db, but that works too!

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Doctor D
Doctor D
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 79
You mentioned at the end that we only got 10 rows and this was due to no data being in the table during those timeframes. Is there a way to return a 0 for the partition count during those timeframes? I have looked in many places and have yet to find an answer, though your explanation up to that point was one of the easiest to understand and most comprehensive. Thank you for that.
David McKinney
David McKinney
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5095 Visits: 2094
Hi Andy,

"One of the rules is that partitioning columns have to be a part of any unique index (as I understand it, this helps the optimizer know that it only needs to check a single partition) if you want that index to be on the same partition."

I've read this over and over, and I still don't get it. By adding another field to my PK or UQ, I change what can be unique in that table. i.e. if previously ContactId was my primary key, and now I add ModifiedDate, then I can end up with an infinite number of duplicates for a given ContactId.

Am I missing something?

Thanks,

David.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search