How Do I Check a Query Store?

by

Enabling and Using Query Store in SQL Server

Use the Query Store Page in SQL Server Management Studio. In Object Explorer, right-click a database, select Properties, Requires at least version 16 of Management Studio. In the Database Properties dialog box, select the Query Store page. In the Operation Mode (Requested) box, select Read Write.

The Query Store feature provides insight on query plan choice and performance. It simplifies performance troubleshooting by helping you quickly find differences caused by query plan changes. Query Store automatically captures a history of queries, plans, and runtime statistics, and retains these for your review.

Depending on the database and workload, Query Store will store more data until it reaches the max size. The database continues operating but new data will not capture because Query Store switches to read-only mode. SSMS provides graphical storage use, but not practical for environments with multiple databases.

To enable Query Store, use the following SQL command:

ALTER DATABASE SET QUERY_STORE = ON;

Note, you cannot enable it for master or tempdb databases. Query Store is not enabled by default for SQL Server and Azure Synapse Analytics databases, and is enabled by default for Azure SQL Database databases.

To check if Query Store is enabled for databases, execute the following query:

SELECT name, database_id, is_query_store_on FROM sys.databases;

Query store was introduced in SQL Server 2016 as a database level feature and is not enabled automatically; it must be enabled manually.

So far, enabling Query Store indicates a 3-5% average performance impact. Also, there is theoretically no upper limit on the number of tables joined in a SELECT statement, but the Database Engine limits it to 64 tables.

To verify Query Store configuration on a database, use the following query:

SELECT * FROM sys.database_query_store_options;

Checking Stored Procedures in SQL Server and Oracle

For SQL Server, stored procedures can be checked using the system views. However, for Oracle, the process is different. The following SQL snippet checks if a stored procedure exists in Oracle:

SELECT * FROM USER_SOURCE WHERE TYPE='PROCEDURE' AND NAME='my_stored_procedure';

To verify the functioning of a stored procedure programmatically, tools like DbFit can be used. For instance, stored procedures can be triggered by an insert into a specific table or an update to a specific field in a table.

To check the history change of stored procedures in SQL Server, you can query the USER_OBJECTS table as follows:

SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'PROCEDURE' AND OBJECT_NAME = 'MY_PROC_NAME';

To determine when a stored procedure was last executed in Oracle, the specific query or table that would provide this information is typically not readily available. Additional monitoring or logging may be required to capture such execution statistics.

Leave a Comment