Hi friends, in next few blogs we will explore another new feature named Query Store which
gets introduced in SQL Server 2016 CTP 2.0. This is a very useful feature for the DBA and developers from the performance point of view.
Query store feature can now give to answer of question like:
My query was running properly yesterday but I don’t know why it is running slow and taking more time then yesterday even I didn’t make any changes in my query nor in my table structure.
Query store feature allows to captures multiple query plan for a query and run time statistics. Query store can store multiple execution plans per query, it can force query processor to use a particular execution plan which is referred as plan forcing using USE PLAN query hint.
By default, Query Store is not active so we can enable it in two ways:
First Using SSMS, Right Click on DatabaseName -> Go to properties -> Query Store options -> Enable -> True
Second way to enable it by using ALTER Database script in this manner:
ALTER DATABSE Database_name SET QUERY_STORE = ON;
Query store option is not enabled for master or tempdb database. If you try to enable it then you get below error:
Msg 12420, Level 16, State 1, Line 1
Cannot perform action because Query Store is not started up for this database.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Query stores contains two stores:
- Plan store – Stores execution plan information
- Running Stats store – Stores execution statistics information
To determine the current options available for query store we can query the system view sys.database_query_store_options.
That’s all folks for the day. We will continue with query store in next few blogs and covers how to use it and store multiple query plan for a query, system views and stored procedure supports query store and many other things.
Regards,
Kapil Singh Kumawat
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
