As you may be aware SQL Server 2016 is coming soon and SQL Server 2016 Community Technology Preview 2.4 is available for download here. http://www.microsoft.com/en-us/server-cloud/products/sql-server-2016/. This release is coming with plethora of new advanced features, one of which is Query Store.
Have you ever seen execution plan regressions after SQL Server upgrade? What about after release of new code? If so this feature can be of great help for you.
So what is SQL Server 2016 Query Store? The SQL Server Query Store feature provides DBAs with insight on query plan choice and performance. It simplifies performance troubleshooting by enabling you to quickly find performance differences caused by changes in query plans. The feature automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. It separates data by time windows, allowing you to see database usage patterns and understand when query plan changes happened on the server
How does this work? Every time SQL Server compiles or executes a query, a message is sent to the Query Store:
The compile and execution information is kept in memory first and then saved to disk, depending on the Query Store configuration (the data is aggregated according to the
INTERVAL_LENGTH_MINUTES parameter, which defaults to one hour, and flushed to disk according to the
DATA_FLUSH_INTERVAL_SECONDS parameter). The data can also be flushed to disk if there is memory pressure on the system. In any case you will be able to access all of the data, both in memory and disk, when you run the
The collected data is persisted on disk and stored in the user database where the Query Store is enabled (and settings are stored in
sys.database_query_store_options. The Query Store catalogs are:
||Query Text Information|
||Query text plus the used plan affecting SET options|
||Query runtime statistics|
||Start and End Times for Capture Intervals|
||Query context settings info|
To try feature out you can either download and install CTP of SQL 2016 or do what I did and setup a SQL Server 2016 VM in Microsoft Azure straight from VM gallery:
Few minutes and you have yourself a working SQL Server 2016 VM in the cloud. You need to enable the Query Store by using the
ALTER DATABASE CURRENT SET QUERY_STORE = ON; statement. It cannot be enabled for system databases such as model or tempdb. If you try to do so you will see something like:
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.
You can also see query store settings via SQL Server 2016 Management Studio GUI:
You may want to change defaults for the following options in order to adjust them to your requirements:
- Max Size MB – limit for the data space that Query Store will take inside your database. The default (100 MB) may not be sufficient if your workload generates large number of different queries or plans or if you want to keep query history for a longer period of time. Keep track of current space usage and increase configuration value to prevent Query Store from switching to “Read Only” mode when disk size exceeds the limit.
- Statistics Collection Interval: defines level of granularity for the runtime data (the default is 1 hour). Consider using lower value if you need finer granularity but keep in mind that it will directly affect the size of Query Store data.
Once the Query Store is enabled, it will start collecting the plans and query performance data and you can analyze that data by looking at the Query Store tables.
Query stores contains two stores:
- Plan store – Stores execution plan information
- Running Stats store – Stores execution statistics information
Next I will run a rather basic query against Adventure Works sample database on SQL Server 16 CTP:
SELECT con.* FROM Person.Person con JOIN HumanResources.Employee emp ON con.BusinessEntityID = emp.BusinessEntityID
Now I can go to Query Store folder right in SSMS:
Looking at top resource consumers by duration I can see this query and its execution plan, which is most important:
Now this query is elemental, but assuming there was really something to tune here, outside of using wildcard ( *) , which is always a bad practice or something to index we could add an index and then rerun , comparing both plans via Query Store. In case of upgrade optimizer issue we can force a particular plan via plan guide in worst case scenario as well.
For more on Query Store see – http://www.sqlservergeeks.com/introduction-to-query-store-in-sql-server-2016-part-1/, https://seniordba.wordpress.com/2015/06/05/query-store-in-sql-server-2016/, http://sqlperformance.com/2015/02/sql-plan/the-sql-server-query-store,
MSDN Docs are already available here – https://msdn.microsoft.com/en-us/library/dn817826.aspx, awesome video from Channel 9 – https://channel9.msdn.com/Shows/Data-Exposed/Query-Store-in-SQL-Server-2016, as well as presentation by Sergio Govoni – https://docs.com/sergio-govoni/1072/sql-server-2016-query-store?containerid=C25196531-6033-6307-8750-001094967533~Mf7ebd4f1-5a55-7f4f-3c59-b77a13ff6d00
Now lets get query tuning….