Exporting SQL Server 2016 Query Store

By Mike Boswell – Data Platform Solution Architect.

As a Data Solution Architect, we regularly work with clients to pilot new data platforms and with SQL Server 2016 an increasing number of clients are looking to modernise their SQL Server, Oracle and DB2 Platforms.

During the piloting phase we will run performance labs and pilot new features. Now one of these new features which gives us a lot of benefit, both in these labs, and in production is the Query Store (https://msdn.microsoft.com/en-GB/library/dn817826.aspx) in SQL Server 2016.

However, there is one "query store challenge" with this in both a Dev, Test and Production environments. During Dev/Test the database is often restored after a test and we needed a way to keep the query store data for analysis. Likewise, production would like to provide feedback to development on performance enhancements.

Microsoft have yet to produce a utility which will extract query store data, into user database, and this blog explains how you can do this to gain insight into your database performance.

To achieve this, it led me down the path of having to look to export the data from Query Store, build Primary Keys and Columnstore Indexes (columnstore indexeshttps://msdn.microsoft.com/en-us/library/gg492088.aspx).. Primary Keys, created as clustered indexes, to support point lookups and Non Clustered Columnstore Indexes to support aggregations.

Once we had this, we could back up the database and share the output with between dev and test teams now that the performance metric data is safely captured.

Note: The rest of this blog is based on the WorldWideImporters database from https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0.

Setting up Query Store

For details on setting up Query Store, in SQL Server 2016, please refer to "Monitoring Performance By Using the Query Store and a Channel 9 vid at https://channel9.msdn.com/Shows/Data-Exposed/Query-Store-in-SQL-Server-2016.

Once setup and you have workload running then you should start seeing data returned by running the following DMVs:

• sys.query_store_plan (Transact-SQL)
• sys.query_store_query (Transact-SQL)
• sys.query_store_query_text (Transact-SQL)
• sys.query_store_runtime_stats (Transact-SQL)
• sys.query_store_runtime_stats_interval (Transact-SQL)

and start using Query Store as documented.

Setting up a User Query Store

We need to set up a user database to be able to pull down the data from the Query Stored and the script below:

• Create a Database
• Select from the WorldWideImporters DB (alter to your DB)
• Import sys.objects table
• Create Primary Keys
• Create ColumnStore Indexes to support aggregation of data

/* DISCLAIMER: This code is not supported under any Microsoft standard support program or service. This code and information are provided "AS IS" without warranty of any kind, either expressed or implied. The entire risk arising out of the use or performance of the script and documentation remains with you. Furthermore, Microsoft or the author shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, including, without limitation, damages for loss of business profits, business interruption, loss of business information or other pecuniary loss even if it has been advised of the possibility of such damages. */ createdatabase [UserQueryStore]GO ALTERDATABASE [UserQueryStore] SETRECOVERYSIMPLE GO USE [UserQueryStore]GO CREATETABLE [dbo].[user_sys_objects]    (     userobjectname nvarchar(128)NOTNULL,     userobjectid intNOTNULL,     userobjecttype char(2)NOTNULL     )ON [PRIMARY]GO select*into [dbo].[user_query_store_plan] from [WideWorldImporters].[sys].[query_store_plan]; select*into [dbo].[user_query_store_query] from [WideWorldImporters].[sys].[query_store_query]; select*into [dbo].[user_query_store_query_text] from [WideWorldImporters].[sys].[query_store_query_text]; select*into [dbo].[user_query_store_runtime_stats] from [WideWorldImporters].[sys].[query_store_runtime_stats]; select*into [dbo].[user_query_store_runtime_stats_interval] from [WideWorldImporters].[sys].[query_store_runtime_stats_interval]; insertinto [dbo].[user_sys_objects]([userobjectname],[userobjectid],[userobjecttype])select [name], [object_id],[type] from [WideWorldImporters].[sys].[objects]; INSERTINTO [dbo].[user_sys_objects]([userobjectname],[userobjectid]         ,[userobjecttype]) VALUES ('Ad-Hoc Query', 0,'AD') GO --Add Primary Keys and ColumnStore Indexes ALTERTABLE dbo.user_query_store_plan ADDCONSTRAINT     PK_user_query_store_plan PRIMARYKEYCLUSTERED     (     plan_id    )WITH(STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY]GO ALTERTABLE dbo.user_query_store_query ADDCONSTRAINT     PK_user_query_store_query PRIMARYKEYCLUSTERED     (     query_id    )WITH(STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY]GO ALTERTABLE dbo.user_query_store_query_text ADDCONSTRAINT     PK_user_query_store_query_text PRIMARYKEYCLUSTERED     (     query_text_id    )WITH(STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY]GO ALTERTABLE dbo.user_query_store_runtime_stats ADDCONSTRAINT     PK_user_query_store_runtime_stats PRIMARYKEYCLUSTERED     (     runtime_stats_id    )WITH(STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY]GO ALTERTABLE dbo.user_query_store_runtime_stats_interval ADDCONSTRAINT     PK_user_query_store_runtime_stats_interval PRIMARYKEYCLUSTERED     (     runtime_stats_interval_id    )WITH(STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY]GO --ColumnStores CREATENONCLUSTEREDCOLUMNSTOREINDEX [user_query_store_plan-CS] ON [dbo].[user_query_store_plan](     [plan_id],     [query_id],     [plan_group_id],     [engine_version],     [compatibility_level],     [is_online_index_plan],     [is_trivial_plan],     [is_parallel_plan],     [is_forced_plan],     [is_natively_compiled],     [force_failure_count],     [last_force_failure_reason],     [last_force_failure_reason_desc],     [count_compiles],     [initial_compile_start_time],     [last_compile_start_time],     [last_execution_time],     [avg_compile_duration],     [last_compile_duration])WITH (DROP_EXISTING=OFF,COMPRESSION_DELAY= 0) GO CREATENONCLUSTEREDCOLUMNSTOREINDEX [user_query_store_query_text-CS] ON [dbo].[user_query_store_query_text](     [query_text_id],     [is_part_of_encrypted_module],     [has_restricted_text])WITH (DROP_EXISTING=OFF,COMPRESSION_DELAY= 0) GO CREATENONCLUSTEREDCOLUMNSTOREINDEX [user_query_store_query-cs] ON [dbo].[user_query_store_query](     [query_id],     [query_text_id],     [context_settings_id],     [object_id],     [is_internal_query],     [query_parameterization_type],     [query_parameterization_type_desc],     [initial_compile_start_time],     [last_compile_start_time],     [last_execution_time],     [last_compile_batch_offset_start],     [last_compile_batch_offset_end],     [count_compiles],     [avg_compile_duration],     [last_compile_duration],     [avg_bind_duration],     [last_bind_duration],     [avg_bind_cpu_time],     [last_bind_cpu_time],     [avg_optimize_duration],     [last_optimize_duration],     [avg_optimize_cpu_time],     [last_optimize_cpu_time],     [avg_compile_memory_kb],     [last_compile_memory_kb],     [max_compile_memory_kb],     [is_clouddb_internal_query])WITH (DROP_EXISTING=OFF,COMPRESSION_DELAY= 0)ON [PRIMARY]GO CREATENONCLUSTEREDCOLUMNSTOREINDEX [user_query_store_runtime_stats_interval-cs] ON [dbo].[user_query_store_runtime_stats_interval](     [runtime_stats_interval_id],     [start_time],     [end_time])WITH (DROP_EXISTING=OFF,COMPRESSION_DELAY= 0)ON [PRIMARY]GO CREATENONCLUSTEREDCOLUMNSTOREINDEX [user_query_store_runtime_stats-cs] ON [dbo].[user_query_store_runtime_stats](     [runtime_stats_id],     [plan_id],     [runtime_stats_interval_id],     [execution_type],     [execution_type_desc],     [first_execution_time],     [last_execution_time],     [count_executions],     [avg_duration],     [last_duration],     [min_duration],     [max_duration],     [stdev_duration],     [avg_cpu_time],     [last_cpu_time],     [min_cpu_time],     [max_cpu_time],     [stdev_cpu_time],     [avg_logical_io_reads],     [last_logical_io_reads],     [min_logical_io_reads],     [max_logical_io_reads],     [stdev_logical_io_reads],     [avg_logical_io_writes],     [last_logical_io_writes],     [min_logical_io_writes],     [max_logical_io_writes],     [stdev_logical_io_writes],     [avg_physical_io_reads],     [last_physical_io_reads],     [min_physical_io_reads],     [max_physical_io_reads],     [stdev_physical_io_reads],     [avg_clr_time],     [last_clr_time],     [min_clr_time],     [max_clr_time],     [stdev_clr_time],     [avg_dop],     [last_dop],     [min_dop],     [max_dop],     [stdev_dop],     [avg_query_max_used_memory],     [last_query_max_used_memory],     [min_query_max_used_memory],     [max_query_max_used_memory],     [stdev_query_max_used_memory],     [avg_rowcount],     [last_rowcount],     [min_rowcount],     [max_rowcount],     [stdev_rowcount])WITH (DROP_EXISTING=OFF,COMPRESSION_DELAY= 0) GO

Once we have the data in our user database we can take the queries mentioned in "Monitoring Performance By Using the Query Store" and alter them to point to our user tables. The queries include the user_sys_objects table to be able to display the name of the object that the query is part of. If the query is part of an ad-hoc query then the object name will display as ad-hoc.

/* /* DISCLAIMER: This code is not supported under any Microsoft standard support program or service. This code and information are provided "AS IS" without warranty of any kind, either expressed or implied. The entire risk arising out of the use or performance of the script and documentation remains with you. Furthermore, Microsoft or the author shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, including, without limitation, damages for loss of business profits, business interruption, loss of business information or other pecuniary loss even if it has been advised of the possibility of such damages. */ USE [UserQueryStore]GO --Last N queries that were executed on the database SELECTTOP 10 qt.query_sql_text, q.query_id, qt.query_text_id, p.plan_id, rs.last_execution_time, so.userobjectname, so.userobjecttypeFROM     dbo.user_query_store_query_text qt JOIN     dbo.user_query_store_query q ON qt.query_text_id = q.query_text_id JOIN     dbo.user_query_store_plan p ON q.query_id = p.query_id JOIN     dbo.user_query_store_runtime_stats rs ON p.plan_id = rs.plan_id JOIN     dbo.user_sys_objects so on so.userobjectid = q.object_id ORDERBY rs.last_execution_time DESC GO --Count of executions for each query SELECT q.query_id, qt.query_text_id, qt.query_sql_text, SUM(rs.count_executions)AS total_execution_count, so.userobjectname, so.userobjecttypeFROM     dbo.user_query_store_query_text qt JOIN     dbo.user_query_store_query q ON qt.query_text_id = q.query_text_id JOIN     dbo.user_query_store_plan p ON q.query_id = p.query_id JOIN     dbo.user_query_store_runtime_stats rs ON p.plan_id = rs.plan_id JOIN     dbo.user_sys_objects so on so.userobjectid = q.object_id GROUPBY q.query_id, qt.query_text_id, qt.query_sql_text,so.userobjectname, so.userobjecttypeORDERBY total_execution_count DESC GO -- Top N queries with longest average execution time within last hour SELECTTOP 10 qt.query_sql_text, q.query_id, qt.query_text_id, p.plan_id, getutcdate()as CurrentUTCTime, rs.last_execution_time, rs.avg_duration, so.userobjectname, so.userobjecttypeFROM     dbo.user_query_store_query_text qt JOIN     dbo.user_query_store_query q ON qt.query_text_id = q.query_text_id JOIN     dbo.user_query_store_plan p ON q.query_id = p.query_id JOIN     dbo.user_query_store_runtime_stats rs ON p.plan_id = rs.plan_id JOIN     dbo.user_sys_objects so on so.userobjectid = q.object_id --WHERE rs.last_execution_time > dateadd(hour, -1, getutcdate()) ORDERBY rs.avg_duration DESC GO --Top N queries that had the biggest average physical IO reads in last 24 hours, --with corresponding average row count and execution count SELECTTOP 10 qt.query_sql_text, q.query_id, qt.query_text_id, p.plan_id, rs.runtime_stats_id, rsi.start_time, rsi.end_time, rs.avg_physical_io_reads, rs.avg_rowcount, rs.count_executions, so.userobjectname, so.userobjecttypeFROM     dbo.user_query_store_query_text qt JOIN     dbo.user_query_store_query q ON qt.query_text_id = q.query_text_id JOIN     dbo.user_query_store_plan p ON q.query_id = p.query_id JOIN     dbo.user_query_store_runtime_stats rs ON p.plan_id = rs.plan_id JOIN     dbo.user_query_store_runtime_stats_interval rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id JOIN     dbo.user_sys_objects so on so.userobjectid = q.object_id --WHERE rsi.start_time >= dateadd(hour, -24, getutcdate()) ORDERBY rs.avg_physical_io_reads DESC GO -- Queries that recently regressed in performance -- The following query example returns all queries which execution time was -- doubled in last 48 hours. SELECT     qt.query_sql_text,     q.query_id,     qt.query_text_id,     p1.plan_id AS plan1,     rsi1.start_time AS runtime_stats_interval_1,     rs1.runtime_stats_id AS runtime_stats_id_1,     rs1.avg_duration AS avg_duration_1,     p2.plan_id AS plan2,     rsi2.start_time AS runtime_stats_interval_2,     rs2.runtime_stats_id AS runtime_stats_id_2,     rs2.avg_duration AS plan2,     so.userobjectname,     so.userobjecttypeFROM     dbo.user_query_store_query_text qt JOIN     dbo.user_query_store_query q ON qt.query_text_id = q.query_text_id JOIN     dbo.user_query_store_plan p1 ON q.query_id = p1.query_id JOIN     dbo.user_query_store_runtime_stats rs1 ON p1.plan_id = rs1.plan_id JOIN     dbo.user_query_store_runtime_stats_interval rsi1 ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id JOIN     dbo.user_query_store_plan p2 ON q.query_id = p2.query_id JOIN     dbo.user_query_store_runtime_stats rs2 ON p2.plan_id = rs2.plan_id JOIN     dbo.user_query_store_runtime_stats_interval rsi2 ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id JOIN     dbo.user_sys_objects so on so.userobjectid = q.object_id WHERE     rsi1.start_time >dateadd(hour,-1000,getutcdate())AND     rsi2.start_time > rsi1.start_time AND     rs2.avg_duration > 2*rs1.avg_duration

Conclusion

Once you have this running you are ready to go to be able to share an insight into dev/test and production whilst keeping a snapshot of where the performance was on a particular date. Combining this with a lightweight performance monitor collection, during testing, takes away all the hassle of running a SQLDiag and then waiting to import/aggregate the data via the ReadTrace utility (Part of RML Utilities).