C
cverbiest
Guest
We've never used table partitioning before and I'd like to check if what I'm proposing makes sense. Sorry about the longish post/question. I'm trying to find a balance between performance gain and extra database maintenance. Current db structure Our database is currently split up in 9 generic area's. Tables are assigned to a group, BASE, TRANS or AUDIT. We have 3 area's per group, one for data, one for indexes and one for lobs. # Schema d "Schema Area":6,64;1 . # BASE = setup tables, read frequent, update less frequently (e.g. customer, item, ...) d "BASE tables":10,64;8 . d "BASE indexes":11,1;8 . d "BASE lobs":12,1;8 . # Transactional tables, high growth / update activity (e.g. Order, Orderline, ...) d "TRANS tables":20,64;64 . d "TRANS indexes":21,1;64 . d "TRANS lobs":22,1;64 . # Audit, mainly write-frequent, query rarely tables d "AUDIT tables":30,64;64 . d "AUDIT indexes":31,1;64 . d "AUDIT lobs":32,1;64 . Tables to focus on I created an Excel report based on _IndexStat and _TableStat to identify the tables that are read most, and to show which index is used most of the time. Here I put my focus on the top 5 transactional tables, ignoring the base tables as those should be cached in memory anyway. Changes to areas All of the tables in my top 5 contain a numeric status field (10 = new, 20 = busy, ... , 60 = done, 99 = cancel) I would start with adding a single area, "TRANS actual data". status 60 = data in "TRANS actual data" status = 60 = data in existing "TRANS tables" area Does this make sense ? A lot of the accesses to those tables do not use the status field as a query, but a unique id field. Does it matter that the most frequently used index does not contain the field that is used the split the data ? I think it doesn't but maybe I'm wrong here. Some of the indexes on those tables have a field before the status field. Fortunately that field has a very limited number of values. Can we still use that index to partition the data if we provide more conditions or is that a bad idea ? I'm also considering to introduce a new status value (90 = archived) to make the move of data between "actual and archive" explicit.
Continue reading...
Continue reading...