Microsoft SQL Server Insight Monitoring
Gain insight into your Microsoft SQL Server by monitoring the slow queries, top sessions, locks, waits, and expensive queries in real time.
Site24x7's server monitoring integration allows you to set thresholds for different attributes and receive alerts whenever those values exceed the defined limits. By leveraging Site24x7's unique and insightful console, gain a centralized view of your Microsoft SQL server's health and ensure the smooth operation of your Microsoft SQL Server environment.
Table of contents
- Setup
Prerequisites
Adding a Microsoft SQL Server Insight monitor
Uninstall agent - Supported platforms and versions
- Performance metrics
- Dashboard
- Threshold configuration
- IT automation
- Security
FAQs - Licensing
Setup
Prerequisites
- Ensure AppLogs is enabled. To enable, go to Admin > AppLogs > Settings > Collect application logs from servers using AppLogs > Yes.
- Site24x7 Windows agent version should be 20.6.0 or above.
- .NET version should be 3.0 or above.
- SQL version should be 2008 or above.
The Site24x7 agent needs a few privileges to collect the metrics. You need to provide the Microsoft SQL user with the following limited privileges.
Create a read-only login to connect to your server and grant the required permissions:
Example:
- Open the SQL Management Studio, then right-click the instance to be monitored and select Properties > Security. Under Server Authentication, check SQL Server and Windows Authentication Mode, and click OK.
Adding a Microsoft SQL Server insight monitor
- Log in to Site24x7 and go to Server > Microsoft SQL Server (+).
- Click Download Site24x7 Windows Server Agent. Copy the device key and paste it in the installation wizard when prompted.
- Once the installation is complete, open the system tray and right-click the Site24x7 tray icon and select App Monitoring Config. Ensure SQL Insight is selected in the Application drop-down, and then select the SQL instance from the left pane.
- Fill in the required credentials to connect to the instance, then click Test connection and Save to complete the configuration. Thus, the SQL instance is configured for monitoring using Site24x7.
To view the monitor, go to Server > Server Monitor > Microsoft SQL Server > select Insights under the SQL instance.
Uninstall agent
- To uninstall the agent in Windows, go to Start > Settings > Apps > Apps & features.
- Select the Site24x7 Windows agent to remove, and then select More > Uninstall.
You can also use these steps to uninstall using the command line.
Supported platforms and versions
- Windows OS platform
- Site24x7 Windows agent version 20.6.0 and above
- .NET version 3.0 and above
- SQL version 2008 and above
Performance metrics
Find the extensive list of essential metrics that can be monitored using our advanced Microsoft SQL Server Insight monitor.
Dashboard
Insight dashboard:
Upon successfully adding the Microsoft SQL Server Insight monitor, you will be automatically directed to the Insight dashboard. Gain valuable insights into high-CPU-consuming queries, slow queries, and input-output (I/O)-intensive queries across all your SQL instances through a comprehensive and unique dashboard.
This Insight dashboard provides a consolidated view of these query metrics, enabling you to easily identify and address any performance issues or disruptions that may arise. By monitoring and analyzing these key query characteristics, you can proactively optimize your SQL environment and ensure efficient performance.
Highlights:
- Obtain a comprehensive view of all Microsoft SQL server queries at a glance.
- Proactively prevent potential performance degradation by analyzing your instances with the highest CPU and memory utilization.
Threshold configuration
After adding the Microsoft SQL monitor to your Site24x7 account, you can add a Threshold and Availability profile to set thresholds and receive alerts when there is a breach.
| Thresholds | Description |
|---|---|
| Sessions | |
| Preconnect Sessions | Get notified with a Trouble, Critical, or Down status when preconnect sessions cross the defined value. |
| Number of Sessions | Get notified with a Trouble, Critical, or Down status when the total number of sessions crosses the configured threshold. |
| Running Sessions | Get notified with a Trouble, Critical, or Down status when the number of running sessions exceeds the configured threshold. |
| Dormant Sessions | Get notified with a Trouble, Critical, or Down status when the total number of dormant sessions crosses the configured value. |
| Sleeping Sessions | Get notified when the total number of sleeping sessions crosses the configured threshold. |
| Blocked Sessions | Get notified when the number of blocked sessions exceeds the configured value. |
| Active Users | Get notified with a Trouble, Critical, or Down status when the number of active users exceeds the defined limit. |
| Locks | |
| Locks | Get notified when the number of locks crosses the configured threshold. |
| Wait | Get notified when the number of waits crosses the configured threshold. |
| Convert | Get notified when the number of converts crosses the configured threshold. |
| Low Priority Wait | Get notified when the number of low priority waits crosses the configured value. |
| Low Priority Convert | Get notified when the number of low priority converts crosses the defined threshold. |
| Abort Blockers | Get notified when the number of abort blocker locks at that moment exceeds the configured threshold. |
| Granted | Get notified when the locks obtained differ from the expected value. |
| Query Parameters | |
| Maximum CPU Time | Get notified when the maximum CPU time consumed during a single execution exceeds the configured threshold. |
| Maximum Execution Time | Get notified when the maximum elapsed time for completed executions crosses the configured threshold. |
| Average CPU Time | Get notified when the average amount of CPU time that was consumed by the executions since it was compiled crosses the configured threshold. |
| Average Execution Time | Get notified when the average elapsed time for the completed executions crosses the configured threshold. |
| Average Logical IO | Get notified when the average I/O of the total number of logical reads and writes performed by the executions crosses the configured threshold. |
| Average Blocked Time | Get notified when the average of the difference between the total elapsed time and the total worker time crosses the defined threshold. |
| Maximum Rows | Get notified when the maximum number of rows ever returned by the query during one execution crosses the configured value. |
| Average Rows | Get notified when the average number of rows returned by the query exceeds the configured threshold. |
IT automation
Site24x7 provides exclusive IT automation templates that enable you to automate remedial actions when there is an outage or threshold breach.
Security
The Site24x7 server monitoring agent never stores your data in the Site24x7 app servers. The username and password are encrypted in the agent for security reasons.
Check out some FAQs on the security aspects of our Microsoft SQL server monitoring:
FAQs:
1. Does Site24x7 need write permission to access databases?
No.
2. What performance data is collected by Site24x7 for monitoring Microsoft SQL servers?
Site24x7 collects data for the performance metrics listed in the document for monitoring Microsoft SQL servers.
3. How do you collect performance metrics for Microsoft SQL monitoring?
Performance metrics are collected by connecting to the Microsoft SQL server with the user credentials given by the user, which has limited read-only access.
4. How do you connect with the Microsoft SQL server?
Site24x7 connects with the Microsoft SQL server with the user credentials given in the terminal console, and collects data.
5. Do you keep the Microsoft SQL connection open?
No. Site24x7 creates a connection to the Microsoft SQL server for collecting performance metrics. Once the data collection is complete, the connection created will be closed.
6. Do you store the Microsoft SQL user password directly?
No. Site24x7 encrypts the password given. The username and password are encrypted in the agent for security reasons. They will not be saved or stored in the Site24x7 app servers.
Licensing
Each Microsoft SQL Server insight consumes one advanced monitor.