MDX Script: missing objects can cause performance problems
Last week we've been auditing a cube (AS 2008 R2) the users of which reported a performance problems after the last cube deployment. We noticed that even the smallest queries took a substantial time to execute. For instance this query ran 500 ms:
- MEMBER [Measures].[Test] AS NULL
- SELECT [Measures].[Test] ON 0
- FROM [Cube]
Here we expect to see something like 0 or 16 ms in SQL Server Profiler. After analyzing the cube script we found a couple of scope assignments referencing a dimensions at the right side of assignments that were deleted in the new version of the cube. Since it was the right side of assignment – the initial parsing has produced no error. Fixing it brought the performance to the original level.
The interesting thing is that ALL the queries were affected and not only referencing these scopes and assignments!
Actually it makes sense to have some performance baseline queries as a part of performance monitoring strategy for your BI system. An “empty query” (above) can be one of them. In considered case it could help not only after deployment, but already at the stage of testing a new version of the system.