Post reply

Performance Tuning SQL Analysis Service.

  • June 9, 2005 at 10:30 am

    #110551

    I'm running into an odd issue with the processing of a cube in Analysis services.

    Here is the deal, if I restart the Analysis Server service then run our normal full process job, it will complete in about 10 mins and then it will commit the transactions to the database within a minute.

    Here are the last 3 lines of my log as an example.

    Error = 1 (00000001), Description = 6/8/2005 9:41:58 PM    5629        Completed Processing Cube 'Sales_Analysis'. End time: 9:41:58 PM  Duration: 0:10:33

    Error = 1 (00000001), Description = 6/8/2005 9:42:20 PM    5629        Committing transaction in Database 'Sales_Analysis'

    Error = 1 (00000001), Description = 6/8/2005 9:42:35 PM    5629    Committed transaction in Database 'Sales_Analysis'

    Now, the next time the process runs, without restarting the Analysis Server services, the process will take 1 hour and 10 mins.  Now the cube itself doesn't take any longer, the hour comes between the Completed Processing Cude line and the Committing Transaction in Database line.  And that hour is precise to the second. 

    Here are the last 3 lines of the log as an example.

    Error = 1 (00000001), Description = 6/8/2005 2:22:33 PM    8205        Completed Processing Cube 'Sales_Analysis'. End time: 2:22:33 PM  Duration: 0:10:57

    Error = 1 (00000001), Description = 6/8/2005 3:22:33 PM    8205        Committing transaction in Database 'Sales_Analysis'

    Error = 1 (00000001), Description = 6/8/2005 3:22:49 PM    8205    Committed transaction in Database 'Sales_Analysis'

    Any additional reprocessing of the cube without a Analysis server service restart, comes with the same 1 hour 10 minute time frame.

    Anyone have an idea as to what is going on between the Completed Cube and Committing Transactions?

    The dimessions, partitions and aggregations in the cube don't take any longer, its just the last step when it commits.

    TIA

  • June 10, 2005 at 11:14 am

    #564690

    The Analysis Services, has a problem that after restart of the service, it tries to load all the dimensions in the memory of the server and does not unload more until next restart.

    Tries link below that treats on performance tunning in AS:

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx

  • June 10, 2005 at 11:28 am

    #564698

    Yep, I have that one.  Been reading through it and trying different memory configs, but nothing has worked, yet.

    I just found the 1 hour time difference to be too precise, so I thought I'd ask.

     

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

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