September 27, 2006 at 8:10 am
Users experience login performance issue with one of our applications (using ASP.NET). This only happened once in the past 6 months, then in the past two weeks , it almost happens every other day. when a user try to login to the application, the login process just hangs ...
the SQL Profiler clearly shows that there is an issue with this login process "duration" is almost 37 minutes. We contacted the vendor, they have no clues, the only suggestion they can give is to stop and re-start the SQL server, this does work ... but why we have to do this everyday? Wonder if anyone have similar issue ...
September 27, 2006 at 1:59 pm
when this happens, take a look at the server they are connecting to (where the SQL DB is located). make sure the IO and processor is not getting maxed out. also check your memory on your sql server. how much ram is in the box and how much is dedicated just to sql? also, is this box being used for anything else (file server, etc..)? if the bandwidth is getting hammered you might have a bottle neck...start there and we will go from there.
September 28, 2006 at 9:20 am
I'd also start shopping for another software vendor as well. The response from the software vendor of "they have no clues" is clearly unacceptable. After all they wrote and architected this application, they should have some ideas other than "restart SQL Server" ... well it's a tad better that telling you to reboot the server anyway ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
September 29, 2006 at 12:03 am
The same problems happended to me many times, and the reasons differed everytime. I agree with CDB that everything is possible, and I would start from checking the memory and CPU on the server.
If no lack of resources, and if the duration is almost 37 minutes, I will examine whether the login query/queries are efficient. If you don't know how, your software vendor should know.
If the login queries are OK, please also look for other big queries that may happen at the same time and possibly hang the login query. There was once some other people installed a software running sub-optimal sql queries without letting me know, and those queries brought my stuff down. It took me quite a long time to find out and to prove it was other people's fault.
Good luck.
September 29, 2006 at 7:18 am
Can you step through the logon process with the Studio debugger and check that all variables are populated correctly? I had a case where the USERID went NULL in the buffer due to security settings. I've got to beleive that your process never gets to SQL Server or you would time out.
September 29, 2006 at 7:58 am
Thanks for everybody's input. Our NT admin said restarting the application-specific application pool fixed the problem. He said he have segregated applications into separate application pools in order to keep from impacting other sites/applications when problems occur. I am not familar with this IIS/ASP stuff but I certainly hope this is THE answer. I'll keep you guys updated on this ... thanks again
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply