Showing results for 
Show  only  | Search instead for 
Did you mean: 
Engaged Sweeper

We've noticed a view running in our LanSweeper database that regularly runs several times a day that takes about 25 minutes to complete and consumes upwards of 200GB in tempdb during its run-cycle.

SELECT COUNT(1) AS total FROM web50rep6ba596c64f8b45f7ad934569fa643b5
This is the SQL behind the query:
SELECT TOP (1000000) dbo.htblticket.ticketid, '#' + CAST(dbo.htblticket.ticketid AS nvarchar) AS Ticket, AS CreationDate, dbo.htblticket.updated AS LastUpdated, dbo.htbltickettypes.typename AS Type,
dbo.htblticketstates.statename AS State, AS Priority, AS Source, AS [User], AS AssignedAgent, AS UserLastNote, dbo.htblticket.subject,
MAX( AS Closure
FROM dbo.htblticket INNER JOIN
dbo.htblpriorities ON dbo.htblpriorities.priority = dbo.htblticket.priority INNER JOIN
dbo.htblticketstates ON dbo.htblticketstates.ticketstateid = dbo.htblticket.ticketstateid INNER JOIN
dbo.htblusers ON dbo.htblusers.userid = dbo.htblticket.fromuserid LEFT OUTER JOIN
dbo.htblagents ON dbo.htblagents.agentid = dbo.htblticket.agentid LEFT OUTER JOIN
dbo.htblusers AS htblusers1 ON htblusers1.userid = dbo.htblagents.userid INNER JOIN
dbo.htbltickettypes ON dbo.htblticket.tickettypeid = dbo.htbltickettypes.tickettypeid INNER JOIN
dbo.htblsource ON dbo.htblticket.sourceid = dbo.htblsource.sourceid INNER JOIN
dbo.htblusers AS htblusers2 ON dbo.htblticket.userid_lastnote = htblusers2.userid INNER JOIN
dbo.htblhistory ON dbo.htblticket.ticketid = dbo.htblhistory.ticketid INNER JOIN
dbo.htblhistorytypes ON dbo.htblhistorytypes.typeid = dbo.htblhistory.typeid INNER JOIN
dbo.htblticketstates AS htblticketstates1 ON htblticketstates1.ticketstateid = dbo.htblhistory.ticketstateid CROSS JOIN
WHERE (dbo.htblticketstates.statename = 'Closed') AND (dbo.htblticket.spam <> 'True') AND ( IN ('Status changed', 'Note added and state changed', 'Internal note added and state changed')) AND
(htblticketstates1.statename = 'Closed')
GROUP BY dbo.htblticket.ticketid, '#' + CAST(dbo.htblticket.ticketid AS nvarchar),, dbo.htblticket.updated, dbo.htbltickettypes.typename, dbo.htblticketstates.statename,,,,,, dbo.htblticket.subject
ORDER BY Closure DESC, dbo.htblticket.ticketid
I'd like to know if there is some way this can be optimized and what's producing this beast?


Lansweeper Tech Support
Lansweeper Tech Support

Hello there!

We would recommend contacting our support team to investigate this:

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now