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 |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2013-08-26 : 08:31:36
|
| I am wondering if anyone has setup table patitioning and can share their experience? Espeically if the performance gains are significant.I'm having trouble understanding why it would be faster than an index. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-26 : 08:53:03
|
| There are a number of reasons why you might want to use table partitioning which I am sure you can google and find. For your specific question as to why choose partitioned table over index: consider a simple example where you are updating the rows in a table. If you are updating only one row, SQL Server perhaps takes a row level lock. But if you are updating a 10,000 rows it would take an exclusive lock on the table - which means readers will be prevented from reading the data. By paritioning the table, you can enable partition level locks, so the readers who read from other partitions will not be blocked.Couple of interesting links: http://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspxhttp://www.brentozar.com/archive/2012/03/how-decide-if-should-use-table-partitioning/ |
 |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-08-26 : 10:49:26
|
| Thanks for the links.In terms of Select statments and aside from possible locking issues, is there any Speed benefit? |
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2013-08-26 : 12:09:18
|
| As is always the case with SQL, it depends. There can be a performance improvement if your data set is large and your queries can consistently incorporate the partitioning column in order to take advantage of partition elimination, however you need a well designed partitioning scheme and extensive testing to prove out. In general, the guidance I've seen and heard is that partitioning is most suited for OLAP and data warehousing scenarios versus OLTP, but YMMV. Keep in mind that partitioning can also benefit maintenance scenarios as you can swap out partitions very quickly compared to other methods of moving data. |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-08-26 : 16:24:35
|
| Partitioning is typically not faster for lookups IF the table has the correct clustering key. But that's what gets many places. Many simply can't get over the idea that every table should by default be clustered on an identity column. [Yes, that claim is ridiculous, but you see it vociferously repeated by a lot of supposedly knowledgeable people.]Partitioning is extremely useful for frequent deletes and inserts of large amounts of data in specific key ranges. That is, you can easily drop a single (obsolete) partition or add a new partition, typically for the most recent date/date range, without affecting the rest of the table data.Another great (side) benefit of partitioning is that older, usually less-accessed data can be compressed, and have a 100% fillfactor, while more current data is not compressed and can have a lower fillfactor, better allowing more current update activity.Other than that, you'd need a very large amount of data before partitioning becomes a big performance gain just from the partitioning. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-08-27 : 09:57:03
|
| Thanks everyone. I am starting to get a clearer picture.I had understood from a few colleagues that Paritions could improve select performance and that made me wonder how. So now I am beginning to think that Paritions is yet another misunderstood concept (like Views). |
|
|
|
|
|
|
|
|
Table Partitioning