CREATE DATABASE QueryStoreTest
GO
USE QueryStoreTest;
GO
ALTER DATABASE QueryStoreTest
SET QUERY_STORE = ON
(
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 1 ),
DATA_FLUSH_INTERVAL_SECONDS = 900,
MAX_STORAGE_SIZE_MB = 50,
INTERVAL_LENGTH_MINUTES = 60,
SIZE_BASED_CLEANUP_MODE = AUTO,
MAX_PLANS_PER_QUERY = 10,
WAIT_STATS_CAPTURE_MODE = ON,
QUERY_CAPTURE_MODE = ALL /* this is required for the testing */
);
GO
创建并运行一个耗时的查询。我用过这个：
SELECT
*
FROM sys.all_columns AS ac
CROSS JOIN sys.all_parameters AS ap
CROSS JOIN sys.all_objects AS ao
在 SSMS 中运行查询完成或停止执行，查询可以在查询存储中找到。
SELECT
qsq.query_id
, qsq.query_hash
, qsp.plan_id
, qsqt.query_sql_text
, qsrs.count_executions
, qsrs.execution_type_desc
FROM sys.query_store_query AS qsq
LEFT JOIN sys.query_store_plan AS qsp
ON qsp.query_id = qsq.query_id
LEFT JOIN sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id
JOIN sys.query_store_runtime_stats AS qsrs
ON qsrs.plan_id = qsp.plan_id
WHERE qsqt.query_sql_text LIKE '%cross join%'
AND qsqt.query_sql_text NOT LIKE '%query_store%'
You'll now have a new Extended Event and a windows displaying the current output of this Extended Event.
Change Configuration
Right-click the session and stop it. Right-click again and go into the properties:
Select the sql_transaction and then click on Configure >...
...switch to the Filters tab and delete the current filter. Add a new filter with the values duration, greater than and 60000000 (microseconds).
Click on OK and then start your session again in the Extended Events. You might have to select Watch Live Data again to have a current window open.
The Fun Part
Now you have an Extended Event Session which will track statements that take longer than 60 seconds. Run a statement on that server with something like this:
BEGIN TRAN
UPDATE DemoDB.dbo.Tweets SET TweetText = 'something else' WHERE TweetID = 2;
WAITFOR DELAY '00:02';
COMMIT TRAN;
After a minute you should see an event pop up in your Watch Live Data window in SSMS. It might look like this:
You can read the statement and with the plan_handle you can even go and query the sys.dm_exec_text_query_plan DMV to have a look at the execution plan:
SELECT
CAST(query_plan AS XML)
FROM sys.dm_exec_text_query_plan(0x06000100C0187C0A601FD200D902000001000000000000000000000000000000000000000000000000000000,0,-1)
Which shows:
Click on the XML link and voilà:
This (in my example) execution plan can then be used to see if things are running ok, or if you might benefit from additional indexes or query tuning.
Answering Your Question
Is there any option to see killed sessions in Query Store?
If the statement has run before, but didn't take longer than your timeout, then these queries might be found in the Query Store.
If the statement terminates, because something changed, then maybe. It depends on the reason for the time-out.
他们没有。我已经测试过了。
创建数据库并启用查询存储。
创建并运行一个耗时的查询。我用过这个：
在 SSMS 中运行查询完成或停止执行，查询可以在查询存储中找到。
执行类型是Regular或Aborted。但是，如果您从另一个窗口终止会话，则count_executions既不会增加，也不会出现新记录。
您可能需要考虑使用较新版本的 SQL Server 提供的扩展事件（快速启动扩展事件）功能仅跟踪花费超过一定时间的查询。
让我们为您创建一个扩展事件，用于捕获运行时间超过 60 秒的语句（只是一个示例）。
导航到 SSMS 中的扩展事件
在这里，您可以右键单击Sessions分支以打开上下文菜单。
新会议...
与标记的会话相反，我们将使用空的New Session...选项。
新会话向导介绍
点击下一步 >
新建会话向导设置会话属性
我们将为会话命名：Statement Duration > 1 Minute，然后单击Start the event session at server startup选项，然后单击Next > ..
新会话向导选择模板
我们不会使用默认模板。下一个 >
新会话向导选择要捕获的事件
我们将只使用sql_transaction事件并使用相应的>箭头将其向右移动。下一步 >。
新会话向导捕获全局字段
我们将至少选择sql_text字段，但我添加了更多类似的内容：
...然后点击Next >。
新会话向导设置会话事件过滤器
我们将暂时过滤sqlos.task_execution_time并将值设置为6000。我们将返回此设置以将其更改为duration。点击下一步>。
新建会话向导指定会话数据存储
您可以在此处指定要将数据存储在文件或 ring_bugger 目标中的位置。我们暂时使用一个文件。配置适合您的服务器的值，然后单击Next >。
New Session Wizard Summary
Validate your configuration and click on Next >.
New Session Wizard Create Event Session
Select both options to have something running and then click on Next >.
Extended Events | Sessions | Statement Duration > 1 Minute
You'll now have a new Extended Event and a windows displaying the current output of this Extended Event.
Change Configuration
Right-click the session and stop it. Right-click again and go into the properties:
Select the sql_transaction and then click on Configure >...
...switch to the Filters tab and delete the current filter. Add a new filter with the values duration, greater than and 60000000 (microseconds).
Click on OK and then start your session again in the Extended Events. You might have to select Watch Live Data again to have a current window open.
The Fun Part
Now you have an Extended Event Session which will track statements that take longer than 60 seconds. Run a statement on that server with something like this:
After a minute you should see an event pop up in your Watch Live Data window in SSMS. It might look like this:
You can read the statement and with the plan_handle you can even go and query the
sys.dm_exec_text_query_planDMV to have a look at the execution plan:Which shows:
Click on the XML link and voilà:
This (in my example) execution plan can then be used to see if things are running ok, or if you might benefit from additional indexes or query tuning.
Answering Your Question
If the statement has run before, but didn't take longer than your timeout, then these queries might be found in the Query Store.
If the statement terminates, because something changed, then maybe. It depends on the reason for the time-out.
按照我的解决方案中的描述创建一个扩展事件，而不是将其设置为 30 分钟，而是将超时设置为 29 分 59 秒，或者使用
1799000000微秒的过滤器值。您现在应该能够在实时数据或创建的文件中跟踪长时间运行的事务。