Welcome to this brief introduction to SQL Diagnostic Manager’s prescriptive analysis. SQL Diagnostic Manager has been around for many years. And supports monitoring a Microsoft SQL Server. The way we distinguish it from typical monitoring tools is that it is more than just a symptom-based monitoring tool in the sense that it can give you deep-dive diagnostics. And even extend further than that to provide you recommendations. And it is those recommendations that we are going to pretty much focus on today with prescriptive analysis. So when you open up a SQL Diagnostic Manager console this is what you will see. And you have your navigation similar to Outlook on the left-hand side. But also a tabular navigation at the very top. The prescriptive analysis features are going to be located under this Analyze tab section all the way to the right. And on the upper tabular navigation. So let us go ahead. And click that. And this will take us to a view of some of the prescriptive analysis reports that are available from past executions of the analysis. So prescriptive analysis is something that can be run manually. You can either click the little button here. And kick it off by selecting your categories of what you wanted to analyze. You can also schedule when it runs by going into the properties of the instance by selecting the instance on the left-hand side. Choose the properties option. And the properties actually shows various properties like polling intervals. For SQL Diagnostic Manager security options for the collection of data how you are going to capture OS [operating system] metrics. Also configuring what you are going to do around the monitoring of drives. But at the very bottom of the list is this analysis configuration. And from here you can also set up the scheduling of when the prescriptive analysis will run. There is also the option to set up alert actions where certain thresholds would trigger the analysis as well. So by going to the Tools menu, and alert actions and responses. I can go into one of my rules. And one of the action options that can be configured is this enables the prescriptive analyzer to analyze the server. And again you can select the categories of what you want it to analyze. And that would be based on certain criteria certain conditions being met for certain alert conditions before it actually triggers that. So whether you run it manually whether you schedule it to run or you set it as an alert action it is going to analyze whatever categories you select. Everything from the server configuration, database configuration, resource utilization issues, index analysis. And even query improvements or queries that are running poorly that could use some improvements potentially. Based on what it analyzes could be wrong with a query. Once the analysis is complete you will have the reports listed in this view. And you can just pick it from the list. I will just go ahead and click the most recent execution. We will go ahead and open that up. And it will bring up a list of all of the findings when it runs. One of the options for the analysis is how long you want it to run. And you can specify five minutes one minute 30 minutes. And also apply additional application or database filters. But it will run for that period of time. And it will finish whatever it is working on at the time that [you know]. If you have selected five minutes. Once it gets to five minutes it will still complete whatever it is working on. So you might find that it runs for six minutes instead of five minutes for example as it wraps up whatever it is working on. But you will get this report displaying everything that it discovered as a potential issue while it did its analysis. And some of this information is going to be more matter-of-fact. Like databases have not had an integrity check performed in X number of days. You will see the display of the findings and any recommendations. And like I said that one is kind of more matter of a fact. But then you have other performance-related like index. It shows this index series experiencing a high level of page latch contention. So it shows the finding and the recommendation. And any of these you might even find some learning. So teach you more about that. So it is not just us or you no idea telling you that this is a potential problem. It will also be third party expert advice backing that up with even more learning information. Here we have a finding for duplicate index discovered. And again it will tell you when it is a problem. Why would it not potentially be a problem? The recommendations learning links with whatever you select. In this report, you also have these options up here at the top that will light up like an optimization script. This one is a pretty basic one being a duplicate index. And as you can see the script that it provides as a script to actually drop the duplicate index. And so you could copy that out to share with the rest of your team. Or click the Run button here to execute it right from within the window. If you want to you can export out the entire report findings to have your entire team review these findings. And go through the list together to review which findings you want to take advantage of versus those that may not necessarily be as critical for your environment. And there is other types of analysis. Like I said the query tuning type recommendations that you might find that are more aligned with really what is going on under the hood with different queries. For example, here we have a search predicate contains the column expression in the function. We can just kind of go down the list here. There are all kinds of different findings. Here is a cursor that could be optimized for better performance. And again it tells you at the bottom what it discovered is a problem and the recommendation. And even a learning link to teach you about why that is a critical issue. And again we look at these buttons at the top that light up for whatever we select. And can select the show problem which will pop to the forefront. The actual query that is running this is actually the SSIS [SQL Server Integration Services] database for Integration Services. And it is showing me where I need to consider rewriting that query that executed on that against that database. So again you will have findings for server configuration, resource utilization, database configuration, disaster recovery configuration, queries, indexes. And as you work through these there is a little flag option over here on the left-hand side that you can click on to mark basically that you have already reviewed that item even taking into account what the optimization script or problem is. And then now it is been marked off has been thoroughly reviewed. And perhaps even deployed as a potential fix. And any of these findings could then be exported out as well. And share with the rest of your team. So to wrap it up here prescriptive analysis is a great add-on as far as all the functionality that you get with SQL Diagnostic Manager to give you additional recommendations to actually fix issues. And as I pointed out at the beginning I find that that is something that distinguishes this product from typical monitoring tools that provide [you know] essentially no recommendations or any information to help you actually fix the problem. This kind of leaves you driving blind when it comes to performance problems around your SQL environment. With that, if you want to download SQL Diagnostic Manager it is available at When you get to the website there is a product drop-down list that you can go to. And look for SQL Diagnostic Manager. That is the name of the product. Once you download the product it is a simple five to ten-minute install. And [you know] after you install the product that it actually has the license key already embedded. So you can hit the ground running with checking out the features. But I would certainly [you know] after this video encourage you to check out the prescriptive analysis features around your environments. And get some good recommendations about how potentially to improve your performance. With that, we will wrap up this video. I appreciate you watching this video. And look for future videos to come in to help you out with additional functionality that comes within SQL Diagnostic Manager and other IDERA tools that we offer again available on our website. Thank you.

How To Run Prescriptive Analysis with SQL Diagnostic Manager for SQL Server

With SQL Diagnostic Manager, tune the performance, security, and disaster recovery of SQL Servers by quickly and easily running prescriptive analysis.

Start a FREE Trial
Share This
Contact IDERA:

Join Our Global Community

Join our email list and receive the latest case studies, event updates, product news, and much more.