What's the best method to monitor SQL health?

We have the occasions when Windows Updates cause SQL servers to freeze-up and not reboot but hang at a blue screen (not a BSOD).

During this hang (or stall) the server can be reached with ICMP (so its not actually off the network) and the SQLSERVR.EXE process continues to run but we can't transact SQL calls.

So, my question is this, what can be monitored with APM to catch this condition?

I have a few ideas using WMI with Win32 SQL objects but I'd like to hear from others before I re-invent the wheel.

Thanks for any replies.

Find more posts tagged with

sql_server

windows_update

win32_sql_objects

hang

WMI

Accepted answers

ShamChauthani

APM has SQL User Experience monitors that are ideal for this sort of monitoring. Using these monitors you can create a SQL query that runs against your server on a scheduled basis and returns results as stats and response times. You could then set threshold and alerts on this monitor to flag this issue.

HTH

All comments

ShamChauthani

HTH

lchance

Thanks for the tip.

Yes - I looked this over and it should be exactly what I need to do some transactions. This should save me a great deal of time. Thanks again!

bchamberlain

We are running NPM 9.1 SP3 and I don't see the "SQL User Experience Monitor" template. I see "thwack.com user experience monitor", "SQL Server SNMP", and "SQL Server 2005 Database APM 2.0" templates. Do I need to upgrade to a newer version?

mark_wiggans

You'll need the APM module.