July 14, 2016 at 9:35 am
All, I have a query I am running to delete some records from the table but not able to. I don't see any blocking, enough space on the disk and the table itself has only 100 K records. I am deleting 6000 records from the table. Can I get some help on what I am doing wrong or why is it taking forever to delete those recordsDELETE FROM ClientServiceSite
WHERE ServiceId IN (61,59,58,10)
I even tried DELETE TOP (200) FROM ClientServiceSite
WHERE ServiceId IN (61,59,58,10)
But no luck so far.
July 14, 2016 at 9:45 am
Run this while the delete is taking place:SELECT wait_duration_ms, wait_type
FROM sys.dm_os_waiting tasks
WHERE session_id = <SPID-of-connection-doing-the-delete>
What do you get?
John
July 14, 2016 at 9:49 am
John Mitchell-245523 (7/14/2016)
Run this while the delete is taking place:SELECT wait_duration_ms, wait_typeFROM sys.dm_os_waiting tasks
WHERE session_id = <SPID-of-connection-doing-the-delete>
What do you get?
John
I get this Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.dm_os_waiting'.
July 14, 2016 at 9:51 am
Deliberate error. I'll let you work it out!
John
July 14, 2016 at 10:38 am
sys.dm_os_waiting_tasks is defined at https://msdn.microsoft.com/en-us/library/ms188743.aspx
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2016 at 10:40 am
1) What is the estimated query plan for the delete? If you are doing a table scan of a billion row table to delete 6000 rows it could take a VERY long time on most systems.
2) use sp_whoisactive to see what is really going on (including the query plan). Awesome free script!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 14, 2016 at 11:01 am
When I run the select * from the table, it returns 100K records in 6 minutes. I deleted top 10 records and it took almost 50 seconds (5 seconds per record).
July 14, 2016 at 11:48 am
Why didn't you provide the query plan?!? I will ask again that you get the query plan for the DELETE and provide it here.
Now that we have additional information that DELETE TOP 10 taking 5 seconds per record on averate I am betting on 1) triggers or 2) (unindexed) FKs or 3) both
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 14, 2016 at 12:25 pm
Chitown (7/14/2016)
When I run the select * from the table, it returns 100K records in 6 minutes. I deleted top 10 records and it took almost 50 seconds (5 seconds per record).
A trigger would potentially explain slow inserts, updates, and/or deletes. This query will quickly return detail about any triggers in your database:
select o.name, c.text
from sys.objects as o
join sys.syscomments as c on o.object_id = c.id
where o.type = 'TR';
However, you also mention above that simply SELECT * from this table takes 6 minutes to return the 100k records, and this table only has 100k records total? I'd expect low end performance like that on a cheap Android device, not a production database server or even a laptop. There is something going on with your server like: severe blocking, high CPU utilization, or and issue with the storage system.
Setup a blocked process trace.
http://www.sqlservercentral.com/blogs/aschenbrenner/2011/12/01/the-blocked-process-report/
Also, run the following and see what is your average wait in ms for page io latches.
SELECT *
, wait_time_ms / waiting_tasks_count AS 'Avg Wait in ms'
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count > 0 AND wait_type like 'PAGEIOLATCH%'
ORDER BY wait_time_ms DESC;
http://www.sqltuners.net/blog/13-05-16/Measuring_Disk_IO_performance_for_SQL_Servers.aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 14, 2016 at 12:28 pm
null
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply