I caught huge queries in DB and can not find the source of that

Question

I am actually just looking for opinions if someone had any 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 their 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 that 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 am still not too sure what causes that CPU waste but 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 and it doesn't look like SQL injection or EF query

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

Can I and How I track the query completely from query end to the project?

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

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","Col1205","Col1216","Col1220","Col1207","Col1208","Col1213","Col1221","Col1224","Col1217","Col1214","Col1222","Col1209","Col1211","Col1215","Col1194","Col1195","Col1197","Col1198","Col1141","Col1142","Col1151","Col1136","Col1145","Col1147","Col1171","Col1172","Col1186","Col1173","Col1175","Col1188","Col1189","Col1187","Col1176","Col1177","Col1118","Col1119","Col1114","Col1121","Col1125","Col1127","Col1128","Col1131","Col1116","Col1155","Col1156","Col1160","Col1165","Col1162","Col1163","Col1167","Col1112","Col1105","Col1106","Col1107","Col1109","Col1110","Col1100","Col1095","Col1089","Col1079","Col1080","Col1096","Col1082","Col1093","Col1097","Col1084","Col1085","Col1090","Col1098","Col1101","Col1094","Col1091","Col1099","Col1086","Col1088","Col1092","Col1074","Col1069","Col1063","Col1053","Col1054","Col1070","Col1056","Col1067","Col1071","Col1058","Col1059","Col1064","Col1072","Col1075","Col1068","Col1065","Col1073","Col1060","Col1062","Col1066","Col1048","Col1043","Col1037","Col1027","Col1028","Col1044","Col1030","Col1041","Col1045","Col1032","Col1033","Col1038","Col1046","Col1049","Col1042","Col1039","Col1047","Col1034","Col1036","Col1040" FROM (SELECT "Tbl1022"."Id" "Col1026","Tbl1022"."Discriminator" "Col1029","Tbl1022"."IsDeleted" "Col1031","Tbl1022"."Title" "Col1035","Tbl1022"."AttendanceId" "Col1048","Tbl1022"."CategoryId" "Col1043","Tbl1022"."CertificatedProgramId" "Col1037","Tbl1022"."CreatorUserId" "Col1027","Tbl1022"."DeleterUserId" "Col1028","Tbl1022"."DistrictId" "Col1044","Tbl1022"."EventCategoryId" "Col1030","Tbl1022"."ExamId" "Col1041","Tbl1022"."InstructionFileId" "Col1045","Tbl1022"."LastModifierUserId" "Col1032","Tbl1022"."OrganizationUnitId" "Col1033","Tbl1022"."ParentId" "Col1038","Tbl1022"."ProvinceId" "Col1046","Tbl1022"."RoomId" "Col1049","Tbl1022"."SurveyId" "Col1042","Tbl1022"."SyllabusFileId" "Col1039","Tbl1022"."TaskParentId" "Col1047","Tbl1022"."TenantId" "Col1034","Tbl1022"."TETrainingId" "Col1036","Tbl1022"."TrainingCategoryId" "Col1040" FROM "myDB"."dbo"."Core_Events" "Tbl1022" WHERE "Tbl1022"."IsDeleted"=(0) AND "Tbl1022"."Discriminator"=N'Task') Qry1050 RIGHT OUTER JOIN (SELECT "Tbl1020"."Id" "Col1052","Tbl1020"."Discriminator" "Col1055","Tbl1020"."IsDeleted" "Col1057","Tbl1020"."Title" "Col1061","Tbl1020"."AttendanceId" "Col1074","Tbl1020"."CategoryId" "Col1069","Tbl1020"."CertificatedProgramId" "Col1063","Tbl1020"."CreatorUserId" "Col1053","Tbl1020"."DeleterUserId" "Col1054","Tbl1020"."DistrictId" "Col1070","Tbl1020"."EventCategoryId" "Col1056","Tbl1020"."ExamId" "Col1067","Tbl1020"."InstructionFileId" "Col1071","Tbl1020"."LastModifierUserId" "Col1058","Tbl1020"."OrganizationUnitId" "Col1059","Tbl1020"."ParentId" "Col1064","Tbl1020"."ProvinceId" "Col1072","Tbl1020"."RoomId" "Col1075","Tbl1020"."SurveyId" "Col1068","Tbl1020"."SyllabusFileId" "Col1065","Tbl1020"."TaskParentId" "Col1073","Tbl1020"."TenantId" "Col1060","Tbl1020"."TETrainingId" "Col1062","Tbl1020"."TrainingCategoryId" "Col1066" FROM "myDB"."dbo"."Core_Events" "Tbl1020" WHERE "Tbl1020"."IsDeleted"=(0) AND "Tbl1020"."Discriminator"=N'SurveyAssignment') Qry1076 RIGHT OUTER JOIN (SELECT "Tbl1018"."Id" "Col1078","Tbl1018"."Discriminator" "Col1081","Tbl1018"."IsDeleted" "Col1083","Tbl1018"."Title" "Col1087","Tbl1018"."AttendanceId" "Col1100","Tbl1018"."CategoryId" "Col1095","Tbl1018"."CertificatedProgramId" "Col1089","Tbl1018"."CreatorUserId" "Col1079","Tbl1018"."DeleterUserId" "Col1080","Tbl1018"."DistrictId" "Col1096","Tbl1018"."EventCategoryId" "Col1082","Tbl1018"."ExamId" "Col1093","Tbl1018"."InstructionFileId" "Col1097","Tbl1018"."LastModifierUserId" "Col1084","Tbl1018"."OrganizationUnitId" "Col1085","Tbl1018"."ParentId" "Col1090","Tbl1018"."ProvinceId" "Col1098","Tbl1018"."RoomId" "Col1101","Tbl1018"."SurveyId" "Col1094","Tbl1018"."SyllabusFileId" "Col1091","Tbl1018"."TaskParentId" "Col1099","Tbl1018"."TenantId" "Col1086","Tbl1018"."TETrainingId" "Col1088","Tbl1018"."TrainingCategoryId" "Col1092" FROM "myDB"."dbo"."Core_Events" "Tbl1018" WHERE "Tbl1018"."IsDeleted"=(0) AND "Tbl1018"."Discriminator"=N'ExamAssignment') Qry1102 RIGHT OUTER JOIN (SELECT "Tbl1016"."Id" "Col1104","Tbl1016"."IsDeleted" "Col1108","Tbl1016"."Title" "Col1111","Tbl1016"."ContentCategoryId" "Col1112","Tbl1016"."ContentFileId" "Col1105","Tbl1016"."CreatorUserId" "Col1106","Tbl1016"."DeleterUserId" "Col1107","Tbl1016"."LastModifierUserId" "Col1109","Tbl1016"."TenantId" "Col1110" FROM "myDB"."dbo"."LMS_Contents" "Tbl1016" WHERE "Tbl1016"."IsDeleted"=(0)) Qry1113 RIGHT OUTER JOIN (SELECT "Tbl1012"."CompletionStatus" "Col1117","Tbl1012"."ElapsedTime" "Col1120","Tbl1012"."FirstEnterDate" "Col1122","Tbl1012"."IsDeleted" "Col1123","Tbl1012"."LastEnterDate" "Col1124","Tbl1012"."Score" "Col1126","Tbl1012"."TrainingItemId" "Col1129","Tbl1012"."TrainingParticipantId" "Col1130","Tbl1012"."Entrance" "Col1132","Tbl1012"."ProgressMeasure" "Col1133","Tbl1012"."SuccessStatus" "Col1134","Tbl1012"."CreatorUserId" "Col1118","Tbl1012"."DeleterUserId" "Col1119","Tbl1012"."EventSessionId" "Col1114","Tbl1012"."ExamAssignmentTakerId" "Col1121","Tbl1012"."LastModifierUserId" "Col1125","Tbl1012"."SurveyAssignmentTakerId" "Col1127","Tbl1012"."TaskAssignmentId" "Col1128","Tbl1012"."TrainingTopicId" "Col1131","Tbl1012"."Id" "Col1116" FROM "myDB"."dbo"."LMS_TrainingParticipantItems" "Tbl1012" WHERE "Tbl1012"."IsDeleted"=(0)) Qry1135 RIGHT OUTER JOIN (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","Col1154","Col1157","Col1158","Col1159","Col1161","Col1164","Col1166","Col1223","Col1218","Col1212","Col1202","Col1203","Col1219","Col1205","Col1216","Col1220","Col1207","Col1208","Col1213","Col1221","Col1224","Col1217","Col1214","Col1222","Col1209","Col1211","Col1215","Col1194","Col1195","Col1197","Col1198","Col1141","Col1142","Col1151","Col1136","Col1145","Col1147","Col1171","Col1172","Col1186","Col1173","Col1175","Col1188","Col1189","Col1187","Col1176","Col1177","Col1155","Col1156","Col1160","Col1165","Col1162","Col1163","Col1167" FROM (SELECT "Tbl1008"."ContentId" "Col1138","Tbl1008"."ExamAssignmentId" "Col1139","Tbl1008"."Id" "Col1140","Tbl1008"."Discriminator" "Col1143","Tbl1008"."IsDeleted" "Col1144","Tbl1008"."Order" "Col1146","Tbl1008"."TrainingTopicId" "Col1148","Tbl1008"."SurveyAssignmentId" "Col1149","Tbl1008"."TaskId" "Col1150","Tbl1008"."CreatorUserId" "Col1141","Tbl1008"."DeleterUserId" "Col1142","Tbl1008"."EventSessionId" "Col1151","Tbl1008"."GradeBookId" "Col1136","Tbl1008"."LastModifierUserId" "Col1145","Tbl1008"."TenantId" "Col1147" FROM "myDB"."dbo"."LMS_TrainingItems" "Tbl1008" WHERE "Tbl1008"."IsDeleted"=(0)) Qry1152,(SELECT "Col1201","Col1204","Col1206","Col1210","Col1193","Col1196","Col1191","Col1170","Col1174","Col1178","Col1179","Col1180","Col1181","Col1182","Col1183","Col1184","Col1185","Col1154","Col1157","Col1158","Col1159","Col1161","Col1164","Col1166","Col1223","Col1218","Col1212","Col1202","Col1203","Col1219","Col1205","Col1216","Col1220","Col1207","Col1208","Col1213","Col1221","Col1224","Col1217","Col1214","Col1222","Col1209","Col1211","Col1215","Col1194","Col1195","Col1197","Col1198","Col1171","Col1172","Col1186","Col1173","Col1175","Col1188","Col1189","Col1187","Col1176","Col1177","Col1155","Col1156","Col1160","Col1165","Col1162","Col1163","Col1167" FROM (SELECT "Tbl1014"."Id" "Col1154","Tbl1014"."EmailAddress" "Col1157","Tbl1014"."IsDeleted" "Col1158","Tbl1014"."LastLoginTime" "Col1159","Tbl1014"."Name" "Col1161","Tbl1014"."Surname" "Col1164","Tbl1014"."UserName" "Col1166","Tbl1014"."CreatorUserId" "Col1155","Tbl1014"."DeleterUserId" "Col1156","Tbl1014"."LastModifierUserId" "Col1160","Tbl1014"."TenantId" "Col1165","Tbl1014"."NormalizedEmailAddress" "Col1162","Tbl1014"."NormalizedUserName" "Col1163","Tbl1014"."UserTitleId" "Col1167" FROM "myDB"."dbo"."Core_Users" "Tbl1014" WHERE "Tbl1014"."IsDeleted"=(0)) Qry1168,(SELECT "Col1201","Col1204","Col1206","Col1210","Col1193","Col1196","Col1191","Col1170","Col1174","Col1178","Col1179","Col1180","Col1181","Col1182","Col1183","Col1184","Col1185","Col1223","Col1218","Col1212","Col1202","Col1203","Col1219","Col1205","Col1216","Col1220","Col1207","Col1208","Col1213","Col1221","Col1224","Col1217","Col1214","Col1222","Col1209","Col1211","Col1215","Col1194","Col1195","Col1197","Col1198","Col1171","Col1172","Col1186","Col1173","Col1175","Col1188","Col1189","Col1187","Col1176","Col1177" FROM (SELECT "Tbl1010"."Id" "Col1170","Tbl1010"."IsDeleted" "Col1174","Tbl1010"."UserId" "Col1178","Tbl1010"."CompletionStatus" "Col1179","Tbl1010"."CompletionTime" "Col1180","Tbl1010"."Discriminator" "Col1181","Tbl1010"."CompletionPercentage" "Col1182","Tbl1010"."LastEnterDate" "Col1183","Tbl1010"."Point" "Col1184","Tbl1010"."TrainingId" "Col1185","Tbl1010"."CreatorUserId" "Col1171","Tbl1010"."DeleterUserId" "Col1172","Tbl1010"."ExamAssignmentId" "Col1186","Tbl1010"."GroupId" "Col1173","Tbl1010"."OrganizationUnitId" "Col1175","Tbl1010"."RoomAssignmentId" "Col1188","Tbl1010"."RoomAssignmentId1" "Col1189","Tbl1010"."SurveyAssignmentId" "Col1187","Tbl1010"."TaskId" "Col1176","Tbl1010"."TenantId" "Col1177" FROM "myDB"."dbo"."Core_EventAssignments" "Tbl1010" WHERE "Tbl1010"."IsDeleted"=(0) AND "Tbl1010"."Discriminator"=N'TrainingParticipant' AND ("Tbl1010"."TrainingId" IS NOT NULL)) Qry1190,(SELECT "Col1201","Col1204","Col1206","Col1210","Col1193","Col1196","Col1191","Col1223","Col1218","Col1212","Col1202","Col1203","Col1219","Col1205","Col1216","Col1220","Col1207","Col1208","Col1213","Col1221","Col1224","Col1217","Col1214","Col1222","Col1209","Col1211","Col1215","Col1194","Col1195","Col1197","Col1198" FROM (SELECT "Tbl1006"."Id" "Col1193","Tbl1006"."IsDeleted" "Col1196","Tbl1006"."TrainingId" "Col1191","Tbl1006"."CreatorUserId" "Col1194","Tbl1006"."DeleterUserId" "Col1195","Tbl1006"."LastModifierUserId" "Col1197","Tbl1006"."TenantId" "Col1198" FROM "myDB"."dbo"."LMS_TrainingTopics" "Tbl1006" WHERE "Tbl1006"."IsDeleted"=(0)) Qry1199,(SELECT "Tbl1004"."Id" "Col1201","Tbl1004"."Discriminator" "Col1204","Tbl1004"."IsDeleted" "Col1206","Tbl1004"."Title" "Col1210","Tbl1004"."AttendanceId" "Col1223","Tbl1004"."CategoryId" "Col1218","Tbl1004"."CertificatedProgramId" "Col1212","Tbl1004"."CreatorUserId" "Col1202","Tbl1004"."DeleterUserId" "Col1203","Tbl1004"."DistrictId" "Col1219","Tbl1004"."EventCategoryId" "Col1205","Tbl1004"."ExamId" "Col1216","Tbl1004"."InstructionFileId" "Col1220","Tbl1004"."LastModifierUserId" "Col1207","Tbl1004"."OrganizationUnitId" "Col1208","Tbl1004"."ParentId" "Col1213","Tbl1004"."ProvinceId" "Col1221","Tbl1004"."RoomId" "Col1224","Tbl1004"."SurveyId" "Col1217","Tbl1004"."SyllabusFileId" "Col1214","Tbl1004"."TaskParentId" "Col1222","Tbl1004"."TenantId" "Col1209","Tbl1004"."TETrainingId" "Col1211","Tbl1004"."TrainingCategoryId" "Col1215" FROM "myDB"."dbo"."Core_Events" "Tbl1004" WHERE "Tbl1004"."IsDeleted"=(0) AND "Tbl1004"."Discriminator"=N'Training' AND "Tbl1004"."Id"<>(6)) Qry1225 WHERE "Col1201"="Col1191") Qry1226 WHERE "Col1185"="Col1191") Qry1227 WHERE "Col1178"="Col1154") Qry1228 WHERE "Col1193"="Col1148") Qry1229 ON "Col1170"="Col1130" AND "Col1193"="Col1148" AND "Col1140"="Col1129" ON "Col1138"="Col1104" AND "Col1143"=N'TrainingContent' ON "Col1139"="Col1078" AND "Col1143"=N'TrainingExam' ON "Col1149"="Col1052" AND "Col1143"=N'TrainingSurvey' ON "Col1150"="Col1026" AND "Col1143"=N'TrainingTask'

Answer 1

The query indeed looks weird - could it be coming from some export function, say, to Excel? Are you sure it's from your project (application)? Would it be possible for the customer to not use your application, say, for one day and see if the query will still show up?

Answer 2

I added the whole query for clarification, I have none of col1xxx or tbl1xxx named tables or columns,

These are aliases. Here, one of the table aliases are defined:

``´
"myDB"."dbo"."Core_Events" "Tbl1004"

And here is a definition of a column alias:

Tbl1004"."CategoryId" "Col1218"

This is the same as

Tbl1004"."CategoryId" AS "Col1218"

Have you tracked down the spid that runs the query and information about it?

It looks a little bit like something that is generated by a query on a remote server using a linked server to your server.