Video : How to Optimize SQL Queries

Transcript

00:08

SQL Query Tuner

This is a brief overview of ideas. SQL Query Tuner this product is here to help DBAs and developers automatically generate tuning recommendations to tune complex queries. It’s here to profile your SQL environment and it’s also here to generate test workloads that you can run against your SQL environment. To use SQL query tuner, you just need to connect to a Microsoft SQL Server. In my example, I’ve connected to my own VM here. Once you’re connected, you’ll be able to see all the different database objects like the different databases that are in your SQL Server. There’s also a project area where you can look at save results of other operations from SQL Query Tuner. The three main operations that you can use in SQL Query Tuner are a) you can run a profile session, b) you can generate a new SQL workload or a workload to be placed on a SQL Server, and then c) you can also run a tuning job to improve the speed of your queries.

01:13

Profile Session

Just for housekeeping, a lot of things I’m going to show you today have already been run and I’m doing that specifically so that you can see the end result of the different profile, tuning and workload operations. To make the best use of SQL Query Tuner, I would usually start off by generating a profile session. To start that operation, you just click on the little icon up here at the top and what will happen is the profile session tab will pop up on your screen and it’s going to be empty at first, but we are going to start generating a view into what type of activity is occurring on that SQL instance. You can see the different weights and workloads that are on your SQL instance. Here is a result of a profiling session I’ve run in the past. You can see it was running for 30 minutes about you can see the weight states that are tied to lock, CPU, IO, buffer memory and other one of the things I wanted to highlight is that when you’re profiling with SQL query tuner.

02:15

We’re not using extended events or Trace. We’re simply querying the DMVs and pulling back the appropriate information so that you can see the different workloads. If you want to highlight a specific area, all you have to do is drag and drop your cursor over that area and the results at the bottom will change according to what you have highlighted. Of course you can also slide that around that period of time around to focus in on certain areas.

Data Filtering

If you’re looking for a peak or a valley in the SQL workload, also note that there’s a filter. If you would like to filter SQL Query Tuner by application or by command or by database, you can do so right up here. Specifically, let’s say you are a developer and you’re trying to tune a specific application or working on a specific database, or if you’re looking for queries that are coming from a specific host box, you can filter on those results.

03:09

Now when you filter in or when you are looking at the queries, they’re going to be shown down here at the bottom by SQL statement, by event or by session, you can select each individual statement. From here you can also either have the plan explained or you can run a tuning job. I’m going to run a tuning job because that’s really where you’re going to want to go when you’re using this tool.

SQL Tuning

To do that select tune, a new tab will open up with the statement that you wanted to work on. Here. What you’ll want to do is decide the number of executions. Again you just hit this play button and SQL Query Tuner will run in the background and look at different rewrites and possible query hints that can be used. It’ll generate cases to rewrite that statement to make it run more effectively.

03:57

This process can take a few minutes. I’m just going to skip two results that have already been run. I’m going to go to this other tuning job. This is what you see on a finished tuning job. You’ll see the original statement and in this case, you can see that it’s running at 00:50 8 seconds. It’s current rewrite down here though, you can see that we can actually change the statement and get it to run down to point 19, or we can try any of these other different rewrites that are available. Lastly, after you run a tuning job, if you want to verify that the information you’re seeing actually shows that the query has been rewritten and is effective, one of the things you can do is generate what we call a SQL workload.

SQL Workload

Now there’s different ways of generating that SQL workload. You can go over here to these top icons and select New SQL load.

04:54

When you do that, you can copy and paste a query into this result window and decide how many times you like to execute it against the SQL instance. Or you can go back to the original tuning job that you ran and select something that you think is reasonable. Right-click on it, say compare to parent and it’ll show you the original query as opposed to the rewrite or the example query that we are suggesting with SQL Query Tuner. From here we can copy queries out of this window and put them in here. We can put them in a new SQL workload window. I’ve already done this. So this is a side note. I would suggest if I was a developer, I might use the original code and run it in its own job. In this case, you can see that I’m running with one parallel session, 500 executions.

05:41

I would then run a profiling session at that same time and then test out the rewrite and put the rewrite in with the same parameters. In that way you can use SQL Query Tuner to not only help identify long-running queries, but to also tune those queries and then prove that those queries are running more effectively.

Conclusion

Thanks for watching this brief overview of SQL query tuner. If you’d like to trial the product, it’s available at www.dairy.com. You can download and trial the product free for two weeks.