Does AS 2005 have a limit to the number of partitions it can support?
I am sure some one out there must have asked this before, but I just can't seem to be able to find an answer in the docs or online.
Regards,
Alex
Hi,
In standard edition: maximum 3 non-writeback partitions per MeasureGroup.
In the other editions (enterprise, developer, evaluation): the limit is 2^32-1, so it's up to how much the server and tools can handle.
Adrian Dumitrascu.
|||Adrian,Thanks a lot. It would be interesting to know at what point the number of partitions begins to affect performance.
Regards,
Alex|||
1) Technically, there is no limit. On Project REAL we are running about 1300 partitions on our Store Inventory MG. However, you will feel the difference at that level. SSMS is slow at making connections (5-10 min) and once you drill down and expand the partition list you again feel a multi-minute delay (although less). That being said, once it is open, navigating and working with the objects is about the same – and queries are not impacted; nor are executing XMLA scripts in the background. It appears to mostly be an issue with SSMS.
2) You should be partitioning based on your query load; not necessarily based on row count. While, in general, the recommendation is to keep partitions to no larger than 20M rows per partition, I’ve seen system which went larger and still got good responses because of other factors, e.g. well tuned aggregations based on the type of queries issued against long-term historic data.
|||Dave,Thanks a lot. Just wondering when the number of partitions grows to this level does it affect cube processing in any way? Have you had a situation in Project REAL that you needed to reprocess the cube?
Regards,
Alex
