Skip to main content
Database Administrators

Return to Answer

added 15 characters in body
Source Link
marc_s
marc_s
  • 9k
  • 6
  • 45
  • 52

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/sprocstored 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.

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/sproc) 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.

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.

Source Link
efesar
efesar
  • 1.1k
  • 1
  • 6
  • 12

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/sproc) 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.