Query Store Custom Reports - Rishan Solutions

Certainly! Here’s a comprehensive and detailed guide on Query Store Custom Reports in SQL Server, covering their purpose, creation, and best practices.

🧠 Introduction to Query Store Custom Reports

Query Store is a feature introduced in SQL Server 2016 that captures a history of queries, plans, and runtime statistics, providing a rich source of information for performance tuning and troubleshooting. While SQL Server Management Studio (SSMS) offers built-in reports for analyzing this data, there are scenarios where custom reports are necessary to meet specific business requirements or to present the data in a more meaningful way.

Custom Reports allow database administrators and developers to tailor the presentation of Query Store data, enabling more efficient analysis and decision-making.

🔧 Enabling Query Store

Before creating custom reports, ensure that Query Store is enabled on your database:

Using SSMS:
- Right-click on the database in Object Explorer.
- Select Properties.
- Navigate to the Query Store page.
- Set the Operation Mode (Requested) to Read Write.
- Click OK.
Using T-SQL: ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

Note: Enabling Query Store may have a performance impact, especially on write-heavy systems. It’s advisable to monitor the system after enabling it to ensure that performance remains acceptable.

📊 Understanding Built-in Query Store Reports

Before diving into custom reports, familiarize yourself with the built-in reports provided by SSMS:

Regressed Queries: Identifies queries whose performance has degraded over time.
Top Resource Consuming Queries: Displays queries that consume the most resources.
Wait Statistics: Shows the wait types and their associated queries.
Forced Plans: Lists queries with forced execution plans.

These reports can be accessed by right-clicking on the Query Store node under a database in SSMS.

🛠️ Creating Custom Reports

Custom reports in SSMS are typically created using SQL Server Reporting Services (SSRS) and saved as .rdl files. Here’s a step-by-step guide:

1. Designing the Report

Define the Purpose: Determine what information you need. For example, you might want a report showing the top 10 queries by CPU usage over the last 24 hours.
Identify Data Sources: Use the Query Store dynamic management views (DMVs) such as:
- sys.dm_db_query_store_query
- sys.dm_db_query_store_plan
- sys.dm_db_query_store_runtime_stats
Design the Layout: Plan how the data will be presented—tables, charts, or graphs.

2. Developing the Report in SSRS

Open SQL Server Data Tools: Launch SQL Server Data Tools or Visual Studio with the SSRS extension.
Create a New Report Project: Select Report Server Project.
Add a Data Source: Connect to your SQL Server instance.
Define Datasets: Write SQL queries against the Query Store DMVs to retrieve the necessary data.
Design Report Items: Use tables, charts, and other items to present the data.
Preview the Report: Ensure that the report displays the data correctly.

3. Deploying the Report to SSMS

Save the Report: Once satisfied, save the report as an .rdl file.
Add to SSMS:
- Right-click on a database in SSMS.
- Navigate to Reports > Custom Reports.
- Select Add and browse to the .rdl file.
- The report will now be available under Reports > Custom Reports for that database.

Note: Custom reports are added at the object level where you right-clicked. For example, if you added the custom report at the database level, it will only be visible when you right-click on that database.

📈 Example: Top 10 Queries by CPU Usage

To create a report displaying the top 10 queries by CPU usage over the last 24 hours:

SQL Query: SELECT TOP 10 q.query_id, q.query_text_id, qt.query_sql_text, SUM(rs.total_cpu_time) AS total_cpu_time FROM sys.dm_db_query_store_query AS q JOIN sys.dm_db_query_store_query_text AS qt ON q.query_text_id = qt.query_text_id JOIN sys.dm_db_query_store_runtime_stats AS rs ON q.query_id = rs.query_id WHERE rs.timestamp > DATEADD(HOUR, -24, GETDATE()) GROUP BY q.query_id, q.query_text_id, qt.query_sql_text ORDER BY total_cpu_time DESC;
Report Design:
- Data Source: Connect to the database containing Query Store data.
- Dataset: Use the above SQL query.
- Table: Display query_sql_text and total_cpu_time.
- Chart: Optionally, add a bar chart to visualize CPU usage by query.

📊 Advanced Reporting Techniques

For more advanced reporting:

Parameterized Reports: Allow users to filter data by date range, query type, etc.
Drill-through Reports: Enable users to click on a data point to view more detailed information.
Subreports: Embed one report within another to provide a hierarchical view of data.

Note: While SSMS supports custom reports, there are limitations. For instance, custom reports cannot be executed automatically to prevent the execution of malicious code. Additionally, subreports, expressions on the query, and non-text queries are not supported.

🧰 Best Practices for Custom Reports

Performance Considerations: Complex queries can impact server performance. Test reports during off-peak hours.
Security: Ensure that only authorized users can access custom reports.
Documentation: Document the purpose and usage of each custom report.
Regular Updates: Periodically review and update reports to align with changing business requirements.

🧪 Real-World Scenario: Identifying Plan Regressions

A common use case for custom reports is identifying queries that have experienced plan regressions:

SQL Query: SELECT q.query_id, qt.query_sql_text, p.plan_id, p.is_forced, rs.avg_duration FROM sys.dm_db_query_store_query AS q JOIN sys.dm_db_query_store_query_text AS qt ON q.query_text_id = qt.query_text_id JOIN sys.dm_db_query_store_plan AS p ON q.query_id = p.query_id JOIN sys.dm_db_query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id WHERE rs.timestamp > DATEADD(D