In this article, I'm introducing you new feature in SQL Server 2016 CTP 2.0 named Query Store. This is a very useful feature for the DBA and developers from the performance point of view.
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 you can enable it in two ways:
Step 1
First Using SSMS, Right Click on DatabaseName -> Go to properties -> Query Store options -> Enable -> True
Step 2
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.
Step 3
To determine the current options available for query store we can query the system view sys.database_query_store_options. Query stores contains two stores:
- Plan store – Stores execution plan information
- Running Stats store – Stores execution statistics information
HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
