3

Here is a query that uses a lot of CPU on my SQL Server (8 cores):

DELETE TOP(1) rd
FROM
  (SELECT TOP 99.9999 PERCENT *
   FROM APM_IisBb_Request_Details
   ORDER BY RequestDate ASC) rd
JOIN APM_IisBb_Request r ON rd.RequestID = r.ID
WHERE r.SiteID = 33355

The query plan is here.

Looks like some purge process from a vendor app. Is there any way to improve it without query modification?

It is generated by a binary app and I can't change the query for now, so I am looking for some indexes maybe or optimizer hints.

It's running on SQL Server Standard Edition 2012.

Table 'APM_IisBb_Request_Details'. Scan count 1, logical reads 122719, physical reads 0,
    read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 3193604, physical reads 0,
    read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'APM_IisBb_Request'. Scan count 1, logical reads 2002, physical reads 0,
    read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  • 2
    Have you tried asking SolarWinds for support tuning their monitoring tool queries? – Erik Darling May 18 '18 at 11:44
  • thanks, Erik, I just addressed that to their support, waiting for their reply, not sure how fast they could provide fix, we will see – user130071 May 18 '18 at 12:25
  • Is your clustered index ordered by RequestDate? I can't tell. If it isn't, you might get a humongous performance boost if you cluster on RequestDate. – James May 18 '18 at 14:39
  • 2
    Please edit the question and add the CREATE TABLE statements for both tables, including indexes (and for all the underlying tables if any of them is a view). Also add whether you are allowed to add more indexes or modify existing ones. – ypercubeᵀᴹ May 18 '18 at 15:56
  • Is there an index on APM_IisBb_Request_Details with RequestId as the first key column and RequestDate in there somewhere as a key or included column? I'd say this has about a 50% chance of making a major difference, but it's worth a try. – Steve Hood Apr 15 '19 at 17:27