Applies to
ApexSQL Plan
Summary
This article describes how to set and use the Query store feature to review and get query execution data collected from the Query store on SQL Server versions 2008 and higher.
Description
This feature gives a detailed overview of query performance inside the SQL Server instances. With the Query store feature easily can be fiends the most expensive queries for Memory, CPU, I/O. Can detriment how many time a query was executed. Get wait statistics information etc.
To start tracing the query performance in ApexSQL Plan, under the Home tab, press the Query store button:
After clicking the Query store button, the Database connection window will appear:
Here, set the SQL Server for which to track the query performance, then, select a database for tracking and click the OK button.
If the SQL Server Query store is not already activated on the targeted database, the grid in the Data tab of the ApexSQL Plan Query store will be empty:
To start tracking executed queries, click the Start Query store button and, on the warning message box, click the Yes button to allow ApexSQL Plan to enable SQL Server Query store on the targeted database:
After clicking the Yes button, the SQL Server Query store will be enabled:
After a while, the collected data will be displayed in the Data tab:
The ApexSQL Plan Query store uses the SQL Server Query Store to trace executed queries for a database, that are located on the SQL Server 2016 and higher.
More about SQL Server Query Store can be seen on the SQL Server Query Store – Overview page.
If the database to track is located on SQL Server 2008 through SQL Server 2014, the ApexSQL Plan Query store will install the OpenQueryStore feature in that database in order to enable tracking the executed queries and collects query runtime execution statistics, wait statistics information:
More about OpenQueryStore can be seen on the OpenQueryStore for SQL Server – Installation and configuration page.
The results in the Data tab can be copied or exported to the XML or HTML file format:
By clicking the View execution plan command from the context menu, the execution plan will be shown under the Management tab:
Query store dashboard
In the Dashboard tab, top queries based on average duration, execution count, executed rows etc. can be reviewed. Easily scroll down to review query details and export desired diagrams:
Query store options
Under the Properties tab, the various options of how the Query store will track and collect a data can be set:
The Data flush interval (min) is the option in which can be set the frequency of which the query execution plans and query statistics data will be moved from SQL Server memory to disk. 15 minutes is set by default. If, in the Data flush interval (min) box, a lower value is set then the flushed frequency from SQL Server memory to a disk will appear more often, which may have a negative performance impact on SQL Server instance (will become slower). On the other hand, if the value in the Data flush interval (min) box is too high, then more the query execution plans and query statistics data will be collected in the memory of the SQL Server instance before that data are flushed to a disk. In this way, the risk of data loss is increased in case of SQL Server instance suddenly stops working.
In the Desired state drop-down box, the state of the Query store feature can be defined. Here can be set three states (modes):
The Off mode– When this value in the Desired state is set then the Query stored is turned off.
The Read only mode – When this value is set then the new executed plans and statistics will not be tracked (collected).
The Read write mode – All executed plans and runtime statistics will be collected, tracked.
The Max plans per query is the option in which can be set a maximum number of plans per each query. By default, the number is 200:
The Max size on disk (MB) options represents the maximum size of the Query store. By default, the size is set to 100 MB:
The Query store data are stored in a database which the Query store is enabled for. Once the size is reached, the Desired state option will be switched to the Read only mode.
The Size based cleanup mode option takes care of the Query store storage:
When 90% of the set value in the Max size on disk (MB) box is reached, the cleaning process will activate and remove the oldest and less expensive data. The cleanup process stops at 80% of the storage size which is set in the Max size on disk (MB) box. By default, the Auto value is set in the Size based cleanup mode box. If the Off value in the Size based cleanup mode box is set, the cleaning process at the 90% of the max storage size set in the Max size on disk (MB) box will not be activated and the Query store will switch to the Read only mode when the max storage size is reached.
The Stale query threshold (days) option is used to set duration to retain query store runtime statistics. By default, it is set to 30 days:
In the Statistics collection interval option, the granularity can be set at which runtime execution statistics are aggregated into the Query store. By default, this is set to 1 hour. If the lower value is set, then the granularity of the query runtime statistics will be finer which means more disk space for storing query runtime statistics:
In the Query store capture mode drop-down box, which queries will be captured in the Query store can be defined. There are three modes that can be chosen:
- All capture all executed queries and stored them in the Query store
- Auto capture queries based on resource consumption
- None stops to capture new queries and will continue to collect information only for the queries that are been previously recorded
By default, it is set to All:
At the bottom of the Properties tab, six additional buttons for setting of the Query store are located:
The Defaults button is used to set the Query store default values.
- The Apply button is used to apply the set values in the Properties tab to the Query store.
- The Clear button clears all data from the Query store.
- The Flush button flushes collected data to disk.
- The Restart button restarts Query store.
- The Restore button sets all properties under the Properties tab to their default values and applies to Query store.
FAQs:
Q: What happens when the Apply button in the Properties tab of the Query store window is pressed?
A: All values that are set in the fields under the Properties tab will be copied to the SQL Server Query Store fields:
But if the OpenQueryStore is activated in a database which is located on SQL Server 2008 through 2014 and the Apply button is pressed, the following info message box will appear:
Q: Which permissions are needed for using Query store?
A: The minimum permission needed for using the ApexSQL Plan Query store is VIEW DATABASE STATE permission:
USE AdventureWorks2016CTP3 GO GRANT VIEW DATABASE STATE TO [USER];
Otherwise, the following error message will appear:
Q: Can Query store be enabled on the SQL Server instance level?
A: No. The ApexSQL Plan Query store feature can be enabled on database level, it can be enabled on the SQL Server databases separately
