- SQL 2012 Enterprise
- OLTP table currently at 1B records
- records increase 5-10M per day
- SAN lun with flash storage
- two indexes (one clustered identity int)
What we have noticed is a gradual slowing of INSERTs over time, which will eventually become an issue to the business if left unchanged.
The question: We are planning to transition to use partitioning on this table soon, primarily for space management, but the main question here is whether partitioning can help improve the performance of INSERTs. (We know about performance increases for SELECTs already, please do not comment on that side of things if possible.)
I have done a fair amount of searching forums, etc, but have not found anything specific to INSERTs and partitioning. It seems reasonable to me that this would help maintain speed over time, given that we will partition on "date inserted" and that the INSERTs would always be directed to the most recent partition, but looking for confirmation.
UPDATE: To clarify: a staging table (for switching in) is not an option due to the OLTP nature and needing to read the data out immediately after inserting, both of which cannot be delayed awaiting a switch in.