First published on MSDN on Jul 30, 2015
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
