none
SSAS or SSRS running query in parallel on UAT but in serial on Prod RRS feed

  • Question

  • I am not sure if this is an SSRS question or SSAS question.  I have a report in SSRS with about 8 parameters that are tied to DAX queries that have as their main parameter the value of another parameter (Param1).  I select a value for Param1 and SSRS goes to the SSAS server to load available values for the 8 other parameters.

    On UAT, this process takes about 3 seconds.  On Prod this process takes 8 seconds.

    Some things that I observed:

    Each query that runs on Prod is as fast or faster than the equivalent on UAT, the duration in the trace that I ran showed durations that were the same or less on Prod than UAT.

    What I found interesting is that on Prod only one query runs each second.

    On UAT I found that up to 4 queries ran in one second.

    When I look at the Properties of the UAT SSAS server the properties are the same as the Prod SSAS properties.

    What could be causing this difference in behavior?

    Both servers are SQL Server 2016 Enterprise

    Prod is build 13.0.5337.0

    UAT is build 13.0.5270.0

    Any suggestions on what to look for?


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    Thursday, August 15, 2019 8:57 PM

All replies

  • Hi Russ,

    Thanks for your post.

    Based on my search, whether the queries could be executed in parallel or not depends on SSRS tool configuration, not SSAS server configuration. You could search the checkbox "Use Single Transaction" in the two environments, and see if it has any difference.

    Reference

    SSRS Dataset Execution Flow Parallel or Single Transaction

    Does SSRS run multiple queries at once?

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 16, 2019 2:41 AM
  • Blame SSRS!

    Thank you so much Will for this information.  I have been pulled into two urgent matters today, but I will check on Monday to see what I can find.

    Do you know if there might be different settings on the SSAS drivers themselves on the SSRS server?

    I will compare versions of the drivers as well as versions of the SSRS servers to see if there might be hints there.  I will also compare the rdl on both servers to make sure that there are no differences there.


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    Friday, August 16, 2019 3:42 PM
  • Hi Russ,

    Thanks for your reply.

    >>Do you know if there might be different settings on the SSAS drivers themselves on the SSRS server?

    Haven't heard settings about execution mode "parallel" or "sequence" on the SSAS drivers.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 20, 2019 7:43 AM
  • Sorry to be long in getting back.  Your second comment answered my second question about settings on the drivers, but not the original question.

    The reason I asked, is that I cannot find a Transaction check box for my SSAS data sources.  The connections are identical on UAT and PROD except for the server name.

    I have a bit of a hiccup in finding a solution.  Someone made a made a change somewhere in the UAT environment, so the reports are behaving differently.

    These are the builds for the different reporting services and analysis services (including data extension dll used by SSRS). 13.0.5426 is the latest build up until today.

    UAT:

    SSRS: 13.0.5426, SSRS, UAT

    SSAS Tab:  13.5270.0, Tabular, UAT

    Data extension dll:  13.5026.0, Data Extensions.dll, UAT


    PROD

    SSRS: 13.0.5366, SSRS, PROD

    SSAS Tab:  13.0.5337.0, Tabular, PROD

    Data extension dll:  13.5026.0, Data Extensions.dll, PROD


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    Thursday, August 22, 2019 2:40 PM
  • Hi Russ,

    Thanks for your reply.

    >>The reason I asked, is that I cannot find a Transaction check box for my SSAS data sources.  The >>connections are identical on UAT and PROD except for the server name.

    This is the screenshot of related configuration about execution mode "parallel" or "sequence".

    I think you need to compare the configuration of SSRS in the two environments, and see if it has any differences.

    Or you could submit your doubts about how to make the report the same in two environment to SSRS forum for help.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 23, 2019 8:31 AM
  • I couldn't find the property on Shared Data Source and the RDL is identical on production and UAT.

    Monday after any upgrades, I will review the configurations again.


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    Friday, August 23, 2019 1:35 PM
  • I couldn't find the property on Shared Data Source and the RDL is identical on production and UAT.

    Monday after any upgrades, I will review the configurations again.


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    Any updates for the current thread? You need to take a further check on the report settings.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 28, 2019 6:57 AM
  • I was able to make Web Services calls to get information about the shared data source.  The connections are identical, with the exception of the Data Source.  One points to one server the other.

    I then compared any properties that I could find and they were the same on both Report Servers, except for things like the server name, and the name of the report server database server.

    I compared properties on both of the SSAS servers.  I found no differences.

    3 out of 4 of the SQL Server instances (1 SSAS instance, and 2 SSRS instances) have the latest service pack 13.0.5426.  1 SSAS instance, the one that runs fastest, is behind:  13.5270.0.  This is the same build that I saw when I first looked at this.  Could it be that after 13.5270.0 something got screwed up on SSAS?

    I am going to go outside my comfort zone (I'm a database developer) and see if there is some kind of Windows configuration that is causing issues.

    I will look at the size of the databases to see if perhaps that might be causing issues.



    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    Wednesday, September 4, 2019 6:50 PM