Unanswered: 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!
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?