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.
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
I like your way of writing SSRS: Report Server Usage Report.Thanks so much for sharing me.
ReplyDeleteThe above script will give me the details of report execution in last 24 hours.
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شركة نقل عفش بجدة
ReplyDeleteشركة غسيل مسابح بالدمام
شركة نقل العفش بالمدينة المنورة
ارخص شركات نقل العفش بالدمام
شركات نقل وتنظيف
ReplyDeleteشركات نقل عفش بالطائف
شركات نقل عفش بالمدينة المنورة
شركات نقل عفش بمكة
شركات نقل عفش
شركة تنظيف خزانات بالمدينة المنورة
Blogs Comments Services
ReplyDeletethanks for sharing send valentine's flowers online to oman
ReplyDelete
ReplyDeleteتسليك مجارى بالاحساء تسليك مجارى بالاحساء
تسليك مجارى بالدمام تسليك مجارى بالدمام
تنظيف بيارات بالرياض تنظيف بيارات بالرياض
كشف تسربات المياه بالاحساء كشف تسربات المياه بالاحساء
كشف تسربات المياه بالدمام كشف تسربات المياه بالدمام
كشف تسربات المياه بالرياض كشف تسربات المياه بالرياض
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!
ReplyDeleteShafi 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.
ReplyDeleteWow! 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
ReplyDeleteشركة تنظيف مكيفات بالاحساء
شركة عزل بالاحساء
شركة تسليك مجاري بالاحساء
شركة تعقيم بالاحساء
شركة مكافحة النمل لابيض بالاحساء
شركة مكافحة الحمام بالاحساء
شركة مكافحة حشرات بالاحساء