SQL Server- Table partitions on SSD

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.