Monitoring sql server

Question

This question is related to monitoring of SQL Server.

1. I want to send an email alert with all open transactions on SQL Server in HTML report whenever there is sustained 50-60% CPU usage for more than 3 minutes. How to achieve this using TSQL or stored procedure so that I can schedule as a job and keep polling on regular basis.

2. Similar as above, I wanted to get notified whenever there is sustained 70-80% memory usage on the server for more than 3 minutes. The reason for this, even though I have set the max server memory setting for sql server, there could some component eating up memory which can cause external memory pressure. In such situations, DBA team should get an email alert notification even before users calls in and open a ticket.

I have anyone can provide sample code that will be great help. Based on that I can extend the functionality of monitoring like collecting trace etc...

Answer 1

You can use a combination of tools. I'll give you an abridged list of the ones I use.

First, I assume you have database mail configured.

Second, you can trigger a lot of run-of-the-mill alerts using SQL Server Alerts. You can also run a stored procedure using a scheduled job (say every five minutes or some interval like that) and fire the user counters which will trigger SQL Server Alerts. When an alert happens, you can also execute a job in addition to notifying operators. If you know WMI, you can use Alerts to get to PerfMon counters.

Third, you can use SQL Server Extended Events, but they are more complicated and require a good understanding of the database engine and hand coding because there is very little in the way of GUI.

Finally, you can just schedule a stored procedure to run on a regular basis, allow it to collect metrics from the DMVs (database management views) and then send the email directly from the stored procedure.

Specifically for your situation, I might suggest you run sys.dm_os_performance_counters on a regular basis (scheduled job/stored procedure) and cache it in a metadata table, then use that for analysis.

Also, getting CPU usage information in SQL Server is not the easiest thing in the world because CPU stats can change on such a finely-granular timescale. Try this article for a good overview of how it works. Perhaps look at "free pages" to get you 70% RAM figure.