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?
2 Answers
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.
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:
- A well defined partition range as you only can have 1000 partitions per function.
- 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.
- 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.
- 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.
audit_dateand your purge process is just deleting rows based on theiraudit_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.