Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Partitioning - Part 4 Expand / Collapse
Author
Message
Posted Wednesday, January 16, 2008 11:49 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Friday, April 18, 2014 9:01 AM
Points: 6,705, Visits: 1,680
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
Post #444150
Posted Thursday, January 17, 2008 1:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:27 AM
Points: 6,997, Visits: 8,411
Nice article series

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


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #444182
Posted Thursday, January 17, 2008 5:49 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Friday, April 18, 2014 9:01 AM
Points: 6,705, Visits: 1,680
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
Post #444258
Posted Thursday, January 17, 2008 6:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:27 AM
Points: 6,997, Visits: 8,411
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #444268
Posted Wednesday, January 23, 2008 10:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 03, 2008 10:43 AM
Points: 1, 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
Post #446534
Posted Thursday, January 24, 2008 5:48 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Friday, April 18, 2014 9:01 AM
Points: 6,705, Visits: 1,680
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
Post #446854
Posted Thursday, January 31, 2008 2:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 04, 2012 4:10 AM
Points: 5, 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
Post #450192
Posted Thursday, January 31, 2008 4:03 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Friday, April 18, 2014 9:01 AM
Points: 6,705, Visits: 1,680
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
Post #450232
Posted Wednesday, May 14, 2008 11:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:23 AM
Points: 1, Visits: 74
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.
Post #500777
Posted Tuesday, December 30, 2008 1:14 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:56 AM
Points: 641, Visits: 1,794
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.
Post #627104
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse