T-SQL – Deleting Small Number of Rows Hangs in Azure SQL Database

azure-sql-databaset-sql

So I'm just trying to delete some bad data from a one of our test databases. I've identified the rows to be deleted, and have written a simple query to handle this.

However, when run, the query hangs on 4% complete, and just sits there for hours. Our databases are hosted on Azure – For this test database we're running a Standard SO with 10 DTUs.

What am I missing here?

Best Answer

Might be that cascading foreign key that table [Shift Offer] has pointing to table [Availability], slows down your delete operation

Try following:

1) create nonclustered index on column [Shift Offer][availability_id_fk] and try running your query again; see if nonclustered index speeds things up

2) if still running too long, try disable the foreign key at all (run sql command)

alter table [Shift Offer] nocheck constraint [paste your constraint name     
here I guess its availability_id_fk]

run delete command, and if succeeds and works fast, you will have to manually delete records from [Shift Offer] table, if it had any rows with availability_id_fk values that were deleted from [Availability] table, and then enable foreign key back

Related Solutions

Sql-server – Setup Managed Backup failure

Issue identified. The network admin didn't have the separate Azure domain environments trusted, although he said that he did. I tried this and failed in our SQL DEV environment/domain in Azure, which isn't trusted. I then, for sake of troubleshooting and communicating to the powers to be, tried it from our SQL QA environment/domain in Azure and it worked flawlessly! The network admin compared the two environments and found the issue. Network issue after all.

Sql-server – make this multiple join query faster

You need to get some numbers that make sense and build from there

select csi.ItemIDNumber, sum(f.TLSale)
from dbo.ReportSales F with (nolock) 
left outer join dbo.DDate dd with (nolock) 
      on f.DDateID = dd.DDateID 
left outer join dbo.DCSaleItem csi with (nolock) 
      on f.DCSaleID = csi.DCSaleID 
group by csi.ItemIDNumber

the join on DDate is doing nothing and the group by csi.ItemIDNumber makes that left into a regular join

Best Answer

Related Solutions

Sql-server – Setup Managed Backup failure

Sql-server – make this multiple join query faster

Related Question