如何控制长时间运行的查询

Question

我们目前管理运行存储过程的网络应用程序，其中一个存储过程在用户搜索特定项目时被触发。不幸的是，有一天我们遇到了一个非常严重的问题，因为这个存储过程变得很慢，导致数据库性能非常差。最终这给我们的应用程序带来了许多问题。我通过运行以下数据库脚本发现长时间运行的查询是引起问题的原因:

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
where
    sqltext.[Text] NOT LIKE '--''check%'
ORDER BY req.cpu_time DESC

所以，我们为了解决这个问题，执行了KILL [SESSION_ID]命令，几秒钟后，应用程序就恢复正常了。现在，我们希望能够主动处理这种类型的问题。当我说“控制”时，我想知道是否可能使Web应用程序能够在一定时间后优雅地终止会话（不会导致后续问题），或者它应该由SQL Server自己来处理？如果还需要进一步澄清，请随意发表评论。

- Josh Monreal

---

你需要优化那些性能较差的查询，这是唯一的选择。 - TheGameiswar

你的应用程序中是否使用了连接超时？ - Vasanthan

也许可以尝试这个：https://dev59.com/t1HTa4cB1Zd3GeqPQ1sD#4005428 - daniell89

真正的解决方法当然是修复你的查询。如果无法完成修复，那么在SQL Server中有一种名为资源管理器（Resource Governor）的东西：https://msdn.microsoft.com/zh-cn/library/bb933866.aspx。它应该能够满足你的需求，但我必须承认我自己从未使用过它。 - Evk

存储过程使用了许多参数值，因为它被用于基于特定筛选器搜索项目。这个问题的痛点在于无法知道选择了哪些筛选器导致查询运行时间非常长。 - Josh Monreal

Answer 1

你真的需要这个 where 条件吗？ sqltext.[Text] NOT LIKE '--''check%' 1. sys.dm_exec_requests 还有一个开始时间列，你目前没有使用 where 子句。传入一个开始时间，所以它不会去到所有数据的开头。 2. 查看数据并尝试相应地修改 where 子句。 3. 获取 proc 的执行计划，我相信它将执行表扫描，这是不好的。

以下是获取执行计划的步骤 第一步 请修改日期、proc 数据库名称

select distinct top 1000 qs.plan_handle,o.name,d.name--,--ps.database_id
from sys.dm_exec_query_stats  qs
    ,sys.dm_exec_procedure_stats    ps
    ,sys.objects o
    ,sys.databases d
where qs.last_execution_time > '2017-03-29 17:06:42.340' 
and qs.last_execution_time < '2017-03-30 18:19:45.653'
and ps.sql_handle = qs.sql_handle
and o.object_id = ps.object_id
AND o.name = 'Your proc name here'
AND d.database_id = ps.database_id
AND d.name = 'database name '

第二步 将输出设置为网格并保存为 .sqlplan 如果您拥有足够的权限，您会获得一个链接，单击它可以打开它。请确保设置了查询输出选项，以便提供足够的空间进行 XML 输出。

select query_plan
from sys.dm_exec_query_plan (copy your handle here from step 1, do no use quotes)

- Cooker

---

我该如何获取这个执行计划？ - Josh Monreal

更新了答案，包括如何获取执行计划的细节。 - Cooker