代码之家 › 专栏 › 技术社区 › VA systems engineer

SQL Server Management Studio活动监视器:“上一个Transact-SQL命令批处理”顶部的意外“SQL”是什么

ssms sql-server c#

VA systems engineer · 技术社区 · 6 年前

我的C#程序提交多个 SELECT 查询远程服务器上的SQL Server实例。我正在尝试使用远程服务器上的SQL Server Mgmt Studio活动监视器跟踪查询问题。屏幕截图中的文本取自 Last Transact-SQL command batch 在活动监视器中右键单击表示失败SQL查询的进程时出现的对话框(该查询导致服务器停止响应)。

我的问题是:窗口顶部的文本 (@SearchString nvarchar(8)) 是意外的。它是从哪里来的? @SearchString 是程序中用于SQL参数的C#变量的名称,但 选择 我编写并从程序发送的语句不明确包括 (@SearchString nvarchar(8)) 如活动监视器中所示。我的SQL Server实例是否出于某种原因将其插入远程服务器端?

p、我认为尝试将VARBINARY contentData列转换为NVARCHAR可能是导致我的查询挂起的原因

1 回复 | 直到 6 年前

Dan Wilson 6 年前

进程正在运行参数化查询。使用活动监视器查看流程查询时,所有参数都是使用参数化查询语法声明的,即 (@SearchString nvarchar(8)) 。

这对于1)查看查询时的信息和2)是否希望在应用程序外部运行查询都是有益的。

可以使用 sys.dm_exec_sql_text() 表值函数并传递连接的SQL句柄。

下面是一个近似于活动监视器工具中找到的数据的查询。资料来源: Google web cache

SELECT 
   SessionId    = s.session_id, 
   UserProcess  = CONVERT(CHAR(1), s.is_user_process),
   LoginInfo    = s.login_name,   
   DbInstance   = ISNULL(db_name(r.database_id), N''), 
   TaskState    = ISNULL(t.task_state, N''), 
   Command      = ISNULL(r.command, N''), 
   App            = ISNULL(s.program_name, N''), 
   WaitTime_ms  = ISNULL(w.wait_duration_ms, 0),
   WaitType     = ISNULL(w.wait_type, N''),
   WaitResource = ISNULL(w.resource_description, N''), 
   BlockBy        = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
   HeadBlocker  = 
        CASE 
            -- session has active request; is blocked; blocking others
            WHEN r2.session_id IS NOT NULL AND r.blocking_session_id = 0 THEN '1' 
            -- session idle; has an open tran; blocking others
            WHEN r.session_id IS NULL THEN '1' 
            ELSE ''
        END, 
   TotalCPU_ms        = s.cpu_time, 
   TotalPhyIO_mb    = (s.reads + s.writes) * 8 / 1024, 
   MemUsage_kb        = s.memory_usage * 8192 / 1024, 
   OpenTrans        = ISNULL(r.open_transaction_count,0), 
   LoginTime        = s.login_time, 
   LastReqStartTime = s.last_request_start_time,
   HostName            = ISNULL(s.host_name, N''),
   NetworkAddr        = ISNULL(c.client_net_address, N''), 
   ExecContext        = ISNULL(t.exec_context_id, 0),
   ReqId            = ISNULL(r.request_id, 0),
   WorkLoadGrp        = N'',
   LastCommandBatch = (select text from sys.dm_exec_sql_text(c.most_recent_sql_handle)) 
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN 
(
    -- Using row_number to select longest wait for each thread, 
    -- should be representative of other wait relationships if thread has multiple involvements. 
    SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
    FROM sys.dm_os_waiting_tasks 
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (r.session_id = r2.blocking_session_id)
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) as st

WHERE s.session_Id > 50                         -- ignore anything pertaining to the system spids.

AND s.session_Id NOT IN (@@SPID)     -- let's avoid our own query! :)