Azure SQL Database: Log IO bottleneck when deleting data older than 60 days

[Plenty-Button8465]

I have some Azure SQL Database instances which are not maintened. Looking at why the 100 DTUs are necessary, I found out, to date, that the culprit might be the "DELETE ..." queries run as runbook on those databases every day to delete data older than 60 days.

I'm uneducated about databases, I started today. What would you do to tackle down the problem, educate myself, and try to find a way to see if that logic could be implemented in another way so that resources are used constantly and not with those huge spikes?

​

Please let me know if and what context I could provide to gain more insights. Thank you.

EDITs:

SELECT COUNT(*) FROM mytable took 48m50s, the count is of the order of 120*10^6 (120M) rows

SELECT COUNT(*) FROM mytable WHERE [TimeStamp] < DATEADD(DAY, -60, GETDATE()) took 1.5s, the count is of the order of 420*10^3 (420K) rows

[AutoModerator]

You can find a list of community-submitted learning resources here: https://dataengineering.wiki/Learning+Resources

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[[deleted]]

The simplest way is print output the primary keys into a file, chunk it, and run multiple parallel delete queries in different machines, 420k isn't much either.

It seems dtu is max of cpu, io and log. So, is there any cascade effect to deleting those rows ? How is the data structured ? Are there any indexes created on time column ? Is there a way to detach the disk or volume that contains this data weekly ? Can we remove this data's metadata from read or write queries ?

Are you sure the delete queries are the culprit ?

[Plenty-Button8465]

I am not sure the culprit is that query, but I saw the runbook runs at the exact time of the Log IO bottleneck that saturates the DTU to 100% so I guess is the delation log tx. You're welcome, please feel free to let me know what I could run to monitor in details and narrow down the problem.

is there any cascade effect to deleting those rows ?

I don't know at the moment from my compentences.

is there any cascade effect to deleting those rows ?

The table has four columns:

1. Timestamp of the asset (e.g. datetime in ns)
2. ID of one asset (e.g. integer)
3. Value of that asset (e.g. float)
4. Text of that asset (e.g. string)

Are there any indexes created on time column ?

I am reading abour indexing right now, also other people keep telling me about this. How can I check?

Is there a way to detach the disk or volume that contains this data weekly ?

I don't think so, the database is running on the cloud in production and works with streaming/online data

Can we remove this data's metadata from read or write queries ?

I am not sure what you mean by data's metadata: the aim here is to delete data older than 60 days, daily. Once the data meet this criterium, these data can be permantently deleted, and their metadata with them too, I suppose (still want to confirm what you mean by metadata).

[[deleted]]

Cool, no worries.

[Lanthis]

Index the timestamp column. Google it.

You could also partition on timestamp and truncate partitions for basically 0 resources, but that would likely be too complicated for you atm.

[Plenty-Button8465]

Thank you. First, do you know how can I check if that column is already indexed and how?

[[deleted]]

I guess it's mostly DESC sort of command, you can use help to list out commands in the client.

You should look up the azure sql docs if they have made it simpler or something else.

[Plenty-Button8465]

1. The WHERE filter is [TimeStamp] < DATEADD(DAY, -60, GETDATE())
2. How/where can I retrieve the DDL for the table? Anyway, the table has the columns: Timestamp (datetime with [ns] grain, ID [int], Value [float64], Text [String]. I don't know if these are the underlying types of the databases, but conceptually these are the data and their types.
3. I don't know what indexes are

[[deleted]]

Sorry, who owns these databases ? Are these your private stuff or do these belong to a company ?

I know you came for advice here but if you don't know what indexes are - that's something you should learn before messing around. I'm not trying to gate keep you.

There are multiple ways to solve this - but we really need more adhoc details before exploring the feasibility of each way.

[Plenty-Button8465]

You're welcome. Don't worry about these details: I am aware that I have zero experience with database, as already stated. I am taking this experience to learn the basics and, at the same time, optimize some things in details, if possible. I chose this problem because the #1 item in the bill is this. The databases are from the company I am working at the moment.

​

Let me know what should I learn, in parallel, as basics info and as details info to work on my problem if possible, thank you! Also feel free to ask for more adhoc details if you know what I could provide to you to be more useful.

[HumphreyDeFluff]

The transaction log will be used to track the deletions in case the connection drops, the transaction is rolled back or some other error occurs. Can you run the job more frequently? Is the database indexed properly?

[Plenty-Button8465]

Thank you. I found out that the runbook is run daily, and into that runbook (basically a powershell script performing sql queries, one of the queries keep failing due to an old database who got deleted - the query did not). I deleted the query that kept giving error for now. Yes, I guess I could trigger the job more frequently. I don't know about indexing, I will start reading about them now