Troubleshoot a query

Question

Hi,

I tried to retrieve a view which is calling multiple views and tables. It was running fast but recently causing issue in performance and not returning any data after running for long time.

Please help me how can I trouble shoot it. Showing the estimated execution plan but not showing the actual execution plan or query output after a long run.

Answer 1

Hi @SVA

Run the query with SET STATISTICS TIME ON and SET STATISTICS IO ON. Check whether the performance is poor due to physical I/O. If the data page to be accessed by a statement is not cached in memory in advance, then the data page must be read from disk to memory during the operation of the statement, which is a very expensive action.

Please refer to this doc for more details: Troubleshoot slow-running queries on SQL Server.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Please help me how can I trouble shoot it.

With that less information it's not possible.

Could be blocking by other processes, or parameter sniffing issue or a lot more.

First check the execution plan of your query.