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

QueryStore_1

Second way to enable it by using ALTER Database script in this manner:

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:

  1. Plan store – Stores execution plan information
  2. 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.

QueryStore_2

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