0

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.

  • I'm not quite sure what you are trying to achieve? Are you trying to see how long it takes for a report to render? Or why your report is taking so long to load? – DimUser Mar 26 '18 at 14:13
  • I'm trying to see how long it takes the load to complete and I'm not sure if the Pre-Render handler will work if it hangs. Thought about using the trace attribute? – IrishChieftain Mar 26 '18 at 14:15
1

To see how long a report takes to load, and what stage is taking up the time use this in SSMS:

USE ReportServer  
SELECT * FROM ExecutionLog3 ORDER BY TimeStart DESC

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

| improve this answer | |
  • I can only see an ExecutionLogStorage table which has these fields. I tried running this: "SELECT * FROM ExecutionLogStorage ORDER BY TimeDataRetrieval DESC" but got Exception of type 'System.OutOfMemoryException'. – IrishChieftain Mar 26 '18 at 14:51
  • ExecutionLog3 is a view, which references the ExecutionLogStorage table. The view is more user friendly. Do you need to do this in code-behind? You could take the ItemID for your report from [dbo].[Catalog] and then get the MAX TimeEnd from the ExecutionLog for ReportID? That should give you your end time. Is there a reason you need to do this? Bear in mind that additional calculations like this will most likely increase the load time of a report. – DimUser Mar 26 '18 at 15:22
  • I don't know which reports the ReportID refers to. I'm simply trying to time the execution of reports - I need to do this to gauge the success or fail of changes I am making to indexes. – IrishChieftain Mar 26 '18 at 15:25
  • Thanks for your help - I had to use SQLCMD tool to get around the memory exception issue. – IrishChieftain Mar 26 '18 at 16:42
  • For others with the same memory exception issue: docs.microsoft.com/en-us/sql/relational-databases/scripting/… – IrishChieftain Mar 26 '18 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.