Case Study: SQL Diagnostic Manager for SQL Server

Ancoris Extends SQL Server monitoring with Microsoft Operations Manager 2005™ using Idera SQL Diagnostic Manager™


Ancoris is a specialist integrator of systems and security management solutions. Our experience with MOM is unparalleled in the UK.

Ancoris consultants have over 5 years experience in working with Microsoft Operations Manager and its predecessor technologies. Ancoris can help organizations understand the benefits of using systems management technologies to get the best level of service from their IT environments. Ancoris also offers the widest range of extensions to MOM 2005.

Ancoris customers include many of the UK’s largest corporate and public sector organizations. Ancoris also partners with leading Microsoft Certified Partners to deliver successful MOM implementations.

Extend SQL Server monitoring with Microsoft Operations Manager 2005™ using Idera SQL Diagnostic Manager™


Microsoft Operations Manager (MOM) 2005 and Idera’s Microsoft Operations Manager (SQLDM) are key products for managing SQL Server production operations.

Together, the products shorten the time to correct application performance problems by quickly identifying the issue, notifying an expert, then providing the tools needed to diagnose and fix the problem in a timely manner.

Ancoris services integrate MOM 2005 and SQLDM together to enhance the production operations of SQL Server.


MOM helps increase availability and performance by reducing the time between when a problem occurs and when an expert is told about it. In other words, MOM tells you about a problem before your phone starts ringing. MOM can also take automated response actions, where the solution to a problem is obvious. For example, if a key service stops, MOM can restart it.


SQLDM helps increase availability and performance by providing an expert with the tools and information they need to quickly diagnose and correct a problem. This reduces the time between when and expert learns about a problem and when it is solved.

SQLDM organizes and explains information about SQL Server internals, so that an expert SQL Server DBA or a new-to-database Windows administrator can quickly identify and fix database problems. In addition to rich information content, SQLDM also provides functionality for enabling selective recovery tasks. SQLDM also provides historical data for performance and capacity planning.

Ancoris integration of MOM 2005 and Idera SQL Diagnostic Manager


The following examples demonstrate how MOM and SQLDM can be used together.

Example 1: The MOM processor utilization role alerts you that SQL Server is using too much of the processor on a box. You select the affected server in MOM 2005 and start SQLDM using a MOM 2005 task.

This enables you to look at the list of user processes. The process that is using the most CPU is easily identified by the bold value in the CPU delta column. You click the process to see its real-time trace and process details and are able to identify this as a fairly complicated Microsoft Access report query being run by a user during production hours. You click the Kill Process button, but (as sometimes happens) SQL Server refuses to immediately kill the process, so you click the Kill User Connection button to force the transaction to end immediately.

Example 2: You are using the MOM SQL Agent event log rule to watch for SQL Agent event log rule to watch for SQL Agent jobs that are failing, using the event log notification feature of SQL Agent. You receive and alert, and look at the summary list of Agent jobs in SQLDM to learn precisely which job step failed and what the error message is.

In order to further diagnose the problem, you examine the job history using SQLDM – has this job ever worked on this server? Since this is a standard maintenance job, you then look across all servers at once with SQLDM to see if it has ever run or failed on any of your other servers. Armed with this information, you decide to try to run the job immediately and start it from SQLDM, then watch the live trace in the process screen to determine precisely where it fails so the problem is identified.

Example 3: An application user is complaining that sometimes an application is very slow to respond, taking over 20 seconds. They’re not quite clear about when, and there are not any MOM alerts that correspond well with the timeframes they are mentioning. You bring up the SQLDM console and look at the Performance screen. You filter using the application name and the specific user account. Looking through the worst performing single SQL statements, batches, and stored procedures/triggers, you note that a test department auditing trigger was inadvertently applied to the production database and that it takes 25 seconds to execute every time a specific transaction is executed. You remove the trigger in SQL Server Enterprise Manager and ask the user to try it now. The user is delighted when the database responds within a second.

Download This Case Study Start a FREE Trial of SQL Diagnostic Manager
Share This
Contact IDERA:
+1 (713) 523-4433