SQL의 삭제 문이 매우 느립니다.

SQL의 삭제 문이 매우 느립니다.

---

시간이 초과되는 다음과 같은 진술이 있습니다.

DELETE FROM [table] WHERE [COL] IN ( '1', '2', '6', '12', '24', '7', '3', '5')

다음과 같이 한 번에 하나씩 시도했습니다.

DELETE FROM [table] WHERE [COL] IN ( '1' )

지금까지 22 분이고 아직 진행 중입니다.

테이블에는 260,000 개의 행이 있고 4 개의 열이 있습니다.

누구든지 이것이 왜 그렇게 느릴 것인지 그리고 속도를 높이는 방법에 대한 아이디어가 있습니까? 내가 WHERE를 수행하고있는 [COL]에 고유하지 않은 클러스터되지 않은 인덱스가 있습니다. SQL Server 2008 R2를 사용하고 있습니다.

업데이트 : 테이블에 트리거가 없습니다.

---

삭제 속도가 느려지는 원인 :

• 많은 기록 삭제
• 많은 인덱스
• 하위 테이블의 외래 키에 대한 인덱스가 누락되었습니다. (댓글에서 이것을 언급 한 @CesarAlvaradoDiaz에게 감사드립니다)
• 교착 상태 및 차단
• 트리거
• 계단식 삭제 (삭제하려는 상위 레코드 10 개는 수백만 개의 하위 레코드가 삭제됨을 의미 할 수 있음)
• 증가해야하는 트랜잭션 로그
• 확인할 많은 외래 키

따라서 귀하의 선택은 차단되는 항목을 찾아서 수정하거나 정상적인 프로덕션로드를 방해하지 않는 시간에 삭제를 실행하는 것입니다. 일괄 적으로 삭제를 실행할 수 있습니다 (트리거, 계단식 삭제 또는 많은 수의 레코드가있는 경우 유용함). 인덱스를 삭제하고 다시 만들 수 있습니다 (오프 타임에도 수행 할 수있는 경우 가장 좋음).

---

1. CONSTRAINT 비활성화

   ALTER TABLE [TableName] NOCHECK CONSTRAINT ALL;

2. 인덱스 비활성화

   ALTER INDEX ALL ON [TableName] DISABLE;

3. 인덱스 다시 작성

   ALTER INDEX ALL ON [TableName] REBUILD;

4. CONSTRAINT 활성화

   ALTER TABLE [TableName] CHECK CONSTRAINT ALL;

5. 다시 삭제

---

많은 행을 삭제하면 매우 느릴 수 있습니다. 다음과 같이 한 번에 몇 개를 삭제하십시오.

delete top (10) YourTable where col in ('1','2','3','4')
while @@rowcount > 0
    begin
    delete top (10) YourTable where col in ('1','2','3','4')
    end

---

예방 조치

SQL Profiler이 문제의 근본 원인은 의 도움으로 확인하십시오 . Triggers실행이 지연 될 수 있습니다 . 그것은 무엇이든 될 수 있습니다. 을 선택하십시오 잊지 마세요 Database Name및 Object Name시작하면서는 Trace불필요한 쿼리를 스캔 제외 할 ...

데이터베이스 이름 필터링

테이블 / 스토어드 프로 시저 / 트리거 이름 필터링

시정 조치

테이블에 260,000 개의 레코드 IN Predicate가 있으며 6 개의 값이 포함되어 있습니다. 이제 각 레코드는의 각 값에 대해 260,000 번 검색됩니다 IN Predicate. 대신 아래와 같이 내부 조인이어야합니다.

Delete K From YourTable1 K
Inner Join YourTable2 T on T.id = K.id

또는에 IN Predicate값을 삽입하십시오.Temporary TableLocal Variable

---

삭제하려는 테이블에 BEFORE / AFTER DELETE 트리거가있는 경우 거기에있는 항목이 지연을 유발할 수 있습니다.

또한 해당 테이블을 참조하는 외래 키가있는 경우 추가 UPDATE 또는 DELETE가 발생할 수 있습니다.

---

다른 테이블에 [테이블]에 대한 FK 제약이있을 수 있습니다. 따라서 DB는 참조 무결성을 유지하기 위해 이러한 테이블을 확인해야합니다. 이 FK에 해당하는 필요한 모든 인덱스가 있더라도 그 양을 확인하십시오.

I had the situation when NHibernate incorrectly created duplicated FKs on the same columns, but with different names (which is allowed by SQL Server). It has drastically slowed down running of the DELETE statement.

---

In my case the database statistics had become corrupt. The statement

delete from tablename where col1 = 'v1' 

was taking 30 seconds even though there were no matching records but

delete from tablename where col1 = 'rubbish'

ran instantly

running

update statistics tablename

fixed the issue

---

Is [COL] really a character field that's holding numbers, or can you get rid of the single-quotes around the values? @Alex is right that IN is slower than =, so if you can do this, you'll be better off:

DELETE FROM [table] WHERE [COL] = '1'

But better still is using numbers rather than strings to find the rows (sql likes numbers):

 DELETE FROM [table] WHERE [COL] = 1

Maybe try:

 DELETE FROM [table] WHERE CAST([COL] AS INT) = 1

In either event, make sure you have an index on column [COL] to speed up the table scan.

---

Check execution plan of this delete statement. Have a look if index seek is used. Also what is data type of col?

If you are using wrong data type, change update statement (like from '1' to 1 or N'1').

If index scan is used consider using some query hint..

---

I read this article it was really helpful for troubleshooting any kind of inconveniences

https://support.microsoft.com/en-us/kb/224453

this is a case of waitresource KEY: 16:72057595075231744 (ab74b4daaf17)

-- First SQL Provider to find the SPID (Session ID)

-- Second Identify problem, check Status, Open_tran, Lastwaittype, waittype, and waittime
-- iMPORTANT Waitresource select * from sys.sysprocesses where spid = 57

select * from sys.databases where database_id=16

-- with Waitresource check this to obtain object id 
select * from sys.partitions where hobt_id=72057595075231744

select * from sys.objects where object_id=2105058535

---

If you're deleting all the records in the table rather than a select few it may be much faster to just drop and recreate the table.

---

After inspecting an SSIS Package(due to a SQL Server executing commands really slow), that was set up in a client of ours about 5-4 years before the time of me writing this, I found out that there were the below tasks: 1) insert data from an XML file into a table called [Importbarcdes].

2) merge command on an another target table, using as source the above mentioned table.

3) "delete from [Importbarcodes]", to clear the table of the row that was inserted after the XML file was read by the task of the SSIS Package.

After a quick inspection all statements(SELECT, UPDATE, DELETE etc.) on the table ImportBarcodes that had only 1 row, took about 2 minutes to execute.

Extended Events showed a whole lot PAGEIOLATCH_EX wait notifications.

No indexes were present of the table and no triggers were registered.

Upon close inspection of the properties of the table, in the Storage Tab and under general section, the Data Space field showed more than 6 GIGABYTES of space allocated in pages.

What happened:

The query ran for a good portion of time each day for the last 4 years, inserting and deleting data in the table, leaving unused pagefiles behind with out freeing them up.

So, that was the main reason of the wait events that were captured by the Extended Events Session and the slowly executed commands upon the table.

Running ALTER TABLE ImportBarcodes REBUILD fixed the issue freeing up all the unused space. TRUNCATE TABLE ImportBarcodes did a similar thing, with the only difference of deleting all pagefiles and data.

---

open CMD and run this commands

NET STOP MSSQLSERVER
NET START MSSQLSERVER

this will restart the SQL Server instance. try to run again after your delete command

I have this command in a batch script and run it from time to time if I'm encountering problems like this. A normal PC restart will not be the same so restarting the instance is the most effective way if you are encountering some issues with your sql server.

참고URL : https://stackoverflow.com/questions/10901299/delete-statement-in-sql-is-very-slow