Free SQL Help for Devs, DBAs, and the Curious

In a world that thrives on data, speed is king. Slow running reports can be a source of frustration for users and a headache for developers. In this post, we will explore how to identify bottlenecks in SQL Server Reporting Services (SSRS) datasets, which are often the culprits behind slow SSRS reports. We'll be using SQL Server 2016, SQL Server 2017, MySQL 5.7, DB2 11.1, and Azure SQL in our examples.

When it comes to diagnosing slow SSRS reports, the first place to look is the SQL Server Profiler. This tool is an invaluable resource for examining the performance of your SQL Server instances. By using the SQL Server Profiler, you can isolate long-running queries that might be slowing down your SSRS reports. Once you've identified a potential culprit, you can use the query plan to help determine why the query is slow.

-- Use SQL Server Profiler to trace the query
EXEC sp_trace_create @options = 2, @tracefile = N'trace.trc';
EXEC sp_trace_setevent @traceid = 1, @eventid = 10, @columnid = 1, @on = 1;
EXEC sp_trace_setstatus @traceid = 1, @status = 1;
-- Run the slow query
SELECT * FROM LargeTable;
-- Stop the trace
EXEC sp_trace_setstatus @traceid = 1, @status = 0;

Another tool to consider is the SQL Server Management Studio (SSMS). Within SSMS, the "Include Actual Execution Plan" option can be a great help in identifying bottlenecks. This creates a graphical representation of the data retrieval process, highlighting the most costly operations. This can guide your optimization efforts.

In SQL Server 2017 and Azure SQL, the Query Store feature can be very useful for tracking performance over time. The Query Store retains a history of query execution plans with their performance data, and can quickly identify queries that have become slower over time. It also allows you to force the server to use a specific plan for a query.

-- Enable Query Store
ALTER DATABASE CurrentDB SET QUERY_STORE = ON;
-- Check the Query Store for high-cost queries
SELECT top 10 qsrs.query_id, qsrs.plan_id, qsrs.avg_duration
FROM sys.query_store_runtime_stats qsrs
JOIN sys.query_store_plan qsp ON qsrs.plan_id = qsp.plan_id
ORDER BY qsrs.avg_duration DESC;

In MySQL 5.7, the Performance Schema can help identify slow queries. The 'events_statements_summary_by_digest' table provides statistics about SQL statements, including the average execution time.

-- Check for slow queries in MySQL
SELECT schema_name, digest, avg_timer_wait
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC;

For DB2 11.1, the MON_GET_PKG_CACHE_STMT table function provides detailed information about SQL statement performance. You can use this to identify slow-running queries in your SSRS datasets.

-- Check for slow queries in DB2
SELECT PKGNAME, AVG_EXEC_TIME
FROM TABLE(MON_GET_PKG_CACHE_STMT (NULL, NULL, NULL, -2)) AS T
ORDER BY AVG_EXEC_TIME DESC;

In conclusion, bottlenecks in SSRS datasets can significantly slow down your reports. By using the right tools and techniques to identify and address these bottlenecks, you can greatly improve the performance of your SSRS reports. Remember, a well-optimized dataset is the key to fast, efficient reports.