Handout to Know About SQL Server 2016 Query Data Store

About SQL Server 2016 Query Store

SQL Server 2016 has a useful feature embedded with it, which help end-users to improve the query performance. Such feature is termed as Query store that resolves all the problem, which occurs during query processing and stores information related to execution of query. You will be happy to know about SQL Server 2016 Query Store, if you don’t want to loose execution plan of queries when your server restart or upgrade. Moreover, it archives Execution plan of particular queries and one can determine this plan for a specific query. The feature is easy-to-use and is available to each users using this version of SQL server.

Applications of Query Store

• The feature enable users to examine that how much resources are being utilized by a query at its execution time.
• It performs auditing operation of query’s history, mentioned by user for accomplishing their tasks.
• One can determine that how many times a query has been executed on one window.
• Database administrator can resolve their problem related to query by making use of Query store.

Lets know how SQL Server 2016 Query Store is beneficial or we can say its advantage.

Advantages of SQL Server 2016 Query Store

• SQL Server users can enable Query Store feature on single database.
• A bulk amount of data is directly available through a Query Store reports.
• At initial level, the feature quickly capture the run-time information related to each query.

Enabling Query Store Feature

• You can enable the feature from database window panel itself. There is a Query Store property page in SQL database, by which you can enable this feature.



• By making use of T-SQL, you can also enable this feature by running the below-mentioned query:

Configuring SQL Server 2016 Query Store

After enabling the feature, time comes to properly configure it because if configuration is improper, then it will result in irrelevant functioning of the feature and may be the applications of machine too. Hence, there is need to properly configure Query Store feature.

The configuration is described in the below snapshot:

One can also configure Query Store feature by making use of T-SQL, also. Run the below-mentioned query in it:

So from above its clear that how to configure SQL Server 2016 Query Store.

Extended Events and Objects Related to Query Store

The feature comprises of six advanced systems and seven catalogs, which can be reviewed by running following query:

Moreover, there are 19 extended events of Query Store feature and they are described in below snapshot:

Query Store Architecture

Query Store Container

• In SQL Server 2016, the Query Store feature can be combined with SQL server Management Studio (SSMS). When you will enable this feature, a new ‘Query Store’ container will be generated in SSMS:



• For viewing the several properties of this container, right-click on it

Top Resource Consumers

A way to view the Query Store feature is by using a panel named as Top Resource Consumers

Number (1): A Metric drop down menu, which is in the left chart and its functioning is to display overall statistics of selected metrics (Horizontal or Vertical axis).

Number (2): A Statistic drop down menu, which has the same functioning as of Metric menu for both vertical and horizontal axis.

Number (3): Denotes the detail of statistics for a particular query_id or plan_id.

Number (4): Denotes the bubbles size that depends upon the number of executions.

Number (5): Upon clicking multiple plans, an execution plan will be displayed with its ID in a window pane

Number (6): Top Resource Consumer [db_name] is indicated

Number (7): Denotes Configurable value of Top 25

Number (8): Indicates Configurable value of last hour

Number (9): Monitored database name of qstore_test

Hope, You might clearly understood the view of SQL Server 2016 Query Store by using Top Resource Consumers. Below you will know about Horizontal/Vertical Axis and Grid of right chart in SQL Server 2016 Query Store.

Note: For refreshing both the charts i.e. left and right chart, use Refresh button for performing the same & the button is available on the left chart. Moreover, there is also a Refresh button on the right chart, but that will only refresh the right chart area not the left chart.

About Horizontal and Vertical Axis

• Horizontal Axis: Available only on Left Chart and depends upon the Statistics selected on the left chart



• Vertical Axis: Available on both chart i.e. Left and Right



• The menu of the left chart vertical axis consist of a new for improving the query performance.



• The right chart vertical axis comprise the list that is shown in below snapshot

Grid of Right Chart

From the Right chart, click on Grid and then select Vertical View option, which is in title bar

Number (1): ‘Track Query’ button, which is used to open the ‘Tracked Queries’ in front of you

Number (2): ‘View Query’ button that will allow you to operate the new SSMS window from T-SQL by executing a query

Number (3): ‘Detailed Grid’ button by which user can view top queries list with all its statistics.

Number (4): A ‘Grid’ button, provides a limited details of top queries list depending on Statistics and Metrics selected.

Number (5): A ‘Configure’ button that enables users for assembling the entire pane all at one places

Conclusion

After having a brief discussion upon this writeup, one can conclude up with the fact that SQL Server 2016 is embedded with very useful features that have multiple properties embedded with it. SQL Server 2016 Query Store is a best procedure to improve the performance of SQL query.