Hey guys!
In this post I will comment on an alerting and monitoring system for SQL Server and Azure Managed Instance, developed by the company PowerTuning, which aims to provide various metrics, analyzes and information to quickly identify the current state of the bank (Health Check), send alerts by email (including graphs) when an event occurs in the database and monitor changes in the bank's behavior , table growth, data files, and more.
I've used other monitoring and alerting systems like Redgate SQL Monitor and SolarWinds SQL Database Monitoring, but PowerAlerts surprised me with the amount of alerts available, the level of detail and the differentiated support that PowerTuning delivers.
The CEO of PowerTuning and one of the creators of PowerAlerts, Fabrício Lima, said that “Anyone who has SQL Server and doesn't use PowerAlerts is crazy”. After testing the product a lot, I have to agree with it lol
If you are interested in Power Alerts, please contact the Power Tuning commercial team: [email protected]
1) What is Power Alerts?
It is a 62,000 lines of T-SQL code tool (and growing) created from SQL Server DBA to SQL Server DBA with dozens of Alerts to monitor your database and, now, with the feature of graphs to facilitate its visualization and understanding of the environment.
Power Alerts is a complement to any monitoring tool because it already returns detailed information with graphics in the email itself (without you having to look at a monitoring screen or log in to the environment). Anyone who has a SQL Server will be happy to have these alerts and logs that are generated for the DBA.
In this live, you can see more details about Power Alerts:
There is nothing like it today here in Brazil (or abroad as far as I know).
Dash with customers who have already received Power Alerts:
2) Alerts created
These are the Alerts that are created in the Power Alerts Deployment:
Plus 1: A monthly email is also created with information about the instance to be used in the event of a disaster as documentation to find out how the instance was before the environment.
This helps to reinstall the environment without impact of changes (helps a lot in disaster cases).
Plus 2: Creation of a daily environment checklist with graphics to send instance information on a daily basis and help analyze and compare information at different time periods.
This can be used to have a quick analysis of the environment or to compare how the values are between two different dates.
3) Example of a FULL Log Alert
This is the alert header which has a lot of useful information:
As we can see below, the e-mail itself already shows the reason for the log being stuck (in this case it is a lack of log backup – Column “Log Reuse Wait” with the value “LOG_BACKUP”).
It also shows the disk that this log is and its free space to make you more calm or worried when this alert arrives.
We still have how much this log will grow and if it is limited (to see if it is growing a lot or a little it helps a lot).
The first information that the DBA would have to log in and discover in an alert like this is already chewed into the alert. Productivity and savings in DBA hours ($).
Following the email, we have a graph to facilitate your visualization of when the log started to grow and the time it took to burst.
Last 60 minutes to see recent log behavior:
Last 48 hours to get a longer view of log behavior:
Still following in the email, we have a graph to facilitate the visualization of how the consumption of the disk where this log file is located. Whether it's running out fast or slow. No log alerts out there on the internet back this information.
As one of the common problems can be the lack of backup, the alert also shows when the last base log backup was with Log FULL:
One less piece of information that the DBA would have to check (more productivity).
In addition, the queries that have an open transaction and are consuming the log are shown so that the DBA can analyze and go after the responsible person to know if he can kill or not:
The DBA would have to find in his scripts which query shows who is consuming more logs to later run and find out. In Power Alerts, the insert that I placed to fill the Log using 3.8 GB of log has already appeared.
What is running at that moment is still shown with a picture of whoisactive:
We can again see the insert filling the log.
Finally, we can still see the graph of alerts by day and by hours, helping us to identify how the history of this alert is.
If I have many alerts per day coming in, we have to try to do something to reduce it:
We were also able to identify if there is a time of day that generates the most alerts to help find the culprit routine:
At the end of the email there is the logo and our alert registration info:
This is a product licensed by Power Tuning at the INPI (National Institute of Industrial Property), under number 512022002100-5, and cannot be shared or used without a purchase agreement or prior authorization.
4) Created DBA routines
In addition to beautiful graphics, our alert system also creates more than 25 routines that store data in tables and help the DBA to monitor and act more efficiently in the environment. These routines are used by Power Alerts to send e-mails and graphics.
DBA routines that are created in the environment (+25 routines):
- Routine to monitor Deadlocks.
- Routine to monitor script errors that occur in databases.
- Routine to monitor the largest waits in the environment in a given time interval.
- Routine to monitor whether statistics are up to date.
- Routine to monitor the queries that are using tempdb.
- Routine to monitor tempdb data file growth.
- Routine to monitor the growth of tables and databases. With this, we can predict the growth of the environment and make new investments only when necessary.
- Routine to monitor SQL Server counters.
- Routine to monitor the most time-consuming queries in the environment.
- Routine to monitor the memory usage of the environment.
- Routine to monitor Login failures.
- Routine to monitor what is running in the environment every one minute (whoisactive).
- Routine to monitor the growth of the Log of Databases.
- Routine to monitor job history.
- Routine to monitor the use of environment indexes.
- Routine to monitor index fragmentation.
- Routine to monitor whether Identity columns are overflowing.
- Routine to monitor the pending I/O queue in SQL Server.
- Routine to monitor response time and usage of SQL Server data files and logs.
- Routine to monitor SQL Server Error Log.
- Routine to monitor disk space.
- Routine to monitor CPU usage.
- Routine to monitor the queries that are using more CPU.
- Routine to monitor the execution of CheckDB in the databases.
- Routine to monitor the history of backups.
- If you have a cluster, routine to monitor the status of the cluster nodes.
- If you have Mirror, routine to monitor the status and performance of the mirror.
- If you have AlwaysOn AG, routine to monitor AG status and performance.
- Routine to delete old data from all created logs.
5) Power Monitor
With all this information stored for Power Alerts, our BI team created a Dash in Power BI to help monitor our customers' bank:
The customer needs to meet some prerequisites to use the Power monitor, such as:
-
- Power BI account
- Data gateway that has access to the trace base
- The installation of apps not listed in the appsource must be enabled
6) Power Reports
In addition to greatly improving the core of the alerts and including sensational graphics, some reporting procedures (Power Report) were also created to help analyze the environment on a day-to-day basis.
Just pass your e-mail and the start and end date parameters to get the desired information (when applicable).
Real example of using Power Reports:
We want to see how the environment performed between 18:00 on 11/27 and 18:00 on 11/28.
Instead of the DBA entering the environment, looking for several queries and manually validating the information of what happened in that period, there is a procedure that he passes the e-mail, the start date and the end date and several graphs like the ones below will be sent to his email.
With less than 5 min, he already has a lot of information available for analysis and visually.
EXEC stpPowerReport_SQL_Performance @Ds_Email=‘[email protected]’,@Dt_Start=‘20221127 18:32’,@Dt_End=‘20221127 18:32’,@Ds_Query = NULL
The result of executing this procedure generates all the next graphs.
Graph with CPU information:
Graph with PLE memory counter information:
Graphic with connection information in SQL Server:
Graph with the number of queries in the table with the Whoisactive Log:
Graph with pending I/O counter information:
Graph with available physical memory counter information:
Graph with the number of queries taking more than 3 seconds (and their average):
Graph with tempdb growth information (data):
Chart with information on the most time-consuming queries:
*After this chart comes part of the query code in the email (Id Query). I won't show it here to save space.
Graph with information on the longest jobs:
*After this chart comes the name of the Jobs in the e-mail (Id Job). I won't show it here to save space.
Chart with information of the biggest SQL Server waits:
Here ends the graphics that we managed to send with just an F5 in the procedure: stpPowerReport_SQL_Performance
How long would the DBA take to gather all this information?
Once again, Power Alerts increases productivity and saves DBA hours ($).
Other Power Reports that you can use on a daily basis:
Report with the growth of a database:
EXEC [dbo].[stpPowerReport_Database_Size] @Nm_Database=@Database_Name,@Ds_Email=@email
Report with the growth of a table:
EXEC [dbo].[stpPowerReport_Table_Size] @Nm_Database=Nome_DB, @Nm_Table = Nome_Tabela, @Ds_Email = Seu_Email
Report to monitor the current database index fragmentation. ERP companies ask for this a lot when they open a slowdown call.
EXEC [dbo].[stpPowerReport_Index_Fragmentation] @Ds_Email = Seu_Email
Report to validate how the statistics are in the environment. In Power Tuning's opinion, up-to-date statistics are much more important than defragmented indexes. This job has to be a priority.
EXEC [dbo].[stpPowerReport_Update_Statistics] @Ds_Email = Seu_Email
7) Feedback from customers
Below are some prints of customer feedback that PowerTuning collected throughout our deployments:
8) Investment Power Alerts
Power Alerts is licensed per instance with the below values:
- Licenses for Instances 1 to 4: USD 49.00 per month per instance.
- Licenses for Instances 5 to 9: USD 37.00 per month per instance.
- Licenses for Instances 10 to 19: USD 25.00 per month per instance.
- Over 20 instances, direct negotiations via email: [email protected]
Monthly Price for 1 instance = USD 49.00
Discounts:
Annual Plan with 20% discount
Annual plan value for 1 instance = USD 464.00 (USD 38.66 per month)
- 3 Year Plan with 40% discount
3-year plan value for 1 instance = USD 1,040.57 (USD 86.71 per month)
Customers who have monthly support with Power Tuning:
- Customers who have a monitoring contract with Power Tuning do not pay anything extra per month for Power Alerts.
- Customers who have a 24×7 DBA contract with Power Tuning have a 50% discount.
There is also a one-time cost per instance (Total value USD 281.00) for installation and configuration work to the reality of the environment and initial monitoring. This single amount does not have a discount because these are DBA hours that will always have to be worked.
This is a product that will continue to evolve over time and the customer will be entitled to all improvements.
What is not included in this proposal:
- Performances of technical improvements in the environment after the implementation of Alerts:
- Full disk
- Log Full
- Base without Backup
- Slow environment
- Other common day-to-day DBA issues
- Implementation of heavy administration routines:
- Index maintenance routines, statistics update and CheckDB will not be implemented in the environment.
![SQL Server – The activated proc ‘[dbo].[sp_syspolicy_events_reader]’ running on queue ‘msdb.dbo.syspolicy_event_queue’](http://dirceuresende.com/wp-content/themes/boxcard/img/thumb-medium.png)
Portuguese 
English