Frequently Asked Questions
Frequently Asked Questions
Getting insight of SQL Server Query Performance with Query Store Feature
Posted by Harri Rantala (M-Files) on 28 June 2019 04:27 PM

Overview

If you are using SQL Server as M-Files database backend the SQL Server Query Store feature can be used to get an insight on query performance of the vault database. It continuously collects and presents detailed historic information about all queries and thus helps performance troubleshooting within a query level. It stores query data with a time reference so database usage patterns can be observed. It is recommended to enable Query Store, especially if you are having performance problems, as collected query data could be used to pinpoint performance issues by M-Files Support. Note that this feature requires at least SQL Server version 2016. Also please note that there is a slight performance impact on the server due to the query store if it has been enabled.

Enabling Query Store

The Query Store is not active by default, but it can be activated by using the Query Store Page in SQL Server Management Studio in Object Explorer, right clicking a vault database, and then clicking Properties. In the Database Properties dialog box, select the Query Store page and Read Write selection in the Operation Mode (Requested) box. You can also use Transact-SQL statement: ALTER DATABASE <vault_database> SET QUERY_STORE (OPERATION_MODE = READ_WRITE);.

Note that Query Store saves its data inside the database and it has size limit (configurable in Query Store page or with ALTER DATABASE <vault_database> SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>); statement). The default value (100 MB) may not be sufficient when a longer period of time of query history is needed, so it is recommended to increase it at least to 1 GB. If data in Query Store hits the size limit, Query Store will automatically change state from read-write to read-only and stop collecting new data. This can be prevented by ensuring that the size based cleanup mode is set to Auto.

It will take some time until Query Store collects the data set that accurately represents the query usage. One day is usually enough to collect data but in case of M-Files the period might have to be longer as there are some operations which are not, by default, run daily but weekly instead.

Available Query Store Views

To see Query Store troubleshooting views in SQL Server Management Studio, refresh the database portion of the Object Explorer pane by pressing F5 and navigate to the Query Store sub-folder under the database node. Following statistic function views are available:

  • Regressed Queries: Pinpoint queries for which execution metrics have recently regressed (i.e. changed to worse).
  • Overall Resource Consumption: Analyze the total resource consumption for the database for any of the execution metrics.
  • Top Resource Consuming Queries: Choose an execution metric of interest and identify queries that had the most extreme values for a provided time interval.
  • Queries With Forced Plans: Lists previously forced plans using Query Store. Query plan analysis and forcing query plans needs higher level expertise on SQL Server.
  • Queries With High Variation: Analyze queries with high execution variation as it relates to any of the available dimensions, such as Duration, CPU time, IO, and Memory usage in the desired time interval.
  • Query Wait Statistics: A useful view to analyze wait categories that are most active in a database, and which queries contribute most to the selected wait category. This is available from SQL Server Management Studio v18.0 and SQL Server 2017 (14.x).
  • Tracked Queries: Track the execution of the most important queries in real time.

For other usage and more detailed information on Query Store see Microsoft SQL Docs page:

https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-2017

(0 vote(s))
This article was helpful
This article was not helpful

Help Desk Software by Kayako case