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
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.