SQL Server 2016 Query Store–Low Overhead Flight Recorder For Your Database

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:

image

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 sys.query_store_runtime_stats catalog.

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:

sys.query_store_query_text Query Text Information
sys.query_store_query Query text plus the used plan affecting SET options
sys.query_store_plan Execution Plans
sys.query_store_runtime_stats Query runtime statistics
sys.query_store_runtime_stats_interval Start and End Times for Capture Intervals
sys.query_context_settings 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:

Capture

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:

image

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:

image

Looking at top resource consumers by duration I can see this query and its execution plan, which is most important:

image

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….

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s