Azure SQL

Question

Azure SQL

PJ_SQL

SSCertifiable

Points: 6703
More actions
February 15, 2023 at 7:56 pm

#4150231

Hello!
Trying to delete about 1000 records from a table in Azure, not sure why it has been running for 2 hours and not giving any errors or completed?

Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

Almost impossible to tell you exactly what is wrong without more data. It could be as simple as you didn't commit your transaction. It could be blocking. Assuming you have no monitoring in place, I'd run some queries against sys.dm_exec_requests to see what is blocking your process (cause that's probably it, but hard to know without more data). Here's more information on the topic. It's also possible that it's an enormous data set, but your WHERE clause isn't using any indexes, so it's scanning the table to find everything to delete. I'd start with blocking though.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 2

Thank you, I think the other factor is that there are more than 100 tables that have dependency on this table, so the delete might be looking into all those other dependent tables before trying to delete on parent table.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Oh yeah, take a look at the execution plan. You can just get an estimated plan, you don't have to execute the query. That will show you all the activity that the engine is doing to satisfy the query. It could very well be all those dependent tables. That's what I mean when I said I didn't have enough information to tell you for sure why the query was running slow. Still, probably blocking. But now you have even more areas where blocking could be playing a factor.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

PJ_SQL wrote:

Thank you, I think the other factor is that there are more than 100 tables that have dependency on this table, so the delete might be looking into all those other dependent tables before trying to delete on parent table.

If there are FKs involved, then most definitely.

I'm curious... why so many individual tables?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

durai nagarajan SSCoach Points: 15778 More actions · Answer 5

If a FK or a trigger is involved i hope , there should be some kindly of error thrown?.

Regards
Durai Nagarajan

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

durai nagarajan wrote:

If a FK or a trigger is involved i hope , there should be some kindly of error thrown?.

Not if it's just processing all the validations, scanning tables looking for matches, that sort of thing. No error, just lots of CPU, disk & memory, slowing it all down.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ScottPletcher SSC Guru Points: 101097 More actions · Answer 7

You would also want to check what the FK DELETE options are on each table. For the ones that are CASCADE, that could cause updates in other tables as well (which, theoretically, could also flow to yet other tables).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".