The SQL Server Database Performance Solution
Ignite for SQL Server is the one SQL Server database monitoring, analysis and tuning solution designed specifically to solve and prevent the toughest performance problems. It's not a general purpose SQL Server monitor - server health tools do that. Ignite goes where conventional tools cannot.
In just four clicks, Ignite users drill down to find the most important performance problem in the instance. With easy to understand graphical displays, and automatically emailed graphic reports, Ignite is the one tool that all DBAs, Developers and DBA Managers can use to collaborate. From real-time to historical trends, Ignite specializes in saving DBA and developer time and helping them measurably improve SQL Server performance.
Response Time Analysis
Ignite applies Response Time analysis, measuring the time for query response, rather than focusing only on server health metrics. Ignite measures time associated with each tiny operation - or wait type - and uses it to expose delays in end user service. This is the most important indicator, since it shows applications waiting on SQL Server to respond.
But Ignite does not stop there. It gathers real time session data, server resource levels, and query information of your SQL Server database performance. It also analyzes historical trends of response time over multiple dimensions - such as wait type, program, execution plan. Then Ignite correlates the data to lead the DBA through the most important problems and solutions.
To learn more about how Ignite can pinpoint SQL Server performance issues, click here to view a demo.
New Features for SQL Server Performance Monitoring
Ignite 8.3 for the enterprise is targeted at database professionals who work with large deployments of Ignite. The enhancements in this release are designed for SQL Server professionals faced with distributed environments, enterprise authentication, security and compliance, and proactive monitoring of large numbers of instances around the world.
Ignite Central provides a unified view of both database performance and alert status across all of your database servers managed by Ignite.
Ignite Central works across the datacenter or across the world
The Ignite Central dashboard focuses your attention on top problems by providing a summary of monitored instances in alarm. In addition, you can perform a custom search across all monitored instances, both local and remote, then drill into any instance’s Ignite server for detailed performance analysis with a single click.
Identify the top problem database instances across your entire enterprise
Ignite Central provides a platform that is ideally suited for large scale and/or geographically distributed database deployments, delivering many key benefits including:
- Scalability to tens of thousands of monitored instances. This high level enterprise view lets you see you entire operation from an interconnected Ignite environment.
- Exception-based management focuses on the top problems without the information overload that can mask critical problems.
- Global monitoring around the world and around the clock
- Access to all monitored database instances gives your entire team the knowledge to make informed decisions
Ignite Central’s distributed architecture communicates over HTTPS using a low-overhead protocol ( REST ) to minimize bandwidth. Unlike federated IT architectures, Ignite Central functions without the duplication or synchronization of data, and adds only a negligible amount of network traffic. This offers optimal efficiency, even over WANs. Industry standard SSL encryption can be used to maintain the security for all data passed between Ignite Servers.
Active Directory Authentication
Numerous companies have embraced Windows Server Active Directory to manage domain users and security settings. Ignite 8.3 integrates with Active Directory (2003 and higher) to allow domain users to login and use Ignite. This eliminates the need to maintain separate logins, while ensuring that the password strength and expiration policies specified in Active Directory are fully enforced.
Domain credentials and "single sign on" simplify authentication
Once you have configured Ignite to point at your Active Directory server, you can add groups from your domain into Ignite and assign permissions to them. To add groups, go to Options then the Administration tab User Administration. Here you will find the Add Active Directory Group button. The Search group function shown below makes it easy to find specific groups within the domain.
Quickly configure domain access using Active Directory security groups
To get started using Active Directory in Ignite refer to this Knowledge Base article
Maintaining consistent alerting across multiple database instances can be difficult, even with as few as two monitored instances. An alert group provides a mechanism for simplifying the maintenance of alerts, while ensuring consistent alerting across multiple database instances. To create an alert group just click on the Alert link at the top right of Ignite then the Alert Group tab.
Alert groups standardize alerting across database instances
Once an alert group is created, Ignite runs each of the selected alerts on the entire list of selected database instances, drastically simplifying alert setup and maintenance. Alert groups with a heterogeneous collection of databases (SQL Server, Oracle, Sybase, or DB2) are supported. Ignite applies the selected alerts to only the appropriate databases automatically, allowing a single alert group to be used across multiple databases types.
When setting up a new database for monitoring, Ignite 8.3 reminds you to add alert groups, making sure that no database goes without alerting. This is done using a new page that automatically appears in the database instance registration wizard, prompting you to select the alert groups to use for the newly added database instance.
New Preconfigured Alerts
In 8.3 Confio introduces new additions to its family of preconfigured alerts.
Ignite allows you to easily select from a lists preconfigured alerts
New SQL Server Alerts
- SQL Server Job Failure: This alert will report jobs that have failed since the last time this alert executed.
- SQL Server Error Log Alert: This SQL Server (2005 and up) alert will report error log entries containing a specified string or pair of strings.
- SQL Server Abnormal Mirroring Status: This SQL Server (2005 and up) alert will trigger if the SQL Server mirroring status is in a state other than Synchronized.
- Windows Service Not Running: This SQL Server (2005 and up) alert runs against the SQL Server monitored instance, and triggers if the specified Windows service is not running.
- Log has Many Virtual Logs: This SQL Server (2005 and up) alert will report SQL Server databases that have a high number of Virtual Logs. To limit the result set and reduce Ignite Repository space, set the Minimum number of virtual logs.
- SQL Server Long Running Jobs: This SQL Server (2005 and up) alert detects SQL Server jobs (in SQL Agent) that are running longer than 2 standard deviations from the mean execution time based upon previous executions.
- SQL Server Ineffective Statistics: This SQL Server (2005 and up) alert detects indexes with ineffective statistics. It uses criteria such as time since last stats update, percent of rows changes, and table size. All indexes meeting this criteria are reported each time this alert is run. To reduce repetitive results, it is recommended to configure this alert with an execution interval of at least one day.
New SQL Server, Oracle, and Sybase Alerts
- Total Blocking Wait Time: This alert will report the amount of time that sessions waited due to blocking. Applies to wait time as seen on the Ignite Blocking tab.
In addition you can create custom alerts and custom metrics, adapting Ignite to meet your specific requirements.
Configurable Password Encryption
Ignite 8.3 provides the ability to select a password encryption to match your requirements. In addition to Ignite’s default encryption, you now have the ability to choose from industry standard AES 128-bit or AES 256-bit encryption.
AES is a form of password-based encryption (PBE), utilizing a passphrase to generate the encryption key. One advantage of AES encryption is that it produces a different encryption string every time it encrypts the same value, improving security.
Choose an encryption algorithm to match specific security policies
Ignite Management Options
This new interface exposes many Ignite features that were previously hidden, helping you better leverage the power of Ignite. After detailed analysis, our user experience experts have organized the controls into 3 tabs: Monitor Setup, Administration, and Support.
Management Options provide easy access to all settings
Ignite 8.2 extends Confio's mission to deliver tools that identify, pinpoint, and resolve the biggest issues impacting database performance. With added support for SQL Server 2012 and 169 new wait types, Ignite gives you unparalleled visibility on not only the amount of wait time, but specific server performance details that make resolving these issues easier than ever before.
Utilizing Confio's proprietary response time analysis, Ignite focuses your attention on the most important problems, providing the fastest route to eliminating SQL Server performance bottlenecks.
Ignite 8.2 provides a wealth of targeted information focused on the issues that have the greatest impact on performance. Database instances including SQL Server, as well as Oracle, Sybase and DB2, are all displayed within one web browser based dashboard. When issues arise, the root cause of the problem can be identified in four clicks or less.
In addition to expert determined metrics, Ignite 8.2 contains a new user interface that enables custom metrics to be easily added. This limitless extensibility provides you the ability to monitor any metric accessible from any database instance through an SQL Query.
Execution Plan Display and Advice
Detailed plan analysis has been added in Ignite 8.2, providing a clear view of the exact execution plan used at any given time. This capability is available for all recent SQL Server versions (2005 SP2 and above). Included is the ability to get execution plan reports, focusing your attention on the plans that are having the greatest impact on slow response time.
With Ignite 8.2 we have added query advice based on expert outlined best practices. This advice is based on Ignite analysis of the execution plan, and it identifies potential SQL Server performance pitfalls such as missing indexes, unmatched indexes, plan change detection and full table scan detection. Then, unlike other SQL Server tools, Ignite identifies and quantifies the impact of these problems in terms of application wait time.
Agentless SQL Server Performance Monitoring
Ignite for SQL Server monitors SQL Server databases without installing any software on the production systems. Ignite is built on a three tier architecture, with software installed only in the middle tier. DBAs, developers and managers access the solution from their browser. Ignite is installed on any Windows server, as well as UNIX/Linux servers. The repository database is configured in a SQL Server or Oracle instance, often a test or development system. Ignite's agentless probes reach across the network to the monitored databases - SQL Server as well as other vendors - and pull performance statistics back to the repository for analysis and reporting.
No Load on Production Systems
Using the agentless architecture, Ignite for SQL Server performance places less than 1% load on the monitored servers. For production use in the most sensitive environments, Ignite captures data continuously without impacting the target system. When it is time to upgrade Ignite to access new features, all the change takes place on the Ignite server, with nothing to change on Clients or Monitored servers. For detailed system requirements, see Specs and Install.
Ignite System Requirements
A single Ignite installation can simultaneously monitor multiple SQL Server instances on different types of platforms, as well as Oracle, Sybase, and DB2. There are two choices for download, depending on the type of server that Ignite will be installed on: Windows or UNIX/Linux. For each, a single download and packaged installation sets everything up, so you are running in minutes.
Server Running Ignite Software
- The Ignite software can be installed on almost any Windows, Unix or Linux server, either physical or virtual. It only needs an operating system that can support Java JRE 1.5 or higher. Repository hardware and configuration must meet minimum requirements as shown below.
|# Number of monitored instances||Minimum # of CPU||RAM||Architecture|
|<20||1||1 GB||32 or 64 bit|
|20-50||2||2 GB||32 or 64 bit|
|51-100||4||4 GB||64 bit|
|101-250*||4||8 GB||64 bit|
* For over 250 monitored instance enable Ignite Central.
Ignite Repository Server:
- An Microsoft SQL Server or Oracle instance is utilized by Ignite to collect and process data from monitored servers. A dedicated instance is not required, but it cannot be the "monitored" instance. The data collection server is defined as the Ignite Repository server, and the requirements are listed below.
- Ignite Repository requires either:
- Microsoft SQL Server 2000 SP3 or higher (2000, 2005, 2008, 2012)
- Oracle 8.1.7 or higher (8i, 9i, 10g, 11g)
- "Express" editions of both SQL Server and Oracle can hold a repository but will likely run out of space due to the 4GB limitations.
- Do not put the repository into an instance that will be monitored. Doing so will affect the performance of that instance.
- Installation requires access to a Sysadmin (MSSQL) or DBA (Oracle) user for the install which will hold the repository.
- Repository hardware and configuration must meet minimum requirements as shown below.
|# Number of monitored Instances||Minimum # of CPU||RAM||Architecture|
|<20||1||2 GB||32 or 64 bit|
|20-50||2||4 GB||64 bit|
|51-100||4||8 GB||64 bit|
|101-250||4||16 GB||64 bit|
Note: if Ignite software and repository server are co-located the numbers are additive.
Ignite Repository Storage:
Free Disk Space in Repository Instance: varies with number of monitored database instances and level of activity on each:
|Monitored Database Activity||Repository Disk Space per Monitored Instance|
- Internet Explorer 8 and higher
- Firefox 3.6 and higher
- Chrome (latest stable)
Monitored Database Instances
These requirements apply to monitored database instances, hosted on either physical or virtual servers.
SQL Server Monitored Database Instances:
- Ignite for SQL Server supports monitoring of SQL Server 2000 SP3 and higher ( 2000, 2005, 2008, 2012)
- SYSADMIN privileges are required to setup Ignite
- The server holding the Ignite software must be able to connect to the monitored MSSQL server.
Oracle Monitored Database Instances:
- Ignite for Oracle supports monitoring of Oracle 8.1.7 and higher (8i, 9i, 10g,11g)
- SYS user privileges are required for initial setup
- The server holding the Ignite software must be able to connect to the monitored Oracle server
Sybase ASE Monitored Databases:
- Ignite for Sybase supports monitoring of ASE 188.8.131.52 and higher.
- SA_ROLE privileges are required to setup Ignite
- The server holding the Ignite software must be able to connect to the monitored Sybase server.
DB2 Monitored Databases:
- Ignite for IBM DB2 LUW supports monitoring of DB2 LUW 8.1 FixPack 13 and higher (8.2.2,...)
- SYSADM privileges are required to setup Ignite
- The server holding the Ignite software must be able to connect to the monitored DB2 server.
These requirements apply to monitoring of the vCenter server for use with IgniteVM.
VMware vCenter Servers:
Confio supports connections to ESX/ESXi and/or vCenter. vCenter is not required, but highly recommended.
- VMware ESX/ESXi 4.0 and higher
- VMware vCenter 4.0 and higher
- Ignite VM installs on any Windows, Linux, or UNIX server with Java JRE 1.5 or higher
- One time entry of a read-only VMware password will be required. Password is not visible to IgniteVM users.
Detailed SQL Server Performance Analysis and Monitoring
Ignite is unique in combining the most important analysis technique, response time analysis, with a full set of customizable server resource monitors, advisors, alerts and reports into a tool dedicated to SQL Server application tuning and performance. While many tools cover server health management, Ignite gives both the expert and novice DBAs and development teams a solution to find and fix performance problems that directly impact end users.
Ignite for SQL Server Features for DBAs and Developers
- Cross-Platform Dashboard »
- T-SQL Query Response Time »
- Historical Trend and Drill Down »
- Wait Time Data in Real Time »
- Wait Type Alerts and Reports »
- SQL Server Tuning Advisors »
- Correlated Resources and Response Time »
- 6 Dimensions of Wait Time Drill Down »
The Ignite dashboard includes all the instances and clusters in a multi-vendor environment, grouped however your DBA team needs it. Only Ignite identifies the most serious problems from SQL Server, Oracle, Sybase and DB2 on one screen so that the extended DBA team can all use the same tool. Alarms for real-time response time and critical server resources all appear in one place, 3 clicks from the root cause of any problem.
Only Ignite can show one screen with the three essential facts required to isolate a SQL Server performance problem: which query, which Wait Type bottlenecks, and what is the response time impact on each query. When tuning SQL Server, Ignite gives an uniquely simple view of exactly what is causing the problem.
Ignite excels at showing long term historical performance trends in SQL Server and giving the DBA and developer the ability to drill down to any point of interest. Since Ignite keeps detailed Wait Type statistics in its data warehouse repository, the team can track response of SQL Server queries, execution plans, databases, programs and more, over days, weeks and months. Then a DBA can display a bar chart to show the exact situation - such as changed query, missing index, excessive page I/O - that occur at the exact time of a problem. When an application owner calls, Ignite illustrates the situation at the historical time of interest.
With data as granular as one second, and real time updates, the Ignite for SQL Server current view shows real time SQL Server performance. When a customer opens a trouble ticket, the DBA can respond instantly and see exactly why SQL Server session was locked, why a batch job was hung, or what I/O bottleneck caused the problem. Unlike SQL Server tools that just show server resources, Ignite gives real time insight into tuning specific SQL Server query processing delays.
Ignite is unique in using SQL Server Wait Types to trigger proactive alerts and generate trend reports so that DBAs and developers can use response time as their primary metric. Whether being alerted on query SLA response violations, or
excessive PageIOLatch times, Ignite gives DBAs menus to build customized alerts and reports, and set automatic delivery based on alert priority and report schedules.
SQL Server performance tuning advisors in Ignite are based on thousands of hours of analysis by Confio's senior DBAs in diagnosing problems based on response time analysis. The Ignite engine captures tiny data points in our data warehouse, and the tuning advisors which problems need attention based on direct impact on users. Then clicking on the alarm trail takes DBAs to the root cause in 4 clicks or less.
Conventional SQL Server tools show server and instance resources like CPU, memory, logical and physical I/O. But do they help in finding the source when a problem arises? Ignite for SQL Server puts wait type based response time and server resources on one screen, so the DBA can see how they are tied together. Does a CPU spike correspond to a delay for a specific query? How much are selected Programs being held up by high physical reads? Only Ignite correlates over 20 different resource metrics with response time to make SQL Server tuning faster and more effective.
Built from a base of response time analysis, Ignite for SQL Server gives the most detailed response time analysis of any SQL Server monitoring tool. Users can drill down to focus on response for 6 different SQL Server wait time dimensions in our data warehouse, including query, Wait Type, Program, Database, and User dimensions. Many tools have Wait Type
data, but only Ignite lets you see a chart to answer a question such as "which query had the highest delays when hitting a certain index?".