MS SQL performance from 10" to 3 minutes

Hello all !

I am runing from .NET application an SQL Query
it normally return the rows in 10 seconds
but time to time the application turn 2 or 3 minutes and nearlly crash (or crash)

with exactly the same datas in database

what can be the reasons ?

thank youcheck whether the session is getting expired or not if not kill it.|||other reason could be lock put on the table during the transaction which may keep the DB server busy.
Also check if some other query seeks a large resultset from DB.|||ppavan21 if I kill the session and a user is logged-in he will be thrown, I cannot do it , or do you see a solution ?

wash : is there a way to unlock ? ot what can I do ?

on 5 rows it takes normally less than one second, sometimes it can turn a few minutes and crash with exactly the sames datas

thank you|||the reasons for this can vary widely.

things to check...

1. open up the task manager to see if it is the sqlserver process consuming resources. Are you running anything on the machine? IIS? exchange?

2. run sp_who\sp_who2\sp_lock to look for blocking\resource intensive operations or excessive locking.

3. Open up the performance monitor and make sure you disk que length is under 3.

4. Have you looked at the execution plan of the query that varies in execution time? Are there any table\index scan as opposed to index seeks in the plan? If the query can return vastly varying amounts of data, have you tried adding WITH RECOMPILE to the query? Have you recompiled the stored procedure lately? Are the indexes that the query is using heavily fragmented?

That should keep you busy.|||RECOMPILE ? i didn't know it was even possible
how do yo do it ?|||recompiling is sometimes beneficial if there has been a large amount of data added to your database recently which can have the effect of making your execution plan out of date.

see sp_recompile in Books Online.|||Sean,

I believe you assume That this is a sproc

I got Money that it's not|||oh probably not. dude can probably use a little BOL reading anyways.