One of our application using an SQL query is reported running slow.

Current execution time:- 15 minutes and so

Earlier execution time:- 30 seconds.

SQL-Sentry Plan for the slow query

enter image description here

I've checked for Fragmentation and stats if they are up to date. The weekend job for same executed successfully and seems OK for this point.

When I am using SQL-sentry plan explorer to check for the execution plan it shows me RED for a table going through clustered index seek and Green for Key Lookup.

I am new on how to analyse the plan.

Kindly advice on how to proceed and improve the performance of the query, thanks!

  • 1
    Kapil you know that you have to post query with execution plan else it would not be possible to tell where the problem lies. – Shanky Mar 9 '15 at 14:19
  • 2
    30 seconds to 15 minutes sounds like blocking to me, not a slightly inefficient plan. – Aaron Bertrand Mar 9 '15 at 14:21
  • @AaronBertrand , I ran the query manually few minutes back and check for any blocking, but could not find one. However when i used Sp_whoisactive to find running queries i see the query i just ran in question got suspended and other query in running status executing on same DB, on surprise no blocking shown. – KASQLDBA Mar 9 '15 at 15:40

Your Answer

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

Browse other questions tagged or ask your own question.