SQL Diagnostic Manager for SQL Server 관련 정보

24x7 SQL 성능 모니터링, 경고 및 진단.

SQL Diagnostic Manager (SQLdm) provides diagnostic information on the health, performance, and status of SQL Server instances across your environment. You can view, diagnose, and report on critical performance statistics from a central point of control. SQL Diagnostic Manager finds and fixes database performance problems for SQL Server in physical, virtual, and cloud environments - including managed cloud databases.

SQL Diagnostic Manager provides 24x7 SQL performance monitoring, alerting and diagnostics

  • Performance monitoring for physical and virtual databases
  • Query monitoring to see the causes of blocks and deadlocks
  • History browsing to find and troubleshoot past issues
  • Predictive alerting with settings to avoid false alerts
  • Capacity Planning to minimize server sprawl
  • NewSCOM management pack for integration with System Center

Physical and Virtual Performance Monitoring
Easily monitor and view the performance of both physical and VMware server environments to get a complete view of SQL Server databases. View performance metrics and alerts for VMs and their related ESX hosts including CPU, memory, disk usage, network, etc. to get a complete performance picture of the SQL Server environment.

Query Performance Monitoring
Identify and monitor inefficient and poor performing SQL queries, batches, statements and stored procedures causing blocks and deadlocks, such as long or most frequent queries or queries consuming unusually high CPU or I/O. The thresholds can be configured on each individual server allowing for greater flexibility. The interface makes it very easy to see the most costly queries by looking at individual query executions (by statement) or by looking at query signatures which roll-up and summarize multiple executions.

History Browsing
Drill down to the precise point-in-time an error occurred to view historical SQL Server and OS metrics and better understand the multiple factors which could have contributed to an error.

Predictive Alerting
Avoid false alarms with adaptive alerting functionality and set alert thresholds based on built-in historical statistical analysis of your server performance data.
The system has embedded heuristic analysis of event trends and behaviors and presents a percentage of likelihood that various events are likely to happen later in the day, because there has been a trend in the behavior in the past. This can help the database administrators to be better prepared to diagnose, drill down, and resolve the issue quickly and effectively in case of a problem.

Capacity Planning
Understand trends and view forecasts of your SQL Server environment to better plan for future needs and get a handle on SQL Server sprawl with database growth reporting.

Mobile Access
Access SQL diagnostic manager from anywhere. Use any mobile device, such as iPhone, Android, Blackberry or Tablet to view real-time and historical SQL Server performance data. Plus, view or kill sessions, start/stop jobs and run queries to resolve or diagnose issues remotely while on-the-go.

NEW Integration with SCOM
The SQL Diagnostic Manager Management Pack provides easy integration with Microsoft’s System Center Operations Manager (SCOM). The Management Pack propagates the current status, health and events of all monitored instances up to Operations Manager, allowing the DBA and IT Operations staff to share data at a higher level across their enterprise. The management pack is easy to install and does not incur any additional monitoring overhead.

Experimental Support for SQL Server 2014
SQL Diagnostic Manager has been tested to work with pre-release copies of SQL Server 2014.

Performance Monitoring

NEW - Availability Group Monitoring - Manage topology of high availability groups in SQL Server2012. Monitor the performance of Always-On availability groups and get alerts to ensure the SQL Server is running on the designated node in the event of failover to a secondary replica. SQL Diagnostic Manager can run on an availability group as well.

Continuous Monitoring and Analysis - Continuous monitoring and real-time analysis of SQL Server and OS health and performance metrics across hundreds of SQL Servers enterprise-wide, including clustered configurations.

Physical and Virtual Monitoring - Easily monitor and view the performance of both physical and VMware server environments to get a complete view of your SQL Server databases. View performance metrics and alerts for VMs and their related ESX hosts including CPU, memory, disk usage, network throughput and more.

Tempdb Monitoring - Quickly identify and resolve tempdb contention and performance related issues with the tempdb monitoring and corresponding diagnostic views. Monitor and display TempDB space and performance related information including:

  • Tempdb file space monitoring
  • Tempdb version store monitoring
  • Monitoring of session space usage of tempdb
  • Monitoring of waits related to tempdb

Custom Monitoring - Add an unlimited amount of custom counters in the monitoring process. These include any perfmon counters or any SQL queries (which can enable advanced application specific monitoring) that can be added to the list of already existing industry specified counters that SQL diagnostic manager will monitor. This information is also stored in the repository so a custom report can be created and stored to capture this data.

Query and Wait Stat Monitoring - Powerful interface for viewing overall waits on servers and queries. Users can easily monitor the resources the SQL server and the queries are waiting on and can also drill down to look at the wait they are experiencing. This level of detail shows which database, application, users, session, etc. is having the highest waits.

Job Management & Monitoring - Job success, failure, cancelled or retry events can be monitored and alerted. The Jobs view displays all jobs, their last known status, complete job history and allows users to start/stop jobs.

Monitor Replication & Mirrored Environments - Easily monitor performance problems with your mirrored or replication environments. The system also provides alerts and real-time diagnostic views to help with troubleshooting in these environments.

Monitor OS Metrics - Users have the ability to collect OS performance metrics using WMI or OLE automation

Alerting

Adaptive and Automated Alerting - Powerful and sophisticated alerting engine offers over 100 pre-defined and configurable alert settings. These alert settings are based on industry best practices and have advanced configuration settings allowing for greater flexibility. Adding or modifying metrics to multiple servers is also simplified with the default alert configuration and the 'copy to' operation which allows for multi-server configuration. Advanced notifications allow alerts to be sent to different groups of people based on metric, time-of-day, instance, and more.

Alert Thresholds Recommendations - Capture the baselines of past performance of a monitored instance to determine if any alert thresholds are set in such a way that they may be excessively noisy or cause false positive. If it is determined that a metric is constantly alerting, it will provide flags to indicate that changes may be needed and will provide recommendations for the new thresholds.

Alert Smoothing & Snoozing - Easily "snooze" any alert or group of alerts for a specified number of minutes which prevents alerts from recurring while the problem is being worked on. For problem metrics which occasionally spike for very short durations, users can easily specify the length of time that an issue must be occurring before the first alert will be sent. This cuts back on noise and allows the database administrators to focus on issues which are representing a continuing problem worth further investigation. The changes to the settings can be done at an individual server level or in bulk for a group of servers.

Per-database & Per-disk Alerts - Users can define per-database and per-disk alerts differently. For instance, users can be alerted when disk E: is 90% full or disk F: is 80% full. Users can also now get alerts on such items as OS Disk Full, OS Database Full, and OS Log Full shown for specific sizes in GBs or MBs as well as a percentage of the total size of the database.

Error & Agent Logs - View and receive alerts on specific conditions within the SQL Server Error Log.

Settings for Maintenance Mode - Disable data collection and alerting during maintenance periods to avoid false positives. Maintenance Mode can be on-demand, one-time or weekly scheduled maintenance periods, and can be enabled via PowerShell to allow integration with outside job scheduling. This mode can also be enabled in bulk for all servers in one shot.

Diagnostics & Analytics

Pinpoint Locks, Blocks & Deadlocks - Identify real-time and historical session locks, blocks and deadlocks and look at the complete blocking chain for easy identification and resolution. Session traces can also be run for additional diagnostics or sessions can be killed. Blocking data includes the ability to chart blocking events, investigate lead-blockers, view the last-command run by the session, etc. while deadlocking monitoring allows the users to drill-down to view the deadlock victim and the code responsible for the deadlock.

History Browser - All session, lock, block and performance counter information is available historically. Diagnosing a problem that happened minutes, days or weeks ago is simple by selecting a past point-in-time and the console quickly changes all screens to display the SQL Server and the OS performance metrics at that point in time.

Performance Baselines - View server baseline performance and compare a server's baseline values and compare a server's baseline over time ranges as well as across different servers. Plus generate reports that show the baseline in the given performance metric's graph or chart, allowing for better capacity planning for new servers.

Server Activity Timeline - Visualize server performance events as a timeline calendar instead of only as a list of events. Events are color-coded for easy differentiation, and a sliding scale provides the ability to zoom in to a specific timeframe. This helps correlate events happening at the same time in an easy to read format for quicker trend analysis and remediation.

Fragmentation Statistics - Fragmentation is one of the most common causes of performance degradation in SQL Server. Statistics on fragmentation are displayed to help you identify indexes that may need attention.

PowerShell Snap-in - Quickly and easily script many of the SQL diagnostic manager management tasks using the PowerShell Snap-in.

Enterprise Management

Customizable & Scalable Console - Server Dashboard provides 'single pane of glass' monitoring via an intuitive, easy to use central console. Some of the key areas highlighted in the console GUI include:

  • Servers
    • Server Dashboard displays eight key areas. Color-coding highlights the area triggering alerts to your specific thresholds.
    • Overview, Session, Queries, Wait Stats, Disk Counters, Databases, Services, Logs
  • Newsfeed Collaboration
  • Reports
  • Administration

Hundreds of servers can be monitored from this single console; multiple consoles can support thousands of databases providing an unprecedented level of visibility and control of your entire SQL Server environment.

Central Data Repository - Store all historical performance data in a central repository enabling analysis of long-term trends, supporting capacity planning and allowing for a variety of strategic analyses.

Application Security - Flexible customization of server access and security permissions allow DBAs to have full access to one or more servers while other users such as Help Desk Operators are provided read-only access.

Agent-less & Low Impact - Simple and secure data collection is accomplished through utilization of a background service. The system is agent-less and does not install additional services, databases, tables, extended stored procedures or anything else on the production systems. This simple architecture greatly reduces server footprint, simplifies the installation and upgrade process and eliminates risk agents on the performance of the monitored SQL servers.

Audit Transaction Logs - Audit a configuration change (e.g. kill a session) that shows up in the change log as an event. For the application security functionality of SQLdm whereby other users (outside of Administrators) have access to the database. Administrators can keep track of what these users are doing and the changes are captured in a new report on configuration changes to provide an audit trail if needed.

Powerful & Comprehensive Reporting - Comprehensive reporting and trend analysis which highlight key performance trends via the easy to use reporting interface. The reports and charts cover areas such as:

  • CPU to I/O Activity
  • Physical Disk Activity
  • Worst Performing by Application or User
  • Logins (including unsuccessful logins)
  • Procedure Cache Hit ratio

The system also provides a series of forecasting reports that allow users to forecast database, drive and table growth based on past performance.

In addition to the over 25 reports that are provided out of the box, users have the ability to create/store a custom report based on any collected metric to identify recurring problems and prevent future occurrences. All reports can also be deployed to SQL Server Reporting Services and emails scheduled easily through the management console.

Dashboard Templates - Custom dashboards through which users can add columns/rows, select different metrics from the panel gallery, and share the dashboard among users to see the performance metrics most relevant for that user for any given database.
Users can also specify different default dashboard per instances. For instance, SQL Servers that are virtualized can be defined to have a dashboard that displays VM and Host data in the dashboard.

IntelliFeed™ Technology - Idera Newsfeed allows users to follow critical issues and share updates on SQL Server problems with other technical staff using a social-networking paradigm.

Knowledge Base - A knowledge sharing tool provided in the UI, gives the ability to add custom environment-specific details as a reference point to reduce problem resolution time, increase availability, and increase internal awareness.