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.
Best Answer
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
SWITCHand 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.