/* 查看会话信息 */ SELECT t1.session_id , t1.request_id , t1.task_alloc , t1.task_dealloc , t2.sql_handle , t2.statement_start_offset , t2.statement_end_offset , t2.plan_handle FROM ( SELECT session_id , request_id , SUM(internal_objects_alloc_page_count) AS task_alloc , SUM(internal_objects_dealloc_page_count) AS task_dealloc FROM sys.dm_db_task_space_usage GROUP BY session_id , request_id ) AS t1 , sys.dm_exec_requests AS t2 WHERE t1.session_id = t2.session_id AND ( t1.request_id = t2.request_id ) AND t1.session_id=97 ORDER BY t1.task_alloc DESC/* 查看具体语句 */ select text from sys.dm_exec_sql_text(@sql_handle) select * from sys.dm_exec_query_plan(@plan_handle)
1.执行 select * from sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL 结果为空 2.执行 select * from sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL 结果为空
再输入:kill 65 看看现在开始动了没
SPID 65: 正在进行事务回滚。估计回滚已完成: 0%。估计剩余时间: 0 秒。
1222锁超时,SELECT t1.session_id , t1.request_id , t1.task_alloc , t1.task_dealloc , t2.sql_handle , t2.statement_start_offset , t2.statement_end_offset , t2.plan_handle FROM ( SELECT session_id , request_id , SUM(internal_objects_alloc_page_count) AS task_alloc , SUM(internal_objects_dealloc_page_count) AS task_dealloc FROM sys.dm_db_task_space_usage GROUP BY session_id , request_id ) AS t1 , sys.dm_exec_requests AS t2 WHERE t1.session_id = t2.session_id AND ( t1.request_id = t2.request_id ) AND t1.session_id=65 ORDER BY t1.task_alloc DESC给我看看结果
查一下
0x00A6AC58 97 4 2639734 CXPACKET 0x04034F00 0x00A6A988 97 0 exchangeEvent id=port4032380 nodeId=0
0x00AF8F28 97 3 28765 CXPACKET 0x04034C58 0x00A6A988 97 0 exchangeEvent id=port4032380 nodeId=0
0x00AFED48 97 2 262422 CXPACKET 0x04034CE0 0x00A6A988 97 0 exchangeEvent id=port4032380 nodeId=0
0x00DA4C58 97 1 2639687 CXPACKET 0x040345F8 0x00A6A988 97 0 exchangeEvent id=port4032380 nodeId=0
继续
这列太长了,我就这么发了,你查查看吧,列的顺序是对的
97 2014-05-07 10:30:42.000 USER-QQB2FR6EOH .Net SqlClient Data Provider 6736 5 .Net SqlClient Data Provider 0x44D63946E6E8BF4CA9A7A496DB0687F5 new56539 running 0x 0 0 0 0 4 2014-05-07 10:30:42.023 2014-05-07 10:30:42.000 0 0 0 1 2147483647 简体中文 ymd 7 1 0 1 0 1 1 1 1 2 -1 0 0 0 0x44D63946E6E8BF4CA9A7A496DB0687F5 new56539 NULL NULL NULL
查看会话信息
*/
SELECT t1.session_id ,
t1.request_id ,
t1.task_alloc ,
t1.task_dealloc ,
t2.sql_handle ,
t2.statement_start_offset ,
t2.statement_end_offset ,
t2.plan_handle
FROM ( SELECT session_id ,
request_id ,
SUM(internal_objects_alloc_page_count) AS task_alloc ,
SUM(internal_objects_dealloc_page_count) AS task_dealloc
FROM sys.dm_db_task_space_usage
GROUP BY session_id ,
request_id
) AS t1 ,
sys.dm_exec_requests AS t2
WHERE t1.session_id = t2.session_id
AND ( t1.request_id = t2.request_id )
AND t1.session_id=97
ORDER BY t1.task_alloc DESC/*
查看具体语句
*/
select text from sys.dm_exec_sql_text(@sql_handle)
select * from sys.dm_exec_query_plan(@plan_handle)
这两句都能执行出来信息,而且有一句还有sql语句。接下来版主。我都晕了
你是在给我查找原因呢,还是原因已经找到了
这个sql语句是另一个同事跑的一个对数据库某张表的某个字段加密的语句。我已经让他停了,现在查不到数据了那俩handle也都为空了。上边的事务回滚依然存在
1.执行
select * from sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL 结果为空
2.执行
select * from sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL 结果为空
看看现在开始动了没
t1.request_id ,
t1.task_alloc ,
t1.task_dealloc ,
t2.sql_handle ,
t2.statement_start_offset ,
t2.statement_end_offset ,
t2.plan_handle
FROM ( SELECT session_id ,
request_id ,
SUM(internal_objects_alloc_page_count) AS task_alloc ,
SUM(internal_objects_dealloc_page_count) AS task_dealloc
FROM sys.dm_db_task_space_usage
GROUP BY session_id ,
request_id
) AS t1 ,
sys.dm_exec_requests AS t2
WHERE t1.session_id = t2.session_id
AND ( t1.request_id = t2.request_id )
AND t1.session_id=65
ORDER BY t1.task_alloc DESC给我看看结果
select text from sys.dm_exec_sql_text(0x02000000FFE7E4104F512F2C581E215A0E83449CF09D6016)
结果:
insert bulk [new56539].[dbo].[Automatic]([A_IdId] nvarchar(50) collate Chinese_PRC_CI_AS,[A_Danhao] nvarchar(50) collate Chinese_PRC_CI_AS,[A_Good] nvarchar(20) collate Chinese_PRC_CI_AS,[A_Bianhao] nvarchar(30) collate Chinese_PRC_CI_AS,[A_Jianshu] int,[A_Hk] numeric(18,0),[A_Dfhk] numeric(18,0),[A_Yf] numeric(18,0),[A_Yfyf] numeric(18,0),[A_Tfyf] numeric(18,0),[A_Dfyf] numeric(18,0),[A_Hfyf] numeric(18,0),[A_Yjyf] numeric(18,0),[A_Shk] numeric(18,0),[A_Sf] numeric(18,0),[A_Sxf] numeric(18,0),[A_Shdate] datetime,[A_Fcdate] datetime,[A_Jcdate] datetime,[A_Skdate] datetime,[A_Hkdate] datetime,[A_Jkdate] datetime,[A_Fkdate] datetime,[A_Shr] nvarchar(50) collate Chinese_PRC_CI_AS,[A_Shtel] nvarchar(50) collate Chinese_PRC_CI_AS,[A_Fhr] nvarchar(80) collate Chinese_PRC_CI_AS,[A_Fhtel] nvarchar(30) collate Chinese_PRC_CI_AS,[A_Qd] nvarchar(20) collate Chinese_PRC_CI_AS,[A_Shd] nvarchar(20) collate Chinese_PRC_CI_AS,[A_Zd] nvarchar(20) collate Chinese_PRC_CI_AS,[A_Fhd] nvarchar(20) collate Chinese_PRC_CI_AS,[A_Worker] int,[A_Fkdanhao] nvarchar(50) collate Chinese_PRC_CI_AS,[A_Gh] nvarchar(50) collate Chinese_PRC_CI_AS,[A_Accout] nvarchar(30) collate Chinese_PRC_CI_AS,[A_Unit] nvarchar(50) collate Chinese_PRC_CI_AS,[A_Pj] int,[A_State] int,[A_Dzzt] int,[A_ModifyDate] datetime,[A_CreateDate] datetime)with(TABLOCK,CHECK_CONSTRAINTS)这个就是插入语句下边那条语句是个XML
SPID (服务器进程 ID): 65
UID (用户 ID): -1
名称 : BULK INSERT
LSN : (20992:430226:190)
开始时间 : 05 7 2014 6:24:52:030AM
SID : 0x0105000000000005150000009abf42e36cb95a12f85084def9030000
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。
sys.开头的表有没有说明,我想学习下。还有我用sql导入导出的时候。再次导入这4000W到分区表里,又卡在最后几百万了我sys.dm_exec_requests 里面查询,发现我的这个批量动作一会被暂停,一会有running,但是界面上的数字一直不变
现在持续一小时了,按照原来的速度剩下的数据用不了5分钟就搞定。。
我现在应该怎么办呢