Monitor with AppInsight for SQL
Identify performance and troubleshoot issues for SQL databases and queries with AppInsight for SQL. This feature provides consolidated resource views for over 100 performance metrics across all SQL servers monitored by SAM. These metrics include read and write latency, index fragmentation, expensive queries (based on CPU time), SQL agent job status with logs, capacity, and resource consumption for CPU, memory, and drive space. Through one page, monitor resource consumption, respond to alerts, and monitor expensive queries without digging through multiple pages and views.
Investigate issues and performance trends without hunting through numerous views into the SQL servers in your environment. AppInsight for SQL provides a level of detail and expert knowledge far beyond what a SQL template can provide, allowing you to monitor virtually every aspect of your SQL instances and databases. The feature polls and reports metrics without use of agents, directly accessing the SQL server using configured access permissions and credentials using SNMP and WMI.
To add a node and configure AppInsight for SQL, see the following:
- AppInsight for SQL requirements and permissions: to configure required settings for monitoring. Use the credentials when adding AppInsight for SQL to a server.
- Add AppInsight for SQL to a node: to monitor SQL servers through the Discovery wizard or manually.
- Monitor a SQL named instance with AppInsight for SQL: to monitor a named SQL server using the server's Virtual IP (VIP).
- Monitor clusters with AppInsight for SQL: to monitor an SQL cluster (used for high availability).
For hardware-related tips, see SQL Server configuration best practices for Orion Platform products.
When applied to a Microsoft SQL instance, the AppInsight for SQL page replaces the default Node Details page. It monitors as an application using multiple component monitors the AppInsight for SQL template. To modify the template or use other templates, see Edit the AppInsight for SQL template.
The following views display SQL data:
- The SQL Server Application Summary view displays all monitored SQL applications, applications with problems, Hardware Health data, and more. To access this page, click My Dashboards > Applications > SQL Server. Click a node to expand the tree and click AppInsight for SQL.
- The AppInsight for SQL view displays application details, databases, application connections, alerts, and more. in the All Applications widget provides all monitored SQL server nodes. Click My Dashboards > Applications > SAM Summary. In the All Applications widget, expand AppInsight for SQL and select a node to open this view.
The SQL Server Application Summary view provides widgets you can use to monitor overall data for all SQL servers including alerts, events, and consumed resources, including:
- All Applications view of all currently managed SQL servers as nodes, expandable to locate specific nodes
- Active Application Alerts for specific alerts affecting SQL servers
- Top Processes by CPU Load, Physical Memory, Virtual Memory and more to gauge applications consuming resources
- Top Monitored Processes by I/O Total Operations, Reads, and Writers for highest bandwidth consumption, reads, and write latency
Further expanding the All Applications on the SQL Server Application Summary displays a quick view into performance counters and status. Each counter and metric can be expanded to review data.
To view all information for an application node, select the application from the view to open the details page for the AppInsight for SQL node. For example, MSSQLSERVER on lab-dem-sql-02.demo.lab in the Orion demo encountered a critical alert. To troubleshoot, access the SQL server through the All Applications widget.
This view consolidates all specific data for the selected SQL server:
- Summary of data including an AppStack view for troubleshooting, alerts, events, expensive queries, capacity usage, and other metrics
- Queries to filter expensive queries
- Transactions of WPM transaction monitors for that installed module
- DB performance for DPA IM data for that installed module
To review database data in the AppInsight for SQL Details view, locate and select a database in the All Databases widget. The Database Details view lists all databases on the node, alerts, AppStack, and more.
To see performance counter details, in the AppInsight for SQL Details view, locate, and select a performance counter in any widget.
Did you know that when SAM alerts you about an application failure, you can use SolarWinds IPAM to quickly determine if a broken or missing DNS record is at fault? You can then use SAM to troubleshoot the service further.