Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table in my database that has about 250m rows. I'm new with databases but I have read up about partitioning the table to increase performance. One of the important points that I seem to notice is that the partitions should be placed in different file groups in different drives. But my server has a single ssd that is partitioned. Is there any advantage in placing the files in individual partitions of the storage device even though physically it is just one. Does partitioning still help if all the file groups are in the same physical drive?

Thanks in advance.

share|improve this question

migrated from stackoverflow.com Feb 28 at 13:54

This question came from our site for professional and enthusiast programmers.

    
I'm a little confused by the way you mention both database partitioning and disk partitioning. There should be no benefit of having multiple disk partitions on one physical disk - if you've only got one database disk, go ahead and put all of your .MDF files on one drive letter. Also go buy more disks :). –  Nathan Jolly Feb 28 at 14:05
1  
@NathanJolly, there is a slight benefit to partition a single drive if you host OS and everything else on the same drive -- not trying to make a point that it's a good practice on a server, though. Say, your OS partition gets corrupted and it's on C:, the rest is on D: -- format C:, re-install OS, and your data is on D: is intact. Mostly a desktop setup! :) –  DenisT Feb 28 at 14:21
3  
@DenisT, I agree completely. I hoped that "one database disk" would qualify my answer a bit - I'm mostly trying to suggest that having different MDF files on E:\, F:\ and G:\ won't help if they're all the same disk - but I'm glad you clarified. –  Nathan Jolly Feb 28 at 14:23
1  
How To Decide if You Should Use Table Partitioning is a good read on the topic. –  Remus Rusanu Feb 28 at 15:47
2  
What benefit are you trying to get from table partitioning? How have you decided that it's the direction to go? In my experience most people that want to use table partitioning don't actually need it. –  mrdenny Feb 28 at 19:53
add comment

3 Answers 3

Performance is only one of several benefits that can be gained from partitioning. The benefits from partitioning are typically realized from partition elimination, where the query engine can rapidly narrow down the data set you're querying against. This allows SQL Server to read in and operate on just a specific portion of the data, greatly reducing work time and I/O. However, as discussed in the linked post, this relies heavily on your how your query is written and whether or not the index it is using can leverage the partitioning scheme you have in place.

It's partition elimination that also allows you to leverage the benefits of placing partitions on different disk systems. The idea is fairly simple: if you can place partitions that are more actively queried against on faster disk, you can help your query out even more because the subset of data it's accessing can be retrieved faster. At the core, though, the concept remains the same. The reason you're increasing query performance is because you're helping the engine reduce the amount of data it is working with.

Two other benefits of partitioning include the ability to rapidly move data in and out of table using SWITCH and reducing index rebuild time by rebuilding indexes by partition. The first is something very specific and can only be used when properly designed, but can be very useful on extremely large data sets. The second is actually one of the main reasons I will typically leverage partitioning, as maintaining indexes on large tables can be very consuming, both from a resource and time perspective.

That being said, 250 million rows is not typically considered a large data set. SQL Server can easily handle tables of this size and greater. When I consider partitioning, I try to think more in terms of data volume. I don't usually think about partitioning until my table is over 250 GB. This isn't to say that your table doesn't fit this profile if your rows are very wide, but chances are you're not anywhere near that threshold. You might want to consider other options to increase your performance. Usually this means proper indexes. A quick win, if you're using Enterprise Edition, could be to instead use data compression, which will reduce your I/O to disk and can be a nice boost to performance.

share|improve this answer
4  
... a related benefit is that you can roll older/colder data to slower storage much more quickly so that you can keep hot data on fast storage with minimal outage. a wise man once said that you can recover the benefits of partition elimination with a good indexing strategy. –  swasheck Feb 28 at 15:42
    
With Table partitioning, if you are using 2008 and up your locks will be at a partition level, instead of the table level –  Nabil Becker Mar 1 at 4:53
    
@NabilBecker This is only true if you enable it. By default, even when you partition, locking will still be at the table level. But it is definitely an option. –  Mike Fal Mar 1 at 16:25
add comment

No. In fact partitioning is not what you think and should be reread - it is more a help to delete data than anything else. Partitions (sql server, enterprise only) are a very specific problem solver. Btw., 250 million rows is quite tiny - want to look at a 10 billion row system running in a vm? Realistically - it depends what you do. Partitions help a little possibly with queries (as does bascialyl the index) but allow fast deletes of a partition.

For pure IO you need pure IO, and if you have a single SSD that is already a good solution - and one that you can not optimize. OS level partitions wont help at all as long as you hit the same disc subsystem.

share|improve this answer
    
Yes. It faster to read from chunks of data. –  rudym Feb 28 at 13:47
    
Actually it is not - at least not signiciantly. It is faster because it forces the clustered index to be sensible - but if you do the same on a non-clustered table you have pretty much the same performance. BUt deletion is different. Care to read for example stackoverflow.com/questions/2701143/… –  TomTom Feb 28 at 13:48
    
selects on partitioned tables are written in specific technique. –  rudym Feb 28 at 13:57
    
@TomTom, would you say that the partitioning can give one more backup/restore chioces? –  DenisT Feb 28 at 14:43
add comment

Yes, it can help. But the problem lies within cost of rewriting your queries in specific manner. Designing Partitions to Improve Query Performance

The meaning of partition is equally part you stored data by date or some other value.

SCRUD will be much faster, because only relevant chuncks of data will be used.

MSDN

Partitioning makes large tables or indexes more manageable, because partitioning enables you to manage and access subsets of data quickly and efficiently, while maintaining the integrity of a data collection. By using partitioning, an operation such as loading data from an OLTP to an OLAP system takes only seconds, instead of the minutes and hours the operation takes in earlier versions of SQL Server. Maintenance operations that are performed on subsets of data are also performed more efficiently because these operations target only the data that is required, instead of the whole table.

share|improve this answer
4  
-1. Because that is what the INDEX in the partition does. Funny enough it also does do without the partition, so it won't be MUCH faster. PArtitioning is about fast deletes of a complete partition. –  TomTom Feb 28 at 13:36
1  
MANAGING LARGE DATA is ot querying. It is deleting. Try deleting 100 million rows of a 10 billin table. Compare that to swapping out the partition and using truncate on it. That is management. Not making a select faster which is executed based on the partition index statistics anyway. –  TomTom Feb 28 at 13:42
    
@TomTom in MSDN says otherwise. From my experience not only manage data but even in reading it. –  rudym Feb 28 at 13:42
1  
@TomTom selects on partitioned tables are written in specific technique. And I really don't understand your offensiveness. –  rudym Feb 28 at 13:56
2  
Offensive or not, what TomTom says is factually correct. Partitioning helps only very specific query patterns while it easily hurts. (Unaligned) indexing is usually a far better solution to improve query performance. –  usr Feb 28 at 14:25
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.