Pages

Wednesday, 6 March 2013

SSRS: Report Server Usage Report

While working with SSRS (SQL Server Reporting Service) it is very common to look into the Execution log to understand the usage pattern of the report that are deployed or to troubleshoot the performance issues.SSRS stores the detailed execution plan in a table named ExecutionLog which resides in the SSRS repository database.

Below script will give you the details of report execution in last 24 hours.

SELECT c.path,c.name,
CASE 
   WHEN el.RequestType = 0 THEN 'Interactive'
  
WHEN el.RequestType = 1 THEN 'Subscription'
  
WHEN el.RequestType = 2 THEN 'Refresh Cache'END AS RequestType,  

 el.Format,
el.TimeStart,
el.TimeEnd,
DATEDIFF(ss,el.TimeStart,el.TimeEnd) AS 'TotalDuration(Sec)',
(
el.TimeDataRetrieval/1000.00) AS 'Data Retrieval 
Time (Sec)',
(el.Timeprocessing/1000.00) AS 'Processing Time(Sec)',
(
el.TimeRendering/1000.00) AS 'Rendering Time(Sec)',

CASE 
WHEN el.Source=1 THEN 'LIVE'
WHEN el.Source=2 THEN 'Cache'
WHEN el.Source=3 THEN 'Snapshot'
WHEN el.Source=4 THEN 'History'
WHEN el.Source=5 THEN 'Ad hoc(Report Builder)'
WHEN el.Source=6 THEN 'Session'
WHEN el.Source=7 THEN 'Report Definition Customization Extension(RDCE)'
END AS 'Source',
el.status,
el.ByteCount/1024.00 AS 'Size(Kb)',
el.[RowCount] AS 'Number of Records'
FROM ExecutionLog EL INNER JOIN Catalog cON c.itemid=el.reportid
WHERE TimeStart>= DATEADD(hh,-24,GETDATE())ORDER BY TimeStart DESCLet us look into the output columns :



  • Path : Report path in the the reports manager
  • Name : Report Name
  • Request Type :This tells you how the report got executed . Interactive is the manual execution of the report using the report manager or a custom UI. Report executed as part of scheduled subscription will have value subscription for this column.
  • Format : The rendering format
  • TimeStart : Report process start time
  • TimeEnd : Report process end time . The difference between the TimeStart and TImeEnd gives you the total duration of the request.
  • Data Retrieval time (Sec) : The number of seconds spent on data sources and data extenstions in main report and all of its subreports. If the multiple data source are accessed parallel, this will be be the duration of the longest data set  duration.If the data sources are accessed sequentially, this will be the sum of all data set duration.
  • Processing Time (Sec) : The number of seconds spent in the processing the request. This include report processing bootstrap time and processing time for grouping,sorting,filtering ,aggregation ,subreport processing etc.
  • Rendering Time : The number of seconds spent on rendering extension.This includes time spent on rendering, pagination module, on demand expression evaluation etc.
  • Source :Specifies the type of execution.
    • Live : data set  queries are executed to get the result
    • Cache: Reports is generated based on the data in the cache. Data set queries are not excuted
    • Session :Subsequent request with an existing session .For example, initial request to view the report and subsequent request is to export to pdf.
    • RDCE: Indicates Report Definition Customization Extension, that can dynamically customize a report definition before it is passed to the processing engine during the report execution
  • Status : Report execution status . rssuccess denote the success of report execution.
  • Size(KB) : Number of Kb generated as output of this report execution request.
  • Number of Records:Number of rows processed.

By execution log retention period is 60 days. If your environment requires a change on this value , you can modify this through the advanced page of report server properties after connecting the SSRS in the SSMS. Refer the technet page.The other option is change the value directly in the configuration table.

SELECT * FROM ConfigurationInfo WHERE Name = 'ExecutionLogDaysKept'



If you liked this post, do like my page on FaceBook

11 comments:

  1. I like your way of writing SSRS: Report Server Usage Report.Thanks so much for sharing me.
    The above script will give me the details of report execution in last 24 hours.

    ReplyDelete
    Replies
    1. A listing of all users with their unique IDs and authentication types. This table proved to be the Buy Essay Online least useful as usernames are stored in a readable form in the above tables.

      Delete


  2. تسليك مجارى بالاحساء تسليك مجارى بالاحساء
    تسليك مجارى بالدمام تسليك مجارى بالدمام
    تنظيف بيارات بالرياض تنظيف بيارات بالرياض
    كشف تسربات المياه بالاحساء كشف تسربات المياه بالاحساء
    كشف تسربات المياه بالدمام كشف تسربات المياه بالدمام
    كشف تسربات المياه بالرياض كشف تسربات المياه بالرياض

    ReplyDelete
  3. Wow! Nice article. Good Information Shared by the Author. Donate Zakat to irredeemable vagrants and widows and assist with giving them a more joyful and better life. Penny Appeal has a 100 percent Zakat strategy, which implies each penny of your Zakat gift will go straightforwardly to the people who need it most. Give today!

    ReplyDelete
  4. Shafi Dropship helps you to launch your turnkey dropship store without hiring designers. shopify dropshipping store Get your own money making dropshipping stores in USA, UK & Canada.

    ReplyDelete
  5. Wow! Nice article. Good Information Shared by the Author. When you Donate Zakat with us, we use it to assist with battling destitution through feasible change or give quick crisis help when emergency hits. Through your liberal Zakat or Sadaqah gift, kids will get food, Pay Zakat Onlinemedication and schooling to work toward a more joyful, more promising time to come.

    ReplyDelete