SQL sometimes stops responding to web apps

[1DMF]

Hi,

We have started to experience random web app hanging, which seems to be a SQL connection / response issue.

If I stop and restart the SQL server, the web apps start working fine again.

I have checked and the SQL server is set to allow unlimited connections and to allow remote connections.

What could be causing SQL to stop responding to web app requests?

What tools or queries should I be using to investigate the problem and what should I be looking for?

Thanks,

1DMF.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[gmmastros]

I had a problem with a web service I was writing. The cause of my problem was with closing the database connection... specifically... I wasn't doing it. Luckily I noticed this before the code went in to production.

To troubleshoot this issue, I opened SQL Server Management Studio and ran "sp_who2". This showed me all of the connections to the database. I was surprised to see many connections coming from the web service application. I then modified the code to make sure I was closing the connection and the problems went away.

I suggest that you run sp_who2 to see if there are any residual connections to the database that you don't expect. If there are, then take a look at the web code to verify that you are closing the connection.

I'm not saying this is your problem. It was a problem for me, and one that only takes a couple minutes to check.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

You know what George I think you're on to something.

It was a new component I was building that utilised my new Perl library objects I've been developing using Moose for the switch over to the Catalyst framework.

The SQL ORM (I use the term loosely!), was re-written for Catalyst as a singleton and so closing the DB is not done as one connection during the life of the worker process serves all DB requests.

I forgot about this when utilising this new SQL library object outside of Catalyst and so the connection isn't closed.

I will be adding a new close method to the object so I can call this method from within standard Perl scripts when used outside of Catalyst.

I would have thought when the script ended and all objects were garbage collected it might close the connection anyway, but the Win32::ODBC driver module I use states this clearly in the documentation...

Finally, MAKE SURE that you close your connection when you are finished:

Dunno if this is the issue, but it looks plausible.

I'll let you know how it pans out.

Thanks,
1DMF.


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[1DMF]

Well I had another hang and it wasn't related to any web app.

I had a query attached to a form in an MS access app, the query joined an SP resutset with a standard access query and this was at times hanging and locking out the entire SQL server (the standard query not the SP), I could see one PID was causing all other users to hang.

I refactored the query bound to the form to use two SP's in the join and set the new SP to use WITH NOLOCK and so far it hasn't happened again.

Hopefully this was the problem and the cure!

Thanks for your input George, it is always appreciated.



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[yelworcm]

The NOLOCK hint kind of cuts in two ways. It takes out no locks, but it also respects no locks. This can mean you could get "dirty reads" (read data that has been updated, but not yet committted). This is not always a problem for all applications, but it should be considered before the NOLOCK hint is brought out.

Could there be a uncommitted transaction hanging somewhere in the application? Most locks are released when the query finishes. The exception is an explicit transaction.

 

[1DMF]

Hi yelwocm,

Thanks for the heads up, I am aware of the 'dirty' nature of NOLOCK and that if it becomes a problem I could use READPAST if I don't want to include dirty reads, but for the purpose of this query it's not a real issue, plus our systems work in real-time, in the sense no query uses transactions so there are no processes awaiting a COMMIT.

Regards,
1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads