Need help understanding cause of long-running SSRS report

(0) Share

Report

Posted on by bking48

215

Running DAX2012 R2 CU1. Running a Project Profit/Loss report for a single month results in about an hour runtime for a report with 134 rows. I'm attempting to determine where the bottleneck is from a system standpoint.

If I look at the SSRS ExecutionLog3 view, the start/end times of the report are at the very end of the hour, and indicate that it only takes SSRS about 20 seconds to generate. Does it sound correct that SSRS isn't involved and the report isn't produced by SSRS until very late in the process?

At the start of the report run, very typical things happen to the system - data db is utilized, temp dbs are utilized, etc for a couple of minutes and then the disk activity goes to virtually nothing. The only server activity is SQL Server using one core at close to 100%. This continues until the end when the report is finally generated.

I thought that for AX-SSRS reports, AX handed off the data query and report creation duties to SSRS, but the ExecutionLog3 times would seem to dispute that.

Thanks for any insight you can give me.

Regards,

Bryan

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Martin Dráb 236,192 Most Valuable Professional on at

Like (0)

Report

RE: Need help understanding cause of long-running SSRS report

That fetching data for some reports takes long time isn't a bug. One option is improving performance (e.g. if you don't maintain DB indexes properly, your queries will take long time). But sometimes you expect it run long time and that's what pre-processing data providers are for.

The discussion above shows you how to analyze the query, therefore go and do it.

Was this reply helpful? Yes No
Khurram 655 on at

Like (0)

Report

RE: Need help understanding cause of long-running SSRS report

Hi Briyan,

Did you find solution for this issue because I am also facing same problem while using AX 2012 R2, for few projects, the report is showing but when select 2 months, then cannot see the report rather an infolog error message for time out:-

"A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond".

I think the problem is in the report (ProjListProjProfitLossProjDP class which is data provider for ProjProfitLossProj SSRS Report) itself rather that increasing time out settings.

Please let me know if you found some hotfix or resolution at your end for this MS bug.

Thanks.

Pir.

Was this reply helpful? Yes No
dolee 11,279 on at

Like (0)

Report

RE: Need help understanding cause of long-running SSRS report

Hi Bryan,

Have you try trace a few more times and seeing that the same query consumes much time? If yes, then I'll look into that for sure.

Regards,

Dominic

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Need help understanding cause of long-running SSRS report

When you put it into SSMS, hit Query / Include Actual Execution Plan. See if that says you are missing anything.

Was this reply helpful? Yes No
bking48 215 on at

Like (0)

Report

RE: Need help understanding cause of long-running SSRS report

Nitesh,

Thank you, this helps my understanding greatly.

I'm continuing to investigate and will report back.

Bryan

Was this reply helpful? Yes No
bking48 215 on at

Like (0)

Report

RE: Need help understanding cause of long-running SSRS report

Dominic,

Thanks for this suggestion. Fascinating amount of information.

The trace shows a sql query that has an exclusive time of 4,500,000 ms. If I run it in SSMS, it takes less than 2 seconds. From my reading, an exclusive time is indeed the time that the selected line ran, correct? Is this an item of valid concern or is the data leading me astray?

Thanks.

Bryan

Was this reply helpful? Yes No
Dick Wenning 8,705 Moderator on at

Like (0)

Report

RE: Need help understanding cause of long-running SSRS report

have you deployed static designs for these slow reporrts

technet.microsoft.com/.../hh496446.aspx

it realy helps you

blogs.msdn.com/.../microsoft-dynamics-ax-2012-reporting-tips-how-to-improve-performance.aspx

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

RE: Need help understanding cause of long-running SSRS report

Please review the followin link

technet.microsoft.com/.../dd361996.aspx

This explains how AX and SSRS reporting Services share tasks related to report generation.

AX handles all the business logic execution part and it gets executed at AOS server level

SSRS need Dataset to generate report and after getting DataSet from AX SSRS report executes the Report level logic and renders the report.

Please verify and appreciated if you update with your findings

Was this reply helpful? Yes No
dolee 11,279 on at

Like (0)

Report

RE: Need help understanding cause of long-running SSRS report

Hi Bryan,

Generally, AX AOS handles the data query part. For example, RDP classes would generate data in temp tables. Then SSRS will collect these data to generate the report.

Try use the AX Trace parser to collect some data and see if you can locate any long queries.

Regards,

Dominic

Was this reply helpful? Yes No