Foglight Query Insights with Easy Buttons

Hello, everybody. This is Gordon Cornelius with Quest Software. Today I have one of our solutions consultants Ben Boyce with me today. Ben's been working with our Foglight solution for the better part of 10 years. And Ben, I had a customer today ask me, hey, Gordon, one of the things that our biggest concern is, is the quality of code running through our environment. How can we optimize this code? How can we get more with the resources that we have? And what immediately came to mind is a new feature we just released in Foglight 6.3. And I was wondering if you could walk me through it real quick. Absolutely, yeah. And the other thing, too, side of that Gordon is we're getting more people from other parts of the organization, whether they're devops team or developers and things. And they're being tasked with, well, what am I creating, specifically SQL syntax, that could be having an adverse reaction, if you will, in the enterprise itself, right? And that's really where Query Insights comes into play. What Query Insights is looking at is across the entire enterprise which queries are having the greatest impact when compared to the other activity that's happening at the instance layer? So because Foglight does offer such a broad coverage of various database platforms, one of the first things I may need to do is to filter to the domain either that I'm responsible for or perhaps that may be most critical to my enterprise. But the reality is maybe we haven't gone that far into the process, right? We don't really necessarily care which domain it's participating in. We just need to know how it's impacting the environment. So when we start to look at these types of questions and these investigations, Foglight is a time-sensitive solution, so we always need to be mindful of when we're looking for performance problems. And Foglight's great about providing me some predefined measures of time. Maybe it's the last one hour, four hours, something along those lines. Or maybe it's a time frame that I wish to enter starting and ending points. And if I need to get even more granular with regards to a time frame, I could do that. But through some very very, simple menu selections, we can put ourselves into the time frame that's most important to our environment and then start to take a look at those queries, regardless of the domain, in this case, are having the greatest impact in the environment itself. Now, for folks that do like to be you know a bit more granular, if you will, there are ways to filter these investigations. Maybe a certain response time or an elapsed time or a number of executions is very critical to you, more so than perhaps impact. Well, I have the ability to filter even further there. So not only isolate to a domain, if that's most important, but also get very, very granular with regards to certain metrics. Once I've arrived at what it is that's of interest to me or where these particular, most impactful queries lie, I can simply choose it from the list and get a sense of the syntax itself that maybe this is something I need to copy and deal with a little bit further. But any time I see this Investigate button, Foglight's letting me know, oh, there's some additional detail that you may be interested in. And the results of this will largely depend on the platform itself. But in this case, I'm working with a SQL server instance. It's navigated right to this particular SQL query. And then down here on the bottom portion of the screen I can start to see a summary of metrics relevant to the SQL execution, CPU utilization, weight percentage. And I always like to point out here that this is not an exhaustive list of metrics that are available. There's a tremendous amount that is available to us. But then it's a decision time. What do I need to do now? Maybe I need help tuning this particular SQL statement. Well, you'll notice we've got a little tune SQL option here. And what that allows me to do is to force that underlying database optimizer to generate distinct execution plans from that SQL under investigation. And here's an example of what that might look like. In this particular case, the investigation took about four minutes. But it generated 150 semantically equivalent SQL statements, so 150 statements that are semantically equivalent to the problematic statement that I found very easily through Foglight. And of those 150 statements, 84 of them were eliminated because they had execution plans that were identical to the original. Well, great. You rewrote it. But if it follows the same path. I don't care. So what we're left with are 66 distinct execution plans. And now these become points for comparison. I can compare and contrast plan costs, if that's what I'm most interested in, its syntactical differences that exist between the alternatives, plan differences that exist between the alternatives. And then I can take it a step further. I could actually execute a subset of those alternatives so that I have hard data. And that's what I love about this example. So the statement that says is the fastest-performing alternative actually has a higher plan cost. Well, that may not be something I would have considered in my investigations. Or maybe I just am not comfortable doing this type of activity on my own and I need a little bit of help. The bottom line is Foglight can make it very, very easy for you to not only locate those queries that are ripe for optimization, but also then to help you take a next step of looking for better-performing alternatives. Well, that's great stuff, Ben. We're all looking for an easy button, and you certainly showed us a way there. So if you'd like to