Randomly running queries taking more time going into loop even basic queries

1

On our sql server, most of the times the queries run fast same query and the same query again taking too long to execute

and going into loop, on the reports it says loading and i wait almost an hour it doesn't time out.

I copied the exact query to sql server query analyzer and the same behaviour.

Not sure how to copy my activity monitor. here.

Please any suggestions advice.

2

Posting activity monitor probably wouldn't help. But, if you can post the code that exhibits this behavior would be helpful. Even better would be if you can post the DDL for the tables and scripts to populate those tables along with the query so someone can copy it and run it in their environment.

This type of random poor performance can happen for a number of reasons:

  1. There is extra load on the server
  2. There is resource contention - for example, a table the query needs is locked by another process/query
  3. Poor query execution plan for certain combinations of input parameters (aka parameter sniffing).