Analysing a SQL server

See if I need a new one or just some bad code

[bateman_ap]

I recently got notice from my host that my SQL server was frequently dropping off their monitoring. It goes down for a few seconds and then returns. They think that I should get a new server and split the SQL machine and the web server.

My sites have been getting larger and there is obviously a bigger load being put on it, however before I go to considerable expense of a new server (it is hosted with one of the big guys and and is a rather bigger proposition than some of the £60 servers I see these days) I wanted to somehow check the load both the webserver and the SQL server is putting on the machine and if possible what SQL querys are running at a particular time to make sure all the ASP code is closing everything properly etc.

Anyone got any tips on what to do?

[mattglet]

See if your host will give you some usage logs (bandwidth monitors, etc.) on your webserver. That's all I can think of off the top of my head for a shared server.

As for checking your database code efficiency, look into SQL Profiler's Trace capabilities:
[developer.com...]

[Easy_Coder]

I wouldn't rule out something else causing that issue too... like a nic card starting to flake out or a switch acting up. Maybe their lan just can't handle the chatter.

[bateman_ap]

Its Rackspace so I really don't think it is their LAN at fault! Suddenly today realised why I pay them, one of my other dedicated servers at a "cheap" host has been down for 5 hours and their tech support really doesn't appear like they really care. Not something u can ever say about RS!

Thanks for the Profiler link, will check it out, have Remote Desktop to the server so can do pretty much what I want.

[Easy_Coder]

So when you drop off the monitoring radar does your site also lose conneciton to the database?

[bateman_ap]

Yup, I have seen it a few times. It is like the page stalls for a few seconds and then starts to laod, like there is a overload on the server and it is just "cleaning up"

[Easy_Coder]

Really...

Couple of questions...
How are you generally making your db connections?
What type of ado cursors are you using?
Are you passing large recordsets or arrays down the wire back to the web server?

I've seen something similar with 2 physical machines where the entire site was db driven and making a general conneciton via dsn inside the site template and closing the connection inside the closing template. However, just before closing the connection the last bit of code wrote a simple user traffic footprint into an archive. Once the footprint archive table hit 1M rows that application started to just stall and eventually lose connection.

It got really bad when a crawler came along... the connections couldn't get back to the pool fast enough so the application was just opening more and more. Once the connection count hit 50 then boom... no more connectivity. The only recovery was bouncing iis or the box.

To allevaite the issue I ended up running a daily package to move the footprint data to another repository.