After upgrading to .5423 yesterday, we have experienced issues with SQL queries made by smartertrack consuming all CPU resources.

 

When checking client connections I see several queries stuck with the same query. The state stays on "Sending Data" and stays there. I am currently having to constantly having to monitor this and kill the queries otherwise the server becomes unresponsive.

 

The lag introduced is also causing SmarterTrack to duplicate tickets and call logs. Sometimes making 3 or 4 copies of the same ticket.

 

The query it's running is below. It's always the same.

SELECT T.TicketID
FROM
(
SELECT
T1.TicketID,
T1.DepartmentID,
T1.GroupID,
T1.IsDeleted,
T1.IsSpam,
T1.TicketStatusID,
T1.UserID,
(
SELECT SUM(TOKEN.Score)
FROM
(
SELECT 10000 + STT.TokenCount * (case when S.WeightFactor is null then 0.5 else S.WeightFactor end) * F.Weight AS Score, STT.SearchFieldID, STT.SearchTokenID, STT.TicketID
FROM st_SearchTokensInTickets AS STT
INNER JOIN st_SearchFieldsInSearchTypes AS F ON STT.SearchFieldID = F.SearchFieldID and F.SearchTypeID = 2
LEFT JOIN st_SearchTokenStats AS S ON (STT.SearchFieldID = S.SearchFieldID) AND (STT.SearchTokenID = S.SearchTokenID)
) AS TOKEN
WHERE
TOKEN.SearchFieldID IN (5,4,6,18) AND
TOKEN.SearchTokenID IN (21037) AND
TOKEN.TicketID = T1.TicketID
) AS Score
FROM st_Tickets AS T1
) AS T
WHERE
T.DepartmentID IN (8,6,5,38,46) AND
T.IsDeleted = 0 AND
T.IsSpam = 0 AND
T.TicketStatusID IN (1) AND
(T.UserID = 1 or T.UserID is null) AND
T.GroupID IN (10,78,59,74,7,76,77,63,28,31,79) AND
T.Score > 0
ORDER BY T.Score DESC, T. /* statement may be truncated */

I will also open a ticket for this as I need an immediate response as it's killing our server preventing us from logging tickets.