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:

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.

*/

create
database [UserQueryStore]

GO

ALTER
DATABASE [UserQueryStore] SET
RECOVERY
SIMPLE

GO

USE [UserQueryStore]

GO

CREATE
TABLE [dbo].[user_sys_objects]

    (

    userobjectname nvarchar(128)
NOT
NULL,

    userobjectid int
NOT
NULL,

    userobjecttype char(2)
NOT
NULL

    )
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];

insert
into [dbo].[user_sys_objects]([userobjectname],[userobjectid],[userobjecttype])
select [name], [object_id],[type] from [WideWorldImporters].[sys].[objects];

INSERT
INTO [dbo].[user_sys_objects]


([userobjectname]


,[userobjectid]

         ,[userobjecttype])


VALUES


(‘Ad-Hoc Query’, 0,‘AD’)

GO

–Add Primary Keys and ColumnStore Indexes

ALTER
TABLE dbo.user_query_store_plan ADD
CONSTRAINT

    PK_user_query_store_plan PRIMARY
KEY
CLUSTERED

    (

    plan_id

    )
WITH(
STATISTICS_NORECOMPUTE
=
OFF,
IGNORE_DUP_KEY
=
OFF,
ALLOW_ROW_LOCKS
=
ON,
ALLOW_PAGE_LOCKS
=
ON)
ON [PRIMARY]

GO

ALTER
TABLE dbo.user_query_store_query ADD
CONSTRAINT

    PK_user_query_store_query PRIMARY
KEY
CLUSTERED

    (

    query_id

    )
WITH(
STATISTICS_NORECOMPUTE
=
OFF,
IGNORE_DUP_KEY
=
OFF,
ALLOW_ROW_LOCKS
=
ON,
ALLOW_PAGE_LOCKS
=
ON)
ON [PRIMARY]

GO

ALTER
TABLE dbo.user_query_store_query_text ADD
CONSTRAINT

    PK_user_query_store_query_text PRIMARY
KEY
CLUSTERED

    (

    query_text_id

    )
WITH(
STATISTICS_NORECOMPUTE
=
OFF,
IGNORE_DUP_KEY
=
OFF,
ALLOW_ROW_LOCKS
=
ON,
ALLOW_PAGE_LOCKS
=
ON)
ON [PRIMARY]

GO

ALTER
TABLE dbo.user_query_store_runtime_stats ADD
CONSTRAINT

    PK_user_query_store_runtime_stats PRIMARY
KEY
CLUSTERED

    (

    runtime_stats_id

    )
WITH(
STATISTICS_NORECOMPUTE
=
OFF,
IGNORE_DUP_KEY
=
OFF,
ALLOW_ROW_LOCKS
=
ON,
ALLOW_PAGE_LOCKS
=
ON)
ON [PRIMARY]

GO

ALTER
TABLE dbo.user_query_store_runtime_stats_interval ADD
CONSTRAINT

    PK_user_query_store_runtime_stats_interval PRIMARY
KEY
CLUSTERED

    (

    runtime_stats_interval_id

    )
WITH(
STATISTICS_NORECOMPUTE
=
OFF,
IGNORE_DUP_KEY
=
OFF,
ALLOW_ROW_LOCKS
=
ON,
ALLOW_PAGE_LOCKS
=
ON)
ON [PRIMARY]

GO

–ColumnStores

CREATE
NONCLUSTERED
COLUMNSTORE
INDEX [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

CREATE
NONCLUSTERED
COLUMNSTORE
INDEX [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

CREATE
NONCLUSTERED
COLUMNSTORE
INDEX [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

CREATE
NONCLUSTERED
COLUMNSTORE
INDEX [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

CREATE
NONCLUSTERED
COLUMNSTORE
INDEX [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

SELECT
TOP 10 qt.query_sql_text, q.query_id, qt.query_text_id, p.plan_id, rs.last_execution_time, so.userobjectname, so.userobjecttype

FROM

    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

ORDER
BY 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.userobjecttype

FROM

    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

GROUP
BY q.query_id, qt.query_text_id, qt.query_sql_text,so.userobjectname, so.userobjecttype

ORDER
BY total_execution_count DESC

GO

— Top N queries with longest average execution time within last hour

SELECT
TOP 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.userobjecttype

FROM

    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())

ORDER
BY 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

SELECT
TOP 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.userobjecttype

FROM

    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())

ORDER
BY 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.userobjecttype

FROM

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

Comments (0)

Skip to main content