I am actually just looking for opinions if someone had similar experience rather than an exact solution of my problem.

My project is Asp.net core 2.2 mvc project with abp framework, One of my customers has its own servers instead of cloud, sometimes(one or two times in a day) DB server's CPU hits 100% and stays there for a long time and locks out every transaction, this makes me have to restart the sql service. Everytime when that happens I am restarting the service to solve this problem but obviously this is not a permanent solution.

I upgraded the project to Asp.net core 6 and everything else is up to date now but that customer's DB and services are so huge because of that it will not be easy to upgrade them, I am probably going to upgrade them in the next months

I tracked database transactions when CPU hits 100%, and I found a weird query that gets millions of millions row from DB and I can't find its source, this query lasts more than 4 hours, it doesn't look like SQL injection or EF query

Just small part of the query looks like this

SELECT "Col1201","Col1204","Col1206","Col1210","Col1193","Col1196","Col1191","Col1138","Col1139","Col1140","Col1143","Col1144","Col1146","Col1148","Col1149","Col1150","Col1170","Col1174","Col1178","Col1179","Col1180","Col1181","Col1182","Col1183","Col1184","Col1185","Col1117","Col1120","Col1122","Col1123","Col1124","Col1126","Col1129","Col1130","Col1132","Col1133","Col1134","Col1154","Col1157","Col1158","Col1159","Col1161","Col1164","Col1166","Col1104","Col1108","Col1111","Col1078","Col1081","Col1083","Col1087","Col1052","Col1055","Col1057","Col1061","Col1026","Col1029","Col1031","Col1035",CASE WHEN "Col1143"=N'TrainingContent' THEN "Col1111" ELSE CASE WHEN "Col1143"=N'TrainingExam' THEN "Col1087" ELSE CASE WHEN "Col1143"=N'TrainingSurvey' THEN "Col1061" ELSE CASE WHEN "Col1143"=N'TrainingTask' THEN "Col1035" ELSE @P1 END END END END "Expr1023","Col1223","Col1218","Col1212","Col1202","Col1203","Col1219","

I searched DB logs, app data logs and server logs at the time when CPU hits 100% and I couldn't find its source

When I try to open profiler for a day, it makes all system halt so I can't do that

I am tired and confused right now. Can this be solved when I upgrade everything in the project?