SQL insert into suspended - 读取未提交或拆分查询？

I have a stored procedure that contains two insert into queries. While running the sp I often see these two as being suspended. The sp has been tested on a test server with almost no traffic but now as we moved it on to the production server these suspended states have appeared with 1-2 deadlocks.

我有一个存储过程包含两个插入查询。在运行sp时,我经常看到这两个被暂停。 sp已经在几乎没有流量的测试服务器上进行了测试,但现在当我们将它移动到生产服务器时,这些暂停状态出现了1-2个死锁。

I assume that SQL Server creates table locks while running these queries but I don't know what is the preferred way to solve this? The insert into queries are moving 30000 records in one iteration into an other database. These are archive data, so queries coming from the normal production processes are nothing to do with the data being archived, they are 2-3 years old.

我假设SQL Server在运行这些查询时创建表锁,但我不知道解决此问题的首选方法是什么?插入查询会在一次迭代中将30000条记录移动到另一个数据库中。这些是归档数据,因此来自正常生产过程的查询与归档的数据无关,它们已有2 - 3年的历史。

• Can I add WITH NOLOCK to the selects to avoid suspended states and deadlocks?

我可以在选择中添加WITH NOLOCK以避免挂起状态和死锁吗?

• Or should I set ISOLATION LEVEL to READ UNCOMMITTED? (these records are old, they won't change)

或者我应该将ISOLATION LEVEL设置为READ UNCOMMITTED? (这些记录很旧,不会改变)

• What other options do I have? Cursors to run through the ids it has to archive one by one? (I tried not to use cursors until now.)

我还有其他选择吗?游标必须逐个存档才能运行? (我试着直到现在才使用游标。)

These are the two queries. @workitemids and @workstepids are table variables containing one int field.

这是两个查询。 @workitemids和@workstepids是包含一个int字段的表变量。

insert into archive_****.archive.workitems
            select * from ****.dbo.WorkItems where ****.dbo.workitems.Id in (select Id from @workitemIds);

insert into archive_****.archive.worksteps([Id], [Timestamp], [Description], [WorkPlace_Id], [WorkItemState_Id], [UserId], [WorkItem_Id], [Technology_Id], [Failcodes_Id], [DrawingNo], [ManualData], [Deleted], [WorkItemState_Arrival_Id], Workstepdatas)
            select [Id], [Timestamp], [Description], [WorkPlace_Id], [WorkItemState_Id], [UserId], [WorkItem_Id], [Technology_Id], [Failcodes_Id], [DrawingNo], [ManualData], [Deleted], [WorkItemState_Arrival_Id],
                (select Fieldname Field, Value [Value], Unit [Unit] from ****.dbo.workstepdatas wsd
                    left join ****.dbo.technologydatafields tdf on tdf.Id = wsd.TechnologyDatafields_Id
                    where tdf.fieldname is not null and wsd.WorkStep_Id = ws.Id
                        and value NOT LIKE '%[' + CHAR(0)+ '-' +CHAR(31)+']%'  COLLATE Latin1_General_100_BIN2
                    for xml auto,type)
            from ****.dbo.worksteps ws
            where ws.Id in (select Id from @workstepIds);

1 个解决方案

#1

Please attempt to write the nested query into a cte as below and advise if any progress. You will need to change the database names.

请尝试将嵌套查询写入cte,如下所示,并告知是否有任何进展。您需要更改数据库名称。

insert into archive_db.archive.workitems (with tablock)
select *
from db.dbo.WorkItems as w
inner join @workitemIds as wi
    on w.Id = wi.id;

with xmlcte
    (ID, xmlRow)
as (
       select ws.id
            , (
                  select Fieldname as Field                           
                       , [Value]
                       , Unit
                  from db.dbo.workstepdatas wsd
                  left join db.dbo.technologydatafields tdf
                      on tdf.Id = wsd.TechnologyDatafields_Id
                  where
                      tdf.fieldname is not null
                      and wsd.WorkStep_Id = ws.Id
                      and [value] not like '%[' + char(0) + '-' + char(31) + ']%' collate Latin1_General_100_BIN2
                  for xml auto, type
              ) as xmlRow
       from db.dbo.worksteps as ws
   )
insert into archive_db.archive.worksteps (with tablock)
     (
         [Id]
       , [Timestamp]
       , [Description]
       , [WorkPlace_Id]
       , [WorkItemState_Id]
       , [UserId]
       , [WorkItem_Id]
       , [Technology_Id]
       , [Failcodes_Id]
       , [DrawingNo]
       , [ManualData]
       , [Deleted]
       , [WorkItemState_Arrival_Id]
       , Workstepdatas
     )
select ws.[Id]
     , [Timestamp]
     , [Description]
     , [WorkPlace_Id]
     , [WorkItemState_Id]
     , [UserId]
     , [WorkItem_Id]
     , [Technology_Id]
     , [Failcodes_Id]
     , [DrawingNo]
     , [ManualData]
     , [Deleted]
     , [WorkItemState_Arrival_Id]
     , [xmlRow]
from db.dbo.worksteps ws
inner join @workstepIds as wsi
    on ws.Id = wsi.id
inner join xmlcte -- I assume inner join is OK
    on ws.id = xmlcte.id;