Tracking SSRS Report Usage by DMVs

tracking-ssrs-report-usage-dbameta

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

tracking ssrs report usage analysis dbameta

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;
Test code in dev environment before executiong on prod*

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;
Test code in dev environment before executiong on prod*

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;
Test code in dev environment before executiong on prod*

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;
Test code in dev environment before executiong on prod*

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:

tracking ssrs report usage more ways dbameta

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’
Test code in dev environment before executiong on prod*

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
Test code in dev environment before executiong on prod*

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:

What are the benefits of tracking SSRS report usage with DMVs?

Cost-effective: It leverages built-in functionalities within SQL Server, eliminating the need for additional software purchases.
Granular data: You can retrieve detailed data about report executions, including report names, execution times, and potentially user information.
Flexibility: You can customize T-SQL queries to analyze specific aspects of report usage based on your needs.

What are some limitations of using DMVs for SSRS report usage tracking?

User identification: DMVs might not always identify users, especially when Windows Authentication is used.
Limited visualization: Extracted data needs additional processing or tools for user-friendly visualization.
Technical expertise: Crafting effective T-SQL queries requires some understanding of SQL Server querying language.

When might it be better to consider a third-party solution for SSRS report usage tracking?

Advanced user identification: If user identification beyond Windows Authentication is crucial.
Detailed reporting and analytics: If you require in-depth analysis features and pre-built reports on report usage.
Simplified visualization: If you need user-friendly dashboards and visualizations without extensive data manipulation.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top