Query Store is perhaps one of the path breaking features to release in SQL Server 2016. This post will give a quick introduction to the same.
What is this query store?
Short answer: Records / Stores every query plan generated along with their run time statistics like Execution Count, Row count, Execution cost, IO and memory usage on to the user database.
What are the key benefits?
1) One can track the query run time performance easily at any point in time
2) Track change of a query plan or change in performance of a particular query over a period of time
3) Track regression - Find which queries started performing badly
4) Query plans will be part of user database backup
5) Ability to force plans via user graphical
interface ( unlike plan guides which demanded some scripting)
What else we get?
4 wonderful reports
1) Regression report -> To track queries that have performed badly over a period of time. One gets to compare historical performance against recent performance
2) Top consuming queries -> To find the poor performing queries by CPU / duration /IO last one hour etc
3) Overall performance report-> Reports average response times, total queries executed etc
4) Tracked queries -> useful to see the trend in performance of a particular query
How to configure?
Right Click on the Database -> Properties -> Query Store -> Set Operation mode to read write.
Rest can be left at default to get it started or you may use the script below
What is this query store?
Short answer: Records / Stores every query plan generated along with their run time statistics like Execution Count, Row count, Execution cost, IO and memory usage on to the user database.
What are the key benefits?
1) One can track the query run time performance easily at any point in time
2) Track change of a query plan or change in performance of a particular query over a period of time
3) Track regression - Find which queries started performing badly
4) Query plans will be part of user database backup
5) Ability to force plans via user graphical
interface ( unlike plan guides which demanded some scripting)
What else we get?
4 wonderful reports
1) Regression report -> To track queries that have performed badly over a period of time. One gets to compare historical performance against recent performance
2) Top consuming queries -> To find the poor performing queries by CPU / duration /IO last one hour etc
3) Overall performance report-> Reports average response times, total queries executed etc
4) Tracked queries -> useful to see the trend in performance of a particular query
How to configure?
Right Click on the Database -> Properties -> Query Store -> Set Operation mode to read write.
Rest can be left at default to get it started or you may use the script below
USE [master]
GO
ALTER DATABASE [DBNAME] SET QUERY_STORE = ON
GO
ALTER DATABASE [DBNAME]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE,
DATA_FLUSH_INTERVAL_SECONDS = 60, INTERVAL_LENGTH_MINUTES = 1)
GO
That's a decent start with query store. Upcoming posts will cover various bells and whistles in it.
