MDX Query running slower in SQL 2012 than SQL2008R2

  • I have 2 instances of SQL SSAS SQL 2008R2 and SQL2012 on a server. SQL 2012 is allocated almost twice the hardware resources as in SQL 2008R2, But a when i backup and restore the databases from SQL2008 to SQl2012 one of the query is running long in SQL 2012 like really long.
    In SQL2008 it runs within a second in SQL 2012 it never completes any idea why that might be? The Query is not that complex,

    WITH MEMBER
    {Measures}.....AS
    SELECT
    {
    {Measures}....

    }
    ON Columns
    NONEMPTY(
    EXPECT({{Dimension].....}
    ON ROWS FROM [Cube]
    Where
    (
    <>
    )

    Can you kindly help?

    Regards,
    Vinoth.

  • Are you on the latest service pack and current on CU updates?
    There have been at least a few performance fixes related to MSAS so you probably want to be current. SP3 and at least CU2 might cover most of them but I'm not positive on that one. If you were current on 2008R2 but not on 2012 - that could explain the difference as there were some updates for both 2008R2 ad 2012.

    Sue

  • Hi,

    Yes we are on SQL 2012 SP3 CU8.

  • Then I would guess the difference is likely related to the architectural changes in Analysis Services - tabular vs multidimensional. I wouldn't really compare 2008 and 2012 since so many things have changed. If you are using tabular, you may want to try using DAX instead. You may also want to check your compatibility levels.
    You may want to try seeing if you can find if certain measures are performing poorly - add them one at a time and see if you can identify if one is having problems,

    Sue

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply