3

We have a very big table which is over 300gb with over half billion rows in it. This table is used for audit purpose for all critical applications. A lot of inserts are happening per seconds. Due to heavy blocking, we can't purge data while the database is online. My boss is saying by partitioning and table compression will help the disk space and overall performance. He is from OLAP DW area. But I highly doubt this will work in OLTP. How do I convince him this will not work? Any articles?

  • Why wouldn't it work? There may be valid reasons but nothing in what you've posted here would seem to indicate that your boss is wrong. If the table is partitioned by audit_date and your purge process is just deleting rows based on their audit_date, dropping an old partition is going to be vastly more efficient than deleting rows. Perhaps, though, you know something about your data that we don't that tells you that your boss's idea won't work. – Justin Cave Jul 30 '15 at 21:10
  • 1
    Your question pre-supposes this approach will not work, and in fact, I think you will be hard pressed to find proof it will not work. Partitioning is one of the best ways to improve performance in OLTP as well as OLAP. You can switch out partitions in seconds which would take log space and time to delete and you can do this while database is online. I use monthly partitions like this currently, – Thronk Jul 30 '15 at 21:19
  • if you want to purge that table - How about you create another table with different schema and exact same structure and then do a schema switch. Then in the new table you can implement partitioning which would be much manageable. If you need the data from the old table, your best bet is to go for table partitioning (its an enterprise edition feature). – Kin Shah Jul 30 '15 at 21:23
3

Sorry to disappoint, but your boss is right on target. Some of us BI folks know about DB optimization, at least a little. =) As with any major architectural change you need to test and adapt appropriately to your unique environment, workload, servers etc.

Due to heavy blocking, we can't purge data while the database is online.

Table partitioning is exceptionally helpful in many environments, especially those with large data sets. Not only can you avoid locking issues and improve query performance, but you can reduce log impact by using Truncate after swapping out your partitions when purging old records.

See this free training by Kendra Little with Brent Ozar Unlimited for a detailed guide about table partitioning in SQL Server which, incidentally, is focused on OLTP environments.

As for data compression, see this SQL Server Customer Advisory Team white paper for data compression implementation. You will save disk space, and reduce I/O at the cost of CPU usage... This trade-off is something that you need to plan and test for before blindly implementing.

|improve this answer|||||
1

For the portion of the question asking about compression, Dave is correct on the trade off. I would investigate adding more capacity to your system before you head down the road of compression.

For the partition portion the answer is going to be it depends. Partitioning takes a lot of planning and analysis of your current queries before you can measure what the benefits could be.

Dave references Kendra's video on partitioning but I would recommend her more exhaustive article that has much more discussion and resources to help in making the decision. There is also another article on MSSQL Tips that goes into some good detail.

Here are a few items, based on my experience of managing an OLTP database before I removed the partitioning, that are required in order to make horizontal partitioning effective:

  1. A well defined partition range as you only can have 1000 partitions per function.
  2. A column that is a good candidate to be partitioned on, which is either a foreign key of the table or a date[time] column. One thing to note with the date is you will have to add a persistent computed column to the table so you can define the part(s) of the date to partition on instead of the date column itself as the partition function needs a deterministic value.
  3. The column that the data is partitioned on must be in the where clause in order to utilize the partitioning, otherwise a scan of all of the partitions will be done to satisfy the query.
  4. Each partition should be in its own file group with the file(s) in the file group on different arrays to maximize the possible I/O.
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.