SQL Optimization

Hello. Welcome to this short video where we're going to look at how we can use the SQL optimizer module with Foglight to identify people that are performing queries and rewrite them to improve the performance by generating a more efficient execution plan. This works for SQL Server and Oracle, and we're going to use the SQL Server example in this video. What we're going to do is use the performance investigator module to drill into the SQL instance where we're going to identify a query that we're going to optimize. What I might do here is just look at a certain period of time, like the last day or the last few hours, that will give me a good sample. And I need to be thinking about what sort of query I want to optimize, whether it's something with a lot of I/O-related issues, or in my example, I'm going to look for something with lots of executions. Because we're going to try and tune the SQL, we need to really decide if it's a query that we do want to choose, so that might be by looking at it through a particular database dimension. So if I drill in through a particular database, I can identify the top queries in the last four hours for that database. And what I've done here is I've sorted on the number of executions. So I've identified the queries with the most executions. But as I said, you could sort that by the more style, or the most active time, or the most CP, for example. And here, we can view the SQL text, but this is the bit that I wanted to show, the Tune SQL. It requires you to have already installed the SQL optimizer Windows application, because when I click on this button here, it's going to open this file that it's just downloaded and that would be associated with an installed application, which will be SQL Optimizer. So that's going to open that application for me and allow me to go through the optimization process for this query. So although Foglight's using a web browser, we would need to run this on a Windows desktop to launch this particular module, and then we'll be able to tune the query. We have two options when we tune the query. We can rewrite the actual SQL text which is useful if you have access to the code, if it's in a Stored Procedure Review, or some or the script that you're running. But if that's not possible, you might want to use the plan control method, where we can use plan control to generate a better execution plan without rewriting the code. I'm going to go for the top one. Now, the application has been launched using my Windows credentials that I'm actually using to run this session. That might not be the correct type of connection to use to this particular database. So just have a think about how you're connecting, and there's a connect button at the top right-hand corner where you can reconnect using a different type of credential. And we also need to select the correct database that we're going to connect into, which is why I use the dimension at the beginning to know that I need to connect into the BMF database. The first thing that will pop up is what sort of symptoms we see for this particular issue. So if it's a long-running query or it takes a long time to execute, you can put your symptoms in there. And then once we click Start, I should get an error popping up because these tables don't exist in the default database that's set, so I'm just going to connect into the correct database. And then we can either optimize just the query or we can optimize for indexes and the query. I'm just going to do the query. The symptoms box pops up again, and then what's going to happen now is the query text is going to be analyzed. And we're going to look at what we could possibly do to this query text-- either reformatting it slightly, changing the syntax using some force options-- just to basically try and generate a more efficient execution plan which would still give me the same results set. Then we're going to execute these. So just bear in mind that if this is an update, delete, or insert statement, that will be rolled back per test. But we need to consider things like the caching side of running these queries. So, by default, I'm just running on my queries twice. But again, it will depend on what sort of query you're executing, about how often you want to run it. We will then see in green where we've managed to see some improvements. This process of generating all these alternatives-- it's currently generated 114 alternatives, a lot of which have the same plan, and they will they won't be shown to us. They will just get excluded. But if we're testing these, and they're taking 10 minutes to run, then the overall duration of this test could be quite a long time because each one will be tested. But you can change things in the settings where you could just say, show me if any of these will run in less than two minutes. But each iteration would then take two minutes to test. But as we can see, we've got a better alternative at the moment, alternative 7. But we'll just leave that running through. But what I could do is just step through some of these alternatives so that you can see what we're doing. That's got a force order, and that will have a different execution plan. And then we've got alternative 2 and then alternative 3. Then eventually, if we get to the current fastest one, which is alternative seven, we can then see that particular query with the rewrites