Introduction To Query Store In SQL Server 2016 – Part 1

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:

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:

  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 

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Kapil Singh Kumawat

Kapil Singh Kumawat has been working with SQL Server since last 5 years. He is from Jaipur, Rajasthan, India and currently working with Cognizant Technology Solutions as SQL Server Developer. He has good experience in performance tuning, SSIS, data migration and data designing. Apart from database he has interest in travelling, watching football and listening music.

View all posts by Kapil Singh Kumawat →

Leave a Reply

Your email address will not be published. Required fields are marked *