Monday, April 4, 2016

SQL Server 2016 - Query Store

Performance tuning is a constant topic of discussion, also one of the top priority items when it comes to good code development, better performing queries provide quicker response times, users/customers are satisfied. There have been constant improvements in the area of performance tuning features in SQL Server, also in the later releases of SQL Server, the execution plans were greatly improved. It is very important for developers to understand execution plans so that they improve queries that cause bottlenecks in the application. One of challenges have been to effectively store execution plans and queries so that they could be reviewed for bottlenecks and improve them. In SQL Server 2016 the concept of query store has been introduced. This Query store feature helps one to gain insight into the query plans and statistics.
In order to enable query store feature in SQL Server 2016, one can use Transact-SQL:

ALTER DATABASE AdventureWorks2012 SET QUERY_STORE = ON;

The query store feature in SQL Server 2016 can also be enabled through SQL Server Management Studio, by choosing the database properties/query store page.
Quoting from Microsoft MSDN Page: I am listing a couple of scenarios for using query store feature:
  • Quickly find and fix a plan performance regression by forcing the previous query plan. Fix queries that have recently regressed in performance due to execution plan changes.
  • Determine the number of times a query was executed in a given time window, assisting a DBA in troubleshooting performance resource problems.
The MSDN link for Query Store is listed below: The page below provides a more completion explanation of query store.

No comments:

Post a Comment