Tracking SSRS Report Usage by DMVs

Tracking SSRS Report Usage by DMVs: SQL Server Reporting Services (SSRS) is one of the popular service offered by Microsoft and used by many organizations for reporting puspose. You can generate many reports using this service. But if you’re managing a SQL Server instance that hosts multiple reports, you may want to keep track of which reports are being used by your users. This information can help you identify which reports are critical to your business and which ones can be retired. And it also help you during the migration of reports.

Understanding SSRS Execution Logs and DMVs

SSRS maintains a historical record of report executions within its internal database. These execution logs capture crucial information like report names, execution times, and user details (when available). Fortunately, SQL Server offers a set of pre-built DMVs that provide access to this valuable data. By crafting T-SQL queries targeting these DMVs, you can unlock a wealth of insights into your SSRS report usage patterns.

Read More: Tips and Techniques for Optimal Database Performance

Essential DMVs for SSRS Report Usage Tracking

Two primary DMVs play a central role in tracking SSRS report usage:

1) ExecutionLog:

This core DMV offers essential data points such as report name, execution start and end times, user who ran the report (if applicable), and parameters used.

2) ExecutionLog3:

This DMV expands upon ExecutionLog by including additional details like rendering time, data retrieval time, and row count processed during report execution.

These DMVs offer a powerful foundation for crafting T-SQL queries to analyze your SSRS report usage data. (Tracking SSRS Report Usage)

T-SQL Queries for Report Usage Analysis

T-SQL queries that leverage the aforementioned DMVs to extract valuable usage data:

Identifying Most Frequently Accessed Reports

This query helps you understand which reports are being used the most within a specific timeframe.

SQL SELECT TOP 10 c.Name AS ReportName, COUNT(*) AS ExecutionCount FROM [ReportServer].[dbo].[ExecutionLog] AS e INNER JOIN [ReportServer].[dbo].[Catalog] AS c ON e.ReportID = c.ItemID WHERE e.TimeStart >= @StartDate AND e.TimeStart <= @EndDate GROUP BY c.Name ORDER BY ExecutionCount DESC;

Explanation:

• TOP 10: This clause limits the results to the top 10 most frequently accessed reports.
• c.Name AS ReportName: This renames the “Name” column from the Catalog table to a more descriptive “ReportName” for clarity.
• COUNT(*) AS ExecutionCount: This calculates the total number of executions for each report.
• @StartDate and @EndDate: These represent placeholder variables for defining the desired date range for analysis. You’ll need to replace them with actual date values in your query. (Tracking SSRS Report Usage)

---

Analyzing Report Usage by User (if applicable):

This query retrieves report usage data with user identification (assuming Windows Authentication isn’t used).

SQL SELECT c.Name AS ReportName, u.UserName, COUNT(*) AS ExecutionCount FROM [ReportServer].[dbo].[ExecutionLog] AS e INNER JOIN [ReportServer].[dbo].[Catalog] AS c ON e.ReportID = c.ItemID LEFT JOIN [ReportServer].[dbo].[User] AS u ON e.UserID = u.UserID WHERE e.TimeStart >= @StartDate AND e.TimeStart <= @EndDate GROUP BY c.Name, u.UserName ORDER BY ExecutionCount DESC;

Explanation:

• LEFT JOIN [ReportServer].[dbo].[User] AS u ON e.UserID = u.UserID: This joins the User table to potentially identify the user who executed the report (if available).
• u.UserName: This includes the user’s name in the result set.
• LEFT JOIN is used because user identification might not be available for all report executions.

---

Examining Report Execution Times

This query investigates report execution times to identify potential performance bottlenecks.

SQL SELECT c.Name AS ReportName, AVG(DATEDIFF(ms, e.TimeStart, e.TimeEnd)) AS AvgExecutionTimeMS FROM [ReportServer].[dbo].[ExecutionLog] AS e INNER JOIN [ReportServer].[dbo].[Catalog] AS c ON e.ReportID = c.ItemID WHERE e.TimeStart >= @StartDate AND e.TimeStart <= @EndDate GROUP BY c.Name ORDER BY AvgExecutionTimeMS DESC;

Explanation (continued):

• AVG(DATEDIFF(ms, e.TimeStart, e.TimeEnd)) AS AvgExecutionTimeMS: This calculates the average execution time for each report in milliseconds.

---

Tracking Report Usage Trends Over Time

This query helps visualize report usage trends over a specific period.

SQL SELECT DATEADD(day, DATEDIFF(day, 0, e.TimeStart), 0) AS ExecutionDate, c.Name AS ReportName, COUNT(*) AS ExecutionCount FROM [ReportServer].[dbo].[ExecutionLog] AS e INNER JOIN [ReportServer].[dbo].[Catalog] AS c ON e.ReportID = c.ItemID WHERE e.TimeStart >= @StartDate AND e.TimeStart <= @EndDate GROUP BY DATEADD(day, DATEDIFF(day, 0, e.TimeStart), 0), c.Name ORDER BY ExecutionDate, ReportName;

Explanation:

• DATEADD(day, DATEDIFF(day, 0, e.TimeStart), 0) AS ExecutionDate: This groups the data by the actual execution date, providing a daily breakdown of report usage. (Tracking SSRS Report Usage)

---

More ways to Track Stats

SQL Server provides a set of Dynamic Management Views (DMVs) that can be used to monitor currently executing queries and their associated reports:

Step 1: Join sys.dm_exec_requests with sys.dm_exec_sql_text

The first DMV we’ll use is sys.dm_exec_requests, which provides information about currently executing requests. We’ll join this DMV with sys.dm_exec_sql_text to retrieve the text of the query associated with each request.

Here’s an example query:

SQL SELECT r.session_id, r.start_time, DB_NAME(r.database_id) AS database_name, CASE WHEN s.host_name = ” THEN ‘Dedicated’ ELSE s.host_name END AS host_name, s.program_name, CASE WHEN s.login_name = ” THEN ‘N/A’ ELSE s.login_name END AS login_name, SUBSTRING(qt.text, (r.statement_start_offset/2)+1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE r.statement_end_offset END – r.statement_start_offset)/2)+1) AS query_text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) qt INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id WHERE r.session_id > 50 — Exclude system sessions AND r.status = ‘running’

This query retrieves information about all currently running queries, including the session ID, start time, database name, host name, program name, login name, and query text. Note that we exclude system sessions (which have session IDs below 50) and only include queries that are currently running.

Step 2: Join sys.objects to retrieve report names

Now that we have the query text, we need to identify which reports are associated with each query. To do this, we’ll join the sys.objects catalog view to retrieve information about the report associated with the query.

Here’s an example query:

SQL SELECT r.session_id, r.start_time, DB_NAME(r.database_id) AS database_name, CASE WHEN s.host_name = ” THEN ‘Dedicated’ ELSE s.host_name END AS host_name, s.program_name, CASE WHEN s.login_name = ” THEN ‘N/A’ ELSE s.login_name END AS login_name, SUBSTRING(qt.text, (r.statement_start_offset/2)+1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE r.statement_end_offset END – r.statement_start_offset)/2)+1) AS query_text, o.name AS report_name FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) qt INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id LEFT OUTER JOIN ( SELECT i.object_id, OBJECT_NAME(i.object_id) AS name, COALESCE(p.value, ”) AS report_path FROM sys.dm_exec_cached_plans p CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) a INNER JOIN sys.dm_exec_query_stats s ON p.plan_handle = s

Visualizing Report Usage Data for Better Insights

Once you’ve crafted your T-SQL queries and retrieved the report usage data, it’s crucial to present it in a user-friendly format. Here are some effective methods:

• Reporting Services: Utilize SSRS itself to create custom reports based on your T-SQL queries. This allows you to design visually appealing reports with charts and graphs, making it easier to identify trends and patterns.
• Data Visualization Tools: Consider leveraging third-party data visualization tools like Power BI or Tableau. These tools offer powerful features to create interactive dashboards that showcase report usage data in a clear and impactful way.

Optimizing Your SSRS Report Usage Tracking

Here are some additional tips to refine your SSRS report usage tracking approach:

• Schedule Regular Reports: Automate report generation using SQL Server Agent jobs to receive reports on report usage at regular intervals (e.g., weekly or monthly).
• Filter and Refine Data: Utilize WHERE clauses in your T-SQL queries to filter data based on specific reports, users, or date ranges for a more focused analysis.
• Data Security Considerations: Remember to adhere to data security protocols when accessing and analyzing report usage data. Ensure you have the necessary permissions and handle sensitive information responsibly. (Tracking SSRS Report Usage)

Conclusion

By leveraging DMVs and crafting targeted T-SQL queries, you can effectively track SSRS report usage without relying on external solutions. This empowers you to make informed decisions about your reporting strategy, optimize report performance, and ensure your SSRS environment caters to user needs. By combining this approach with data visualization tools, you can gain valuable insights that drive continuous improvement within your SSRS ecosystem.

---

FAQ: