simple sql performance question

[ktucci]

here is a simple question, but just recently this issue is really causing havoc

imagine i have a stored proc that simply select some rows and it takes 10 seconds to run it query analyzer...now imagine i run this same query 2 times concurrently through query analyzer and now it takes 18 seconds for each one to run...is this normal behavior for sql server and if so how do get around this...keep in mind, i am talking very generally here simple select statement the nolock hint...i have tried this on many servers, with no other sql usage going on at the same time with the same reaults more or less on each server

any help will be appreciated...thanks

-keith

 

[SQLBill]

One thing to check...in Enterprise Manager, drill down to your database. Right click on the database, select Properties. Click on the Options tab. Is Auto-Close on (checked) or off?

Auto-Close closes the connection when a query is done running. This means that the connection has to be re-openned every time a query is run, even if they are run concurrently.

-SQLBill

 

[ktucci]

thanks

auto close is unchecked...the problem is really with an application that spawns executables to run a FOR XML statement...while one is running it takes 30 seconds to complete, but while 2 are running the time doubles for one to complete...the query analyzer example is just so i can exclude that the problem is unique to my long running FOR XML statement...