We have a workflow system that has tasks (tickets, whatever).
As of today, we have several hundred thousand closed tasks and about
5-7k open tasks.
The majority of operations work on open tasks, and the majority of
reports are also for open tasks; open tasks eventually become closed,
closed tasks can become open too but that's rare.
Is it possible to partition the tasks' table by the IsClosed bit
field, and have SQL server move rows back and forth automatically?
Will it be slow?
Also, for the reports that aggregate data from both small active
partition and big "closed" partition, will there be a big performance
hit compared to a single, non-partitioned table?
Articles on partitioning I have googled are kind of one sided and
mostly deal with static partitions of archived sales data etc that are
maintained manually or using scripts on regular basis.
Hmm.. any ideas?
No comments:
Post a Comment