Transcript

Welcome to IDERA’s virtual education for SQL Doctor. SQL Doctor is a tool that can help you with not only identifying problems and misconfigurations on a SQL instance but also providing recommendations and options for fixing those different problems. SQL Doctor analyzes everything from the potential misconfigurations on the SQL instance to the workload. Anything going against production best practices. Query waits, indexes. Including the analysis of missing indexes, redundant indexes. It also analyzes queries that are running during the analysis. And anything that could be improved upon within the query. When using the product the first thing you want to do is add a SQL instance to be managed. You can select the button here at the top to select Add Server. Plug in the server name. Define whether it is tied to production or OLTP [online transaction processing]. There are specific recommendations that are displayed. If you check these boxes for example for a production instance. If we see that a domain controller exists on the SQL Server then you are going to see some red flags for that. If we see multiple instances that are on a production server then again we would throw up some considerations around that as well. You can also specify down at the bottom whether to use Windows or SQL authentication. Some of the data that is captured is pulled in from WMI [Windows Management Instrumentation]. And so you can also specify the advanced connection settings to either use […] automation through SQL authentication. Or specify other account credentials for WMI access as well. Both credential SQL credentials, as well as the Windows credentials, would require SQL sysadmin [system administrator] with respect to SQL and local admin with respect to the WMI data. The analysis will run on a scheduled basis if you want it to. There is a scheduling feature. Or you can kick off the analysis on your own by selecting the analyze over at the top of the screen. Selecting the categories of what you want to analyze across that instance. You can also define some additional filters. So you are very carefully able to narrow down what she analyzed to a particular application or database. This is also where you can adjust the amount of time that the analysis will run. Just be careful because if you set the time to run for 30 minutes then you can expect that it is going to run its analysis completely for that 30 minutes. So if you are looking for quick results then I would set it to a shorter period of time. The result looks like this. We have a report that is generated. We see all kinds of different recommendations. With heat mapping. If there are any problems that were discovered during that analysis. It will provide you the details of the findings. The objects are relevant to whatever the problem was. And any recommendations. In this case, we see that it is recommending that we kill a process that lasted for quite some time. To learn a little bit more about whatever the recommendation is we can click on the Learning link at the bottom of the screen which will bring up a third-party article. In this case, TechNet which is talking about how to minimize blocking and SQL Server. So after we review this information then we can go back and make the determination of whether or not we want to accept those recommendations. At the top there is a Show Me the Problem which will highlight within the command you know the problem the query that is causing us this problem. If we want we can also take that command, copy it, and do a quick query diagnosis on that particular command. Selecting the database from the list. And it will bring up yet a separate report that will display anything that it discovered as a potential problem. In this case, it found the use of SELECT * query hint abuse. Again showing the problem. we can highlight in that command in yellow the line that needs to be rewritten and in blue the particular text that would be throwing up the red flags. So going back to the previous report. There are all kinds of different recommendations. Some of them again are tied to indexes. Some of them are tied specifically to configurations. You have wait statistics analysis. For example, if it identifies CX packet weights are heavy on this system then you will see recommendations for max degree of parallelism. But you can go to the different categories here to hone in on what you truly need to review with respect to improving performance. If you need to focus solely on index optimization there is a category for that. Here we see missing indexes on one of my databases and tables. And sit down at the bottom. It provides us the different schools of thought for when this is a problem, when it would not be a problem. At the top, we also have the optimization script that we can run directly to fix this problem. All of this, by the way, can be exported out if we need to share this information with other audiences or other engineering teams or even perhaps a third-party application vendor. So that is how you can run an analysis through SQL Doctor. There are all kinds of great recommendations to compliment that. You can also go to the query plan stats at the bottom to see all the queries that have executed and the statistics around the count, the reads, the writes. Also with within those queries, we can see all kinds of recommendations at the bottom of the screen which we select. And as we highlight them it will jump to the particular command which again we can click on the recommendation. And it will bring up the details of the recommendation and highlight in the command where the problem resides. This can be a great tool for consultants to go on-site and do a quick analysis to provide some food for thought for where we need to focus our attention. It can also be great for just DBAs [database administrators] on a daily basis to run on a daily basis, weekly basis, or monthly basis. Just to get an idea. And even with hot spots and problematic performance activity throughout the day. What we should do perhaps to improve on that performance. IDERA SQL Doctor can be downloaded from the IDERA website www.idera.com. Feel free to reach out to us to ask any questions about the product. You can also go to our community forum at community.idera.com. There is a lot of different users out there that are ready to answer your questions as well. So I feel free to download the product. And if you have questions feel free to reach out to us. And other than that have a great day.