ReportServer Forum

VasanthR

Member

Registered: 2018-10-16

Track long running report in background at Report server

I am looking for ways to track long running report that are executed by user and running in background at Report Server as we are seeing some reports that was executed were running for long time and held 100% CPU utilization in reporting database server. I am new to RS support, would be nice if I know the ways to track those report and terminate before it reaches 100% utilization at db server. Also I need to know how to setup report timeout capability in Report Server portal so that I can define time limitation to terminate long running report queries before it get escalated. Thanks for your help in advance! (vs: Enterprise Edition RS3.0.5)

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Track long running report in background at Report server

Dear VasanthR,

the response time of the BI Systems should be fast enough for the user not to become impatient (ideally). This can be achieved in most of the cases known to us be preprocessing the requested dataset prior to access by reportserver (i.e. overnight). If you deal with millions of rows you might as well consider using a columns store to achieve fast results for the given set of selected columns. Thus said the optimization approach has limits. There is a minimum processing time for a given setup especially if you consider that reportserver itself capsules the queries and makes them more complex by selecting columns and filtering attributes.

There is a way to track running reports in the system:

SELECT initial.date_field, final.date_field, initial.action, final.action,  initial.KEY_FIELD, initial.VALUE FROM
(SELECT E.*, P.KEY_FIELD, P.VALUE
   FROM RS_AUDIT_LOG_ENTRY E,
        RS_AUDIT_LOG_PROPERTY P
  WHERE E.ENTITY_ID = P.LOG_ENTRY_ID AND E.ACTION IN ('REPORT_EXECUTION') AND P.KEY_FIELD = 'report_id'
) initial left join
(SELECT DISTINCT e.action, e.DATE_FIELD, P.VALUE
   FROM RS_AUDIT_LOG_ENTRY E,
        RS_AUDIT_LOG_PROPERTY P
  WHERE E.ENTITY_ID = P.LOG_ENTRY_ID AND E.ACTION IN ('END_REPORT_EXECUTION','REPORT_EXECUTED_FAILED') AND P.KEY_FIELD in ( 'report' , 'report_id' )
) final on initial.VALUE = final.VALUE
where final.value is null
order by final.DATE_FIELD desc

Note the P.KEY_FIELD in ( 'report' , 'report_id' ). In the current version, the "report" is correct, while in the next version "report_id" is correct. So the query above should always work. I raised ticket RS-3166 for this purpose.

We will consider implementing a "currently running reports" view in future versions: RS-3163.

Regards,
Eduardo

VasanthR

Member

Registered: 2018-10-16

Re: Track long running report in background at Report server

Thanks for posting query to track long running report and it helps!
I am also looking for tables detail that being created on Reportserver internal datasource - (Mariadb). Appreciate if you can share Mariadb data dictionary if exist as I need to know tables and its purposes. I am wondering if there is way to terminate or end long running report that were identified from the reportserver portal or please share you thought on this.