SQL Server performance issues

[Baxteen]

or just truncate.

Truncate is jsut a detele plus a receed of the auto incremeting ID's.
if the problem is the pools making a delete slow then truncate will not be faster.
dropping the table and renaming the staging table means that you can mess with things.
you can insert the data in round robin. into the staging table
then you can change the allocation to an index based sorting AFTER while still on the staging table.
this means you can have a table stay live during the opperation and not have down time and because the drop and rename happen so quick you have very little chance of other things reading from that table breaking.

 

[Kosmik]

Truncate does not write to logs though which is why its significantly faster than delete. If you need to use static tables, best way of handling or you can do drops and creates but AFAIK that will still create log entries.

*edit* yes, my old neurons seemed to recall correctly.

Difference between DROP and TRUNCATE in SQL - GeeksforGeeks

Your All-in-One Learning Portal: GeeksforGeeks is a comprehensive educational platform that empowers learners across domains-spanning computer science and programming, school education, upskilling, commerce, software tools, competitive exams, and more.

www.geeksforgeeks.org

 

[Baxteen]

Truncate does not write to logs though which is why its significantly faster than delete. If you need to use static tables, best way of handling or you can do drops and creates but AFAIK that will still create log entries.

*edit* yes, my old neurons seemed to recall correctly.

Difference between DROP and TRUNCATE in SQL - GeeksforGeeks

Your All-in-One Learning Portal: GeeksforGeeks is a comprehensive educational platform that empowers learners across domains-spanning computer science and programming, school education, upskilling, commerce, software tools, competitive exams, and more.

www.geeksforgeeks.org

solid point I did not consider log files.

strong doubt the log files is what is causing this problem

 

[Kosmik]

solid point I did not consider log files.

strong doubt the log files is what is causing this problem

Problem is really architecture and scale.

 

[bridgeburner]

Thanks for the responses everyone. Some useful points. Yeah this thing is a real cluster ****. Based on these responses and what I was thinking anyway, is that it seems like a fundamental architecture issue. This thing was put together before my time and my team has been dumped with cleaning it up.

OP have you worked with SSAS before or are you a DBA Dev Primarily?
@Kosmik gave you the perspective and approach I’d follow tbh.

Unfortunately don't have experience with SSAS but something I'll look into. Not a DBA - am a C# guy

@bridgeburner what I would reccomend with your delete process.

instead of deleting records.
create a Staging table and rebuild the table in the opperation. so if the eventual table name was going to be dbo.tableData you make a dbo.stg_TableData
after the staging table is populated, you drop dbo.tableData and then you can rename the staging table:

Yeah was thinking about something like this as per my OP but just worried about shifting the problem from deletes to inserts as not all data gets deleted. Its only for a particular date range. So effectively with this approach, I'd have to copy the data to the staging which could be significant chunk. I'll need to do some benchmarks

exactly. based on the bahaviour in OP with delete taking forever it sounds like the pools are what is causing the problem.
records sit clustered in one pool so the compute is not spreading load.

At the moment, it's on a P6 - no elastic pools. When this thing is running, the DTUs aren't even maxed out. They sit around 70-80%

 

[Kosmik]

Thanks for the responses everyone. Some useful points. Yeah this thing is a real cluster ****. Based on these responses and what I was thinking anyway, is that it seems like a fundamental architecture issue. This thing was put together before my time and my team has been dumped with cleaning it up.


Unfortunately don't have experience with SSAS but something I'll look into. Not a DBA - am a C# guy


Yeah was thinking about something like this as per my OP but just worried about shifting the problem from deletes to inserts as not all data gets deleted. Its only for a particular date range. So effectively with this approach, I'd have to copy the data to the staging which could be significant chunk. I'll need to do some benchmarks


At the moment, it's on a P6 - no elastic pools. When this thing is running, the DTUs aren't even maxed out. They sit around 70-80%

Personally,

1. Offload basic summised data to a reporting db away from your transactional
2. Offload reporting data to a proper data cluster proccesor and jobs. Will be far cheaper than a p6.

We crunch proxy logs, couple gigs a text file per hour ( x 24 obviously ), databricks cluster chews a day in under a hour but it all depends on your workers etc but you have FAR more flexibility.

 

[Baxteen]

At the moment, it's on a P6 - no elastic pools. When this thing is running, the DTUs aren't even maxed out. They sit around 70-80%

I feel like you need to check a bit more.
I really feel like this could be 90% of the issue.
trust me on the videos I posted. I believe you are not fully understanding that I was talking about and that will be because of my insability to explain.
I jsut had that same issue wiht one of our servers. the system design was taken over from an on prem solution to an AWS solution.

on prem takes 1.2 Bil records and processes it in 7 mins.
the AWS solution ran more than an hour on only 7 milion records. indexes was the same. took a long time to figure out what the problem was and it was because the AWS soliution was doing everything with default leading to performance bottle necks.

 

[Kosmik]

My little config for checking jobs.

Debug Cluster



Or if I'm serious, take this up to 12 workers or more but you hit a parrelism vs time benefit limit.