IDERA SQL Diagnostic Manager Overview

AUDIO RECORDING IN FIREFLIES: https://app.fireflies.ai/view/videoplayback-3-mp3::GyCGAGv388

ORIGINAL YOUTUBE VIDEO: https://www.youtube.com/watch?v=0_5-q8EsGV4

Overview of SQL Diagnostic Manager

SQL diagnostic manager is a powerful performance monitoring, alerting, and diagnostic solution to proactively notify administrators of health, performance, or availability problems within the SQL server environment via central console, web based dashboard or mobile device. It minimizes server downtime by providing agent lists real-time monitoring and customizable alerting for fast diagnosis and remediation. 

Architecture of SQL Diagnostic Manager

SQL Diagnostic Manager supports all SQL server versions from 2000 and above collection is centralized in angel lists. Nothing is installed on your server. You can adjust your collection cycle as low as one minute. Old data collected is stored in a customizable historical repository that allows you to view from the console what occurred in the past or run historical reports. 

SQL DM Thick Console

We provide you with three main consoles to view the collected data. The first one is a thick console, a dot-net consulting’s toll on your computer or laptop that allows you to configure, manage, view real time and historical performance data and create reports.

SQL DM Web Console

The second one is the web console. It is a web browser based console, allowing you to see the performance of your instances. 

SQL DM Mobile

The third one is a SQL DM mobile in mobile friendly web interface for your smartphone or tablet devices. You can organize instances in views, a logical grouping of instances by default, you will have views for all servers, critical warning, informational in any server in maintenance mode. This allows you to focus on a particular sector of your environment or instances with a certain status. 

SQL Server Instance

The quick at a glance box gives you quick information on your instances, however, your mouse to see top alerts, see quick information of the different areas and see resource usage for your SQL instance, Tags or attributes you apply to your instances. You can assign multiple attributes to one instance, make it easier to find instances or do certain activities in bulk. For example, if I want to upgrade my virtual environment over the weekend, I can right-click the tack hold virtual and set it to maintenance mode at a specific time. 

Sections in SQL Diagnostic Manager

SQL diagnostic manager is also divided in the following sections, overview sessions, queries, resources, databases, services, logs, and analyze. 

View Server Alerts

You can see alerts from many areas within SQL diagnostic manager, quick at a glance where you can hover your mouse and see detailed information SQL DMT day, where you can see the active alerts that you have for the day And the alert history, where you can filter by metric date severity and finding any alerts that occurred in the past. 

Active alerts

Let’s take a look at an active alert on their SQL DM today. I see that I have a blocking condition going on right now, clicking on that alert takes me to the sessions area where I can see the blocking condition from here. I can get additional information from the blocking report. Blocking report is a snapshot in time of the blocking condition with additional detailed information like the host, the user, the application, the database, and the last commander was issued. Showing blocking conditions in real time is great. What if that condition already occurred in the past?

Historical alerts

With SQL diagnostic manager, I can use the history browser to navigate back to where the specific condition occurs. Here I have selected a blocking condition for a specific server over the weekend when I was not in the office, I can right click and show the historical view. It takes me to that specific time and date. Here I can go forward or backward for each collection period. In my case is every minute. 

View Deadlocks

Deadlocks are also shown in a report format. Although deadlocks resolve themselves in SQL server, we can still get a report on which session got terminated. We can export that information into an XDL file that can be easily shared with someone else so they can open any management studio. 

Analyze SQL Queries

In SQL diagnostic manager, we can see a list of the top worst performing queries in SQL server. First, you want to define what is considered a bad performing query. In my case, I’m doing it by duration in anything that is above five seconds. You can easily filter the quarries you want to see either by time application, user, or any other filtering information here. I can sort any column for easy identification of the problematic query. There are two main views, signature mode and statement mode.

Signature Mode

Signature mode shows all active quarters group by the raw signature of this statement. You can see here that this type of statement ran X number of times. This helps me determine how often is this query hitting my SQL server. If it’s causing any degradation with the resources on my SQL server. 

Statement Mode

The statement mode allows you to see each individual statement exactly as they were collected by the query monitoring trace query waste dots are showing two different ways over time. By duration over time, you can see the time of the day associated with the weights and their total. Wait time by duration shows you the biggest offender by total wait time, allowing you to drill down to the statement, the application, the database, the client, the sessions, and the users. 

I can start my drill down from applications to see which weights are being generated from a specific application. In SQL Diagnostic manager, alerts can be enabled or disabled as you wish. Thresholds are set by default, but can be easily adjusted. 

Customize Performance Baselines

Another important concept is the use of baselines. Baseline defines their normal use of that metric for a specific period of time, you can custom my baselines to define your peak periods or business hours. Baselines will show on this chart to guide you with the optimization of your thresholds, or you can simply use the recommendations that we give you based on your normal use. Baseline is also used to alert based on a percentage above or below the current baseline. This will make the alert more dynamic as the baseline will fluctuate over time.

Responding to Server Alerts

There are several ways you can execute a response to an alert. An email is one. This will allow you to pass variables to your own customized template. Based on certain conditions like days of the week tax specific alert status, you can generate an email. You can also run an executable or batch file, or if you wish you can run an agent job by loading the job and even specifying a specific step within that job, you could also run a SQL script to automate our corrective action.

In this example, if my transaction log is full, I can automatically run this script, passing several variables and truncating my transaction log. There is also a tempDB to see if there’s any contention within the 10 DB and the associated sessions using the TempDb. 

On their table on indexes, you can see the size dependencies index and the statistics of a table. You can also see the fragmentation of that table and even issue a rebuild or update statistics, right out of the console. 

Get Expert Recommendations

In the analyst area recommendations are given based on your selection of areas you like to analyze in your SQL server areas, such as blocking process, deadlocks resources, database, and server configuration index inquiry optimization to name a few. You are presented with a finding list of all the expert recommendations that we have found on the server. You can select each finding to see more detailed information on why we issued the recommendation details about this recommendation.

In some of them, they include links to external articles to learn more about their condition. Also in some cases you have a SQL script that you can run directly from the console to optimize the condition. There are over 30 reports that come out of the box with SQL diagnostic manager. You can run these reports on demand, schedule them to be sent on an email, deploy them to SSRS, or you can actually export them to Acrobat or Excel. 

Additional Resources

For more information, please visit idera.com where you can download a full trial of SQL Diagnostic Manager for 14 days in 15 instances. Thank you.