Partitioning - Part 4

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Andy Warren » Partitioning - Part 4

Partitioning - Part 4

Rate Topic

Display Mode

Topic Options

Author

Message

Andy Warren

Posted Wednesday, January 16, 2008 11:49 PM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

Comments posted to this topic are about the item Partitioning - Part 4

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #444150

ALZDBA

Posted Thursday, January 17, 2008 1:58 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:25 AM
Points: 6,861, Visits: 8,045

Nice article series w00t

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

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't 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" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #444182

Andy Warren

Posted Thursday, January 17, 2008 5:49 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

Thanks for the comment and the blog note. Find anything of interest on the blog? I'm still exploring the medium.

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #444258

ALZDBA

Posted Thursday, January 17, 2008 6:14 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:25 AM
Points: 6,861, Visits: 8,045

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

     Jul 13

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

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #444268

baylor

Posted Wednesday, January 23, 2008 10:19 AM

Forum 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

Andy Warren

Posted Thursday, January 24, 2008 5:48 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #446854

parudl

Posted Thursday, January 31, 2008 2:39 PM

Forum 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

Andy Warren

Posted Thursday, January 31, 2008 4:03 PM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #450232

Doctor D

Posted Wednesday, May 14, 2008 11:29 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:25 AM
Points: 1, Visits: 68

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

David McKinney

Posted Tuesday, December 30, 2008 1:14 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:43 AM
Points: 582, Visits: 1,601

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 »

Permissions