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, dbo.htblticket.date AS CreationDate, dbo.htblticket.updated AS LastUpdated, dbo.htbltickettypes.typename AS Type,
dbo.htblticketstates.statename AS State, dbo.htblpriorities.name AS Priority, dbo.htblsource.name AS Source, dbo.htblusers.name AS [User], htblusers1.name AS AssignedAgent, htblusers2.name AS UserLastNote, dbo.htblticket.subject,
MAX(dbo.htblhistory.date) 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
dbo.web50rephelptickettimeworked
WHERE (dbo.htblticketstates.statename = 'Closed') AND (dbo.htblticket.spam <> 'True') AND (dbo.htblhistorytypes.name 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.date, dbo.htblticket.updated, dbo.htbltickettypes.typename, dbo.htblticketstates.statename, dbo.htblpriorities.name, dbo.htblsource.name,
dbo.htblusers.name, htblusers1.name, htblusers2.name, dbo.htblticket.subject
HAVING (MAX(dbo.htblhistory.date) > GETDATE() - 7)
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?
