Query taking ages for no apparent reason

Hope someone can help me with this because its driving me potty!

I have a .NET script that sends really simple queries to SQL server that works perfectly 50% of the time but for the other 50% it takes ages (2-3 minutes) and then fails, I'm assuming because it times out. I then check the SQL by excecuting it via query analyzer and it again takes ages but will work eventually (I'm assuming because this bypasses the timeout settings, but changing these isn't on).

This happens randomly, the scripts will be working fine and then fail a few times before magically working again!

Any ideas? Perhaps some database features that commonly cause this problem? The problem only occurs with one database, all our others are fine but we can't spot any differences!

Any help or tips would really be appreciated.

Thanks.sound like a locking issue.

When you are running the query via .net, in query analyzer in a seperate session run sp_who2. This will show you if there are any locked processes.

Even better use enterprise manager (if you have access)|||Originally posted by dbabren
sound like a locking issue.

When you are running the query via .net, in query analyzer in a seperate session run sp_who2. This will show you if there are any locked processes.

Even better use enterprise manager (if you have access)

Thanks for the advice.

There's no sign of locking when my problem is occuring using sp_who2 (I refreshed sp_who2 a few times whilst I was waiting for the query to give-up).

On the other hand, I had a look using enterprise manager->Locks/Object and there's a huge list of Table Locks (908!) owned by 'xact' (a transaction? )for the database i'm using. Other db's being used have database locks owned by the SESS (session I assume). I've never explicitly asked for a lock, but this db is someone else's so could there be somehting in there that aquires a lock?

Thanks for you help,

suddy.|||Suddy

Everytime you access the db, you will take a lock - the type and severity of that lock depends on what you are doing - have a look at locking in BOL (it can explain it better ..)

I find it easier to use locks/process id in Ent Manager as it is often easier to track the spid to a particular PC/trnsaction. It also tell you which process is blocking which other processes.

Another option may be to use profiler to track the SQL that is being ran, and capture blocking lock information - but this will have a performance impact itself (so be weary of it)|||Thanks for your help Dbabren.

Darned problem has mysteriously vanished this morning but I'm going to go away and have a look at BOL because this is bound to come back if I don't work out what's going on.

Thanks again.|||Originally posted by suddy
Hope someone can help me with this because its driving me potty!

I have a .NET script that sends really simple queries to SQL server that works perfectly 50% of the time but for the other 50% it takes ages (2-
Thanks.

Can you post the queries? Are you using the "NOLOCK" directive with your select statements?