数据库 - 请问 SQL Server 显示锁或是超时，但在 session 都查不到有卡的 sql，请问如何还原追踪当时是哪些sql跟参数造成的问题?

Question

请问 SQL Server 显示锁或是超时，但在 session 都查不到有卡的 sql，请问如何还原追踪当时是哪些sql跟参数造成的问题?

举例: 系统在11-12点显示几个事务锁或是超时

事务(进程 ID 97)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。  或是事务(进程 ID 98)与另一个进程被死锁在 锁 | 通信缓冲区 资源上，并且已被选作死锁牺牲品。请重新运行该事务。  或是执行超时已过期。完成操作之前已超时或服务器未响应。 

我用以下SQL查询DB session都没看到有卡的 sql

SELECT      r.scheduler_id ,            status         ,            r.session_id   as SPID,            r.blocking_session_id as BlockBy,            substring(                ltrim(q.text),                r.statement_start_offset/2+1,                (CASE                 WHEN r.statement_end_offset = -1                 THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2                 ELSE r.statement_end_offset                 END - r.statement_start_offset)/2)                 AS [正在執行SQL命令],            r.cpu_time      as [CPU Time(ms)],            r.start_time    as [Start Time],            r.total_elapsed_time as [Execute Total Time],            r.reads              as [Reads],            r.writes             as [Writes],            r.logical_reads      as [Logical Reads],            -- q.text, /* 完整的 T-SQL 指令碼 */            d.name               as [DataBase]FROM        sys.dm_exec_requests r             CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS q            LEFT JOIN sys.databases d ON (r.database_id=d.database_id)WHERE       r.session_id > 50 AND r.session_id <> @@SPIDORDER BY    r.total_elapsed_time desc

是否能追出当时发生原因，跟能调查出是谁下哪个指令，被谁下的指令卡住

Answer 1

在 SQL Server 中，当发生死锁或超时时，要追踪造成问题的 SQL 语句和参数，你可以采取以下步骤：

1. 启用死锁图和超时跟踪:
   首先，你需要启用死锁图和超时跟踪。这可以通过以下命令完成：

DBCC TRACEON (1222, -1)

这会在 SQL Server 日志中生成死锁图和超时跟踪的详细信息。

2. 查看 SQL Server 日志:
   打开 SQL Server Management Studio (SSMS)，连接到目标数据库，然后查看 SQL Server 日志。在日志中，你应该能够找到与死锁或超时相关的详细信息。
3. 分析死锁图:
   死锁图将显示两个或多个进程之间的锁定冲突。你可以分析这些图来确定哪些 SQL 语句和参数导致了死锁。
4. 查看执行计划:
   使用 EXPLAIN 语句查看导致问题的 SQL 语句的执行计划。这可以帮助你了解查询的执行方式，并可能揭示潜在的锁定问题。
5. 检查会话信息:
   使用 sys.dm_exec_sessions 和 sys.dm_exec_requests 动态管理视图，你可以获取有关当前会话和请求的详细信息。通过分析这些信息，你可以找到可能导致问题的会话和 SQL 语句。
6. 查看事务日志:
   事务日志是 SQL Server 用于记录所有更改的持久化日志。你可以使用第三方工具（如 PerfView 或 SQLdiag）或使用 T-SQL 查询来查看和分析事务日志，以找到与问题相关的信息。
7. 审查应用程序代码:
   如果可能的话，审查导致问题的应用程序代码。了解是哪个应用程序或用户执行了导致问题的 SQL 语句，并进一步调查他们是否使用了特定的参数或配置。

通过以上步骤，你应该能够追踪并确定导致 SQL Server 显示锁或超时的 SQL 语句和参数。请注意，解决死锁和超时问题可能需要进一步的调查和调整数据库设计、查询优化或应用程序逻辑。