Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2008-04-22 : 03:50:34
|
| Hi guys,1) I'm about to implement SQL Table/Index Partitioning for the first time. It seems really good, no downside. Is there an "Gotcha's" I gotta be weary of?2) My tests show a clear performance gain. Is there a performance loss in other areas? In "Designing Partitions to Improve Query Performance" it says this:"In this way, the SQL Server query optimizer can process the join faster, because the partitions themselves can be joined. If a query joins two tables that are not collocated or are not partitioned on the join field, the presence of partitions may actually slow down query processing instead of accelerate it."What does that mean?If I have:Select A.Col1, B.Col3from Ajoin B on A.Col5=B.Col6Does that mean I have to partition A.Col5 & B.Col6 the same way else my queries will get slower?3) Does it take up more space? Do restores take longer? 4) How much maintenance is involved?Is there a way of automating the creation of new partitions? Or do you just create loads that you'll need in the future e.g. always have a few months' partition created ahead of time?The "Designing Partitions to Manage Subsets of Data" article in BOL seems to indicate some manual work every month (if you have a monthly partitioning strategy).5) Why not partition every table that has over say a million rows?6) If I create a Yearly Partition on a table that has been around for a long time, will it automatically partition all the indexes on that table? Or do you have to re-create the indexes for that to happen?7) Neither table can have a full-text index in a partition move. Does that mean you can't ever partition tables with full-text indexes?8) Partitioning doesn't seem to fit well with replication. Do you have to end up choosing between the two? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-22 : 23:26:09
|
| 1. not worth on small table.2. if partition key doesn't align with clustered index, you'll see more table scan.3. no and no.4. have to think about how to switch partitons. 5. too much maintenance overhead, may not justify performance gain on table in such size.6. no, have to partition nonclustered index separately. |
 |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2008-04-23 : 09:42:35
|
| Thanks rmiao. What's been your experience in implementing partitions in a production environment? |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2008-04-23 : 09:48:19
|
| On point 2 again. How big a problem is this? I have a table that I want to partition. It has a one-to-one relationship with another table that it often gets joined with. But that table doesn't have a create date column. I was thinking of added that column so that I can partition the two tables the same. But I join on the int PK & FK. So is this saying it's better to use that as the partition column?And how likely is it to stuff up all the other joins to other tables (that aren't partitioned the same) on other columns? How would you accurately monitor/test this? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-23 : 10:31:23
|
| Does any performance gain warrant the extra admin and maintenance effort?Could get better performance in other ways?I've usually found that performance isn't the overriding factor in implementing partitioning and if it is there's an obvious reason for it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2008-04-23 : 10:38:45
|
| What other motive could there be for partitioning? It's hidden to the Client/Developer. So only the DBA is even aware of it. I suppose the nightly index defrag can be quicker if you're only doing a recent partition. But other than that, what other reason(s) are you referring to? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-23 : 23:08:51
|
| We partition table with more than 1b rows and needs to remove old rows periodically, most are fact tables in DW. For point 2, you can partition index too. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2008-04-25 : 04:15:41
|
| Does anyone have any comments on points 7 and 8? |
|
|
|
|
|
|
|
|
SQL Partitioning