Using Query Store in SQL Database V12


Recently the SQL Product group released Query Store for SQL Database V12. The basic concept and usage is explained in this blog post. SQL Server Management Studio (SSMS) for SQL 16 has also been released as a standalone download (available here) so that SQL Database V12 customers can install it independently of the SQL Server setup.

The aim of this blog is to show you a couple of quick "how to" scenarios on using SSMS to look at Query Store data on SQL Database V12. Let us start by looking at a quick intro on how to enable the Query Store feature.

Here are the accompanying queries that you can run in SSMS against your user database.

–Landing page on MSDN

–https://msdn.microsoft.com/en-US/library/dn817826.aspx

 

–Turn Query Store On

ALTER DATABASE [AdventureWorks] SET QUERY_STORE=ON;

 

–Cleaning up the space

ALTER DATABASE [AdventureWorks] SET QUERY_STORE CLEAR;

 

–Get Query Store options

SELECT * FROM sys.database_query_store_options;

 

–Modify some Query Store options

ALTER DATABASE [AdventureWorks] SET QUERY_STORE (interval_length_minutes = 1)

–Determines the time interval at which runtime execution statistics data is aggregated into the query store

 

ALTER DATABASE [AdventureWorks] SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 90)

–Determines the frequency at which data written to the query store is persisted to disk asynchronously

 

–Turn Query Store Off

ALTER DATABASE [AdventureWorks] SET QUERY_STORE=OFF;

The most common scenario is to determine which queries are consuming resources on your database.

Here are the accompanying queries that you can run in SSMS against your user database.

— What are the top 10 long running queries for my database

select top 10 q.query_id, p.plan_id,

rs.count_executions,

CONVERT(NUMERIC(10,2), (rs.avg_cpu_time/1000)) as ‘avg_cpu_time_seconds’,

CONVERT(NUMERIC(10,2),(rs.avg_duration/1000)) as ‘avg_duration_seconds’,

CONVERT(NUMERIC(10,2),rs.avg_logical_io_reads ) as ‘avg_logical_io_reads’,

CONVERT(NUMERIC(10,2),rs.avg_logical_io_writes ) as ‘avg_logical_io_writes’,

CONVERT(NUMERIC(10,2),rs.avg_physical_io_reads ) as ‘avg_physical_io_reads’,

CONVERT(NUMERIC(10,0),rs.avg_rowcount ) as ‘avg_rowcount’

from sys.query_store_query q 

JOIN sys.query_store_plan p ON q.query_id = p.query_id

JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id

where rs.last_execution_time > dateadd(hour, 1, getutcdate())

order by rs.avg_duration desc

 

— What were the queries that were borted ( either by client/server)

select top 10 q.query_id, p.plan_id,

rs.execution_type_desc,

rs.count_executions,

CONVERT(NUMERIC(10,2), (rs.avg_cpu_time/1000)) as ‘avg_cpu_time_seconds’,

CONVERT(NUMERIC(10,2),(rs.avg_duration/1000)) as ‘avg_duration_seconds’

from sys.query_store_query q 

JOIN sys.query_store_plan p ON q.query_id = p.query_id

JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id

where rs.last_execution_time > dateadd(hour, 1, getutcdate())

where rs.execution_type <>0

order by rs.avg_duration desc

 

–How to obtain the query text for a particular query_id

select q.query_id,qt.query_sql_text

from sys.query_store_query q

JOIN sys.query_store_query_text qt ON q.query_text_id=qt.query_text_id

where q.query_id IN (18,7)

–More queries are available at https://msdn.microsoft.com/en-US/library/dn817826.aspx

Next we look at the how to use Query Store to detect plan regression and how to force a good execution plan for a particular.

Finally, when you open a case with Microsoft Support, we provide you with an SQL Database Data Collector that collects some basic DMV output from your SQL Database environment. We have enhanced this collector to also collect query store related DMVs if query store has been enabled for your database.

In conclusion we want to encourage all SQL Database customers to enable this new feature and use it to understand your workload.

Author: Rohit Nayak (@sqlrohit)
Reviewers: Keith Elmore, Adam Saxton (@GuyInACube)
Escalation Services, Microsoft

Comments (0)

Skip to main content