January 16, 2008 at 11:49 pm
Comments posted to this topic are about the item Partitioning - Part 4
January 17, 2008 at 1:58 am
Nice article series :w00t:
btw the blog url is wrong !
It should be http://blogs.sqlservercentral.com/members/Andy-Warren.aspx
Johan
Learn to play, play to learn !
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[/url]
- 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
January 17, 2008 at 5:49 am
Thanks for the comment and the blog note. Find anything of interest on the blog? I'm still exploring the medium.
January 17, 2008 at 6:14 am
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
Learn to play, play to learn !
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[/url]
- 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
January 23, 2008 at 10:19 am
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
January 24, 2008 at 5:48 am
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.
January 31, 2008 at 2:39 pm
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
January 31, 2008 at 4:03 pm
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!
May 14, 2008 at 11:29 am
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.
December 30, 2008 at 1:14 am
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply