We need to store a lot of logs in MS SQL Server 2016 Database for 1-1.5 year. Income: 50 Gb of logs per month (~ 50 millions rows per month), we store all data in 6 months and shrink 50% of this data for older records. Prerequisites:
- Required different indexes for last 1st month, 2nd month and others.
- Required data compression for big XML/Text data older than 2 months.
- For now - Required data shrink for data older than 6 months (just remove 50-60% records with big data (XML/Text) from table), but it will be better just heavily compress rows and don't remove it.
Main Use-Case: search for 100-200 (sometime 1000) rows by some IDs or string filters in SQL IN manner (sometime LIKE).
Exist two approaches for partition:
- "Old style" - Separated Tables for each period (I think without common View in our case), month as example (Log_0117, Log_0217, ...).
- Pros: Fully independent management and schema (for evolution), independent indexes and query/plans caching, as expected - more performance and efficiency, simple to implement, less magic and more controlable.
- Cons: Many-many tables (8 for every period), complex queries (especially for cross-period queries).
- "Modern style" -
Partitioned tables and indexes.
- Pros: Easy management, just one table for all periods, in theory simpler queries.
- Cons (as I found by some articles): Strong requirements for partitioned indexes (by normal, all indexes must contain partition-column (therefore more memory required), such indexes require rebuild after partition switching, hard to change schema/indexes for different periods, more magic and less controlable.
My questions for community:
- Is it really builtin Partitioned tables/indexes make developing more simple?
- Does it have really inconvenient limitations now in MS SQL Server 2016 (as Enterprise, as or everything not so bad?
UPD1: Columnstore Indexes - another interesting option from @DavidBrowne, but as described at MSDN:
Columnstore indexes give high performance gains for queries that use full table scans, and are not well-suited for queries that seek into the data, searching for a particular value.
Unfortunately, we mostly search for 100-200 records in logs, rather than calculate complex statistics, but I save it in the mind for future, maybe will be helpfull.
UPD2: @DanGuzman, I meant Aligned indexes when said about some requirements - https://technet.microsoft.com/en-us/library/ms187526(v=sql.105).aspx:
Partitioning NonClustered Indexes
When partitioning a unique nonclustered index, the index key must contain the partitioning column. When partitioning a nonunique, nonclustered index, SQL Server adds the partitioning column by default as a nonkey (included) column of the index to make sure the index is aligned with the base table. SQL Server does not add the partitioning column to the index if it is already present in the index.
It's also heavily recommended not to have Non-Aligned indexes 1) partitioning big tables - indexes , 2) Performance Impacts of Partitioned Aligned Indexes.
It means - if we want to make queries faster just for last month, we need to: 1) cover all table by this index (but it requires really more disk/memory) or 2) organize long maintenance window for partition switching and indexes rebuilding.