locked
Performance - Partition tables in CRM for 'archiving' - Supported? RRS feed

  • Question

  • Has anyone gone down the path of trying to set up partitioning on CRM tables for performance/archive reasons in very large CRM DB's? Would this even be supported? To explain, say you added a field called 'archive' to opportunities, then set up table partitions such that when archive = 1, it would go to one SQL partition, and when it is 0/null it would go to the 'other' SQL partition.

    Then you change your views to all include the 'archive' attribute as filter criteria (separate views for 'current', 'archive' , and 'all' data as required) - at that point your 'non-archive' views are querying against a potentially vastly smaller 'active' dataset, which could also be targeted to be stored on separate/faster disks as well.

    Thursday, March 27, 2014 6:04 PM

All replies

  • Microsoft would class this as unsupported. If you did try this there's one major obstacle - coping with adding an attribute to the entity, as CRM would add a column just to the standard table.

    Overall, I don't think you'd get much performance benefit anyway. Crm will only return a page of records at a time, so you're never getting huge volumes of data from one query (the exception would be an SSRS report, but then you'd control the SQL). I think you'd be better off ensuring you've got suitable indexes that work for the commonly used views


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Thursday, March 27, 2014 7:59 PM
    Moderator
  • 1. You're probably right, this would likely be considered unsupported, as it seems indexes are the only allowed 'tweaking' of the DB - so this wouldn't be viable.

    2. Just for the record, I'm talking about the intrinsic table partitioning built into SQL Server (like they use in CRM for partitioning 3-month increments of the audit logs if you have SQL Enterprise).

    http://blogs.msdn.com/b/thecrmguys/archive/2011/09/07/what-s-behind-auditing-in-crm-2011.aspx 

    http://msdn.microsoft.com/en-us/library/ms191133(v=sql.105).aspx

    In that case, an ALTER TABLE to add an attribute on an entity should just 'work' on a partitioned table according to the documentation of ALTER TABLE.

    This whole topic comes up for discussion regularly when seeing really large CRM DB's and always trying to come up with new 'archiving' strategies, but we just keep coming back to the same old options :)

    Tuesday, April 1, 2014 7:58 PM