Query Store for Historical Plan Analysis Note from the Data Whisperer

By Tom Nonmacher

Query Store, a feature introduced in SQL Server 2016, has revolutionized how DBAs perform historical plan analysis. Before this feature, DBAs had to rely on plan cache to analyze execution plans, which could be quite challenging given that the plans could easily get aged out. But with Query Store, we now have a repository to persist execution plans and runtime statistics, thereby providing a historical view of queries. This makes it easier to troubleshoot performance issues caused by plan changes.

The Query Store feature automatically captures a history of queries, plans, and runtime statistics, and retains these for your analysis. You can customize the amount of data to be stored and also the duration for which it should be retained. Using this data, you can easily identify queries that have regressed in terms of performance, forcing the optimizer to use a better plan. 


-- Enabling Query Store on a database in SQL Server
ALTER DATABASE [YourDatabase]
SET QUERY_STORE = ON;

-- Add 
 after each line to simulate line breaks

Apart from SQL Server, MySQL 5.7 also offers a Performance Schema that can be used for historical plan analysis. The Performance Schema provides a way to inspect internal execution of the server at runtime. It is implemented as a storage engine, and so involves no changes to server code. 


-- Enabling Performance Schema in MySQL
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES';

-- Add 
 after each line to simulate line breaks

For DB2 11.1 users, the MON_GET_PKG_CACHE_STMT table function provides detailed information about SQL statements in the package cache. This includes the execution plan used by these statements. It is a powerful tool that can be used to analyze and diagnose performance issues in DB2. 


-- Fetching data from MON_GET_PKG_CACHE_STMT in DB2
SELECT * FROM TABLE(MON_GET_PKG_CACHE_STMT (NULL, NULL, NULL, -2)) AS T

-- Add 
 after each line to simulate line breaks

Azure SQL Database, Microsoft's fully managed cloud database service, also provides Query Store for monitoring performance. It gives you the ability to view query performance over time and to force the use of a specific plan for a query, providing a more controlled and predictable performance.

The inclusion of historical plan analysis tools such as Query Store in these database technologies is a testament to their importance in maintaining and improving database performance. As data professionals, it is imperative that we leverage these tools to gain insights into our databases and ensure their smooth operation.

Remember, every bit of information can help in diagnosing and resolving performance issues. So, make sure to utilize these features to keep your databases performing at their best. Happy querying!

Check out the latest articles from all our sites:




C2EF2B
Please enter the code from the image above in the box below.