Loading the player...

Transcript

Expand

Hello and welcome to this short video on the SQL Query Tuner. The SQL Query Tuner is a SQL Server only product. And it is designed to do basically three things. It will let you profile your database. It will let you tune a SQL query. And it will let you run a load test. The load test end of itself does not produce any results. And it is best used in conjunction with the profiler quits which we will see shortly. I am going to go ahead and start a profiling session. Now on this box labeled 114. And to do that I will simply click this icon at the toolbar called New Profiling Session. As the profiling starts to run we will see any activity that is going on in our database. However, in this case, since this is a database simply running in a VM (virtual machine) on my local environment. I am going to run a load test to simulate a load on the system. I will do that by choosing File, New SQL Load. And this will open up a dialog where I can paste a SQL query in it. I am going to go over to my Project Explorer. And I am going to choose this SS [SQL Server] query - 3 SQL. And I am going to select all of this. And I will copy it. And now I am going to paste it into my SQL load test. The load test will run for an elapsed time of either hours and minutes or the number of executions. Or you can also press this icon down here in the lower right-hand corner. When the load test is running this will be read. I am going to run this load test for (we will run it for) 3 minutes. And I am going to click Start. Once I have started the load test. We will start to see content come into the chart window. And we will see the load that that query is placing on our system. You can see that our load test has run. We can obviously see where the load test started and stopped. And now once it is over we are not seeing any more content come through in the profiling session. So I am going to go ahead and just stop this profiling session. And we are going to switch over to a profiling session that was run earlier. You can save the profiling sessions out is dot OR files. And we can refer back to those in the future if we wish to do so. Here is a profiling session where I ran a series of load tests. You can see here there is four discrete sections in this profiling session. And I wanted to find out which one of these queries. These are all different queries. I wanted to find out which one was going to be giving the best performance. So the best improvement based on a tuning session. So we can isolate any of these segments simply by holding the left mouse button down and dragging it across. And now the results we see here are only for the SQL that was occurring during that slice. So here if we take a look at this query. And if you click on it you can open that up in a window. Below we have a in our From clause for client transaction. We have another part of the query where it says with index client transaction [… ]. Ok, well. That was the result of this load test. What we see here. So here is another option. The same query but it is written slightly different. You notice that in the From clause that With Index is no longer present. And we see that there is been a big decrease in the amount of CPU [central processing unit] that is being used. So if we scroll down. It also has an option force order hint. At the end of the query. If we take a look at our third option here. And we expand this. We can see that that force option hint is no longer here. So that was the results we got from that. Well, I want to go back to the actual query that we started with. And that is right here. So this query was actually run in this time slice. The original query here. But if we take a look at this. We notice that there is a little warning sign here. And if we hover the mouse over that it says Cartesian product that returns all the rows in all the tables listed in the query. Well, that sounds bad in terms of query performance. So what I am going to do is just click on this. And it is going to ask me if I want to add some join criteria. So I would like to do that. So I double click on that. And we see that it modified our query to add some more joint information. Now this query was run in this time slice for our load test. And if I hover over that or select that slice. And then come in here. We can see here where we had the additional joint information added. Okay. So that are those are a couple of ways that you can use the profiler in conjunction with a load test. The load or the profiler is going to give you all the information about your instance. So it is not initially database specific. However, there is a filter up here. So I can filter by application, command, database, host name, […] domain, and username net address or username. Okay. So if I wanted to just filter out information that I was getting from my load test which is being run by SQL Query Tuner. I can filter by application. And then I have the list of applications that are running that will appear in this drop-down. If you select the apply filter button or the refresh button. It will take out everything except whatever you have selected in this filter. Now in this case as I said earlier the only thing that is running on this because is the load test. Because it is a date that is running in a VM [virtual machine] in my environment. So this does not have an obvious effect. Just in running this. We saw a couple little bumps here. The change went up when I went back to the full view. But keep in mind you can also select a slice in time if you want. If you have a see a big spike in your database performance. And of course, you can isolate it by actual database schema name as well. So if we were just profiling our database. Let us say that we started with this query. And your worst performing query is always going to be at the top. So if we wanted to start a tuning session based on this query. We can take a look at this. If we select it. And I right-click in here. I can get an explain plan. Or I can choose to tune it. If we look at the explain plan. First I will give us a little bit more real estate here. We can see things in here that kind of run up some red flags. We have got some table scans. An index scan here, table scan here. There is some nested loops with the table scan at the bottom. And we can see some of these things that are that have a high planned cost. Okay. So you can probably be pretty sure that if you are seeing table scans that that might be a good candidate for a query that needs to be tuned. And by tuning we are going to get a number of different options. So to start a tuning session. I can simply select Tune. And it will open up another tab here. And it will initially most of the time it is going to initially process this query a bit. Now this one has already been tuned. So it already has that information. But you want to make sure that generate cases is selected. Perform detail analysis and execution iterations are all selected. And then you can click the Run button. And it will start tuning your query. I am going to close this tab. Because we have already got a tuning session that we have run. And let me just pull one up here. So this is our original query that shows up. And you can see I have those check boxes selected. And it takes a few minutes to run. So we are not going to wait during the video to watch this tuning job run. However, what I am looking for is a situation where I have green bars on both sides ideally. Now you will have to select these chevrons here to expand this. It will not automatically expand that for you. But my original query is here. And if I right-click on that. Well, let me do it this way. I am going to choose force order as my tuned query. Okay. That is one of the cases that we looked at up here. so if I select that and then I right-click. And I choose comparative parent. Then we are going to get a side-by-side comparison of these two queries. Now here is the one with the index primary key use. And here is the one with the force order. Okay. That was an improvement. Because based on these results we had a significant improvement in the cost compared to the parent. And the elapsed time was half a second or so faster. Okay. And that is the kind of the result that we see here. So here was our original query that had the index. This was the one that used the force order. Okay. And you can see that our CPU [central processing unit] time went to almost nothing for this. So if I highlight the entire slice here. We can see that there is just a couple little bumps here. They hardly even show up with respect to my CPU [central processing unit]. The SQL tab also shows us some more information, the number of executions, average elapsed time, events, and sessions. And then any procedures that might be being used. Okay. But that in of itself is worth looking into some more. So we ran another one of the options. And came up with this where there really was not a significant difference between the original query and this query. But then if you take into consideration that warning sign here. We chose to implement that. And that is when we got a significant improvement in the overall cost in CPU [central processing unit] utilization for that query. So those are just a few of the ways that you can use the query tuning tool in conjunction with the load test. To verify results of a tuning job we can also profile our database. Obviously, if there is activity going on in your database you are going to see the worst performing SQL here at the top. Okay. If we take a look at our tuning job. There is also some more information here under the analysis tab that is worth looking at. So I am going to click on analysis. This is our original query here. And you can see that we have this index in use here. And I want to go through the index analysis. So if I select. And these are color-coded. If I select a gray-colored index. The explanation for that is here in the right-hand panel. This index has no effect on statement execution. And those are what that is what. The gray means the blue this index is defined on a column present in the predicate. So it could be used by the database optimizer when you run the statement. In this case however, it was not. There is also green ones says this index is used by the optimizer when you run this statement. And then finally we have three orange colored indexes here. And if I select one of these. It gives us an explanation. It says table broker is scanned via a full table scan. But it has a filter where broker last name is equal to Smith on it. So we suggest implementing this index. Now those are the table scans that we saw in the explain plan. So you have the option also to improve this query. If you do not want to change the code. Or maybe you cannot. Maybe it is a third party application. But you do have control of your database. So you have control over indexes. I can select these three indexes. And right click. And choose create indexes. And now it is going to give me the DDL [data definition language] to create those indexes. You still have to be cognizant as a DBA [database administrator] whether creating these indexes may cause more overhead than they are worth. And that is only a decision that only you can make in regard to your environment and how often these queries might be run. I could also open these if I do not want to create them on the fly. I can open those in an SQL editor and save them out. If you follow whatever your process you normally have for implementing changes to a database especially in a production system. But we do give you the option to execute these as long as you are connected to that database and have those permissions. So this was our original query. But we also have available to us the information and index analysis for all the cases that were generated. So you can see here. There is quite a long list. So let us just take a look at force order for example. That was one of the options. And when you select this. And you can click refresh. It is going to go back out and reconnect to that database and re-parse those column statistics. So this will take a minute to run. And now we see. We have some results now. In this case it you might expect that it is recommending the same three indexes. There could be options in some of these different cases that were generated where an index main creating new indexes may not be required. But you are going to have to go through the most promising of those to see if that is what it is. And obviously you know we have a large number here. You are not going to go through all of those. Your overview information should be a guide as to which one of these options are worth looking into. Okay. Because that index creation is going to have some effect on the results that were given here. Those indexes are created dynamically to test the execution and cost of those queries. so once those X. Once the query case has finished running, then those indexes are dropped that were created dynamically. So they were just there and gone. So that we could get some better information about how we can improve performance. We can see information up here. There was 486 cases generated. And we have it shows us information here that is also color coded in relation to our index analysis. so that wraps up this demo of SQL Query Tuner. If you have any questions, please do not hesitate to contact IDERA at idera.com. Thank you.

An Overview of IDERA SQL Query Tuner

SQL Query Tuner is an automated SQL query optimization tool that maximizes database and application performance by quickly discovering, diagnosing, and optimizing poor-performing SQL queries. It empowers database administrators and database developers to eliminate performance bottlenecks by graphically profiling critical metrics inside the database, relating resource utilization to specific queries, and helping to tune problematic SQL queries visually.

Start a FREE Trial of SQL Diagnostic Manager

(SQL Query Tuner is an add-on to SQL Diagnostic Manager.)

Share This
Contact IDERA:
+1 (713) 523-4433