I'm working on a issue with heavily fragmented indexes on a large production DB. I've pretty much identified the indexes that are heavily fragmented, including those that are not really being used. I plan to rebuild some and remove others. So my next step is to devise a before and after time test.
One of the symptoms of this is SSRS reports taking about an hour to render. I'm new to Report Server. I can see that a report is being embedded in the ASPX page using a ReportViewer control with the ServerPort ReportPath and ReportServerUrl properties set. My problem is trying to figure out how to time the display of the report from start to finish in the code-behind. I can write the start time to a file in the Page_Load but I can't figure out how to record the end time… Pre-Render could just hang and I'm not sure if this is the only page lifecycle event I can tap into to record this. Should I use a Windows Service, and if so, how would I trigger/record the start and end times that way?
I'd really appreciate some feedback on whether this is possible via the display page's code-behind.
Best Answer
To see how long a report takes to load, and what stage is taking up the time use this in SSMS:
This will show you a log of the activity for each report being used. you will be able to see start time, end time (and calculate time taken), which parameters are being used, and the time taken for each of the stages of report loading (TimeDataRetrieval, TimeProcessing, TimeRendering) in milliseconds. Further details relating to the query here
Use this link to troubleshoot each stage, as per your findings