Transcript

Query Monitor

Hello, everyone. In this video, we are going to talk about the query monitor functionality within SQL Diagnostic Manager. Query monitor is one of those features that is really designed to help fill in the gaps between polling intervals. So if my SQL Servers are being monitored with SQL Diagnostic Manager at six-minute intervals, there are going to be queries that are running between those polling intervals that I am going to miss. And that is really what the query monitor is all about. The query monitor is there to help you fill in the gap between those polling intervals.

Now depending on the version of SQL Server that you are monitoring, there may be a different method of collecting these poor-performing queries. On the newer versions of SQL, anything 2016 or above, we can use query stores. So if you have query stores enabled on databases that can be leveraged to capture these poor-performing queries. For anything 2008 or above you could use extended events, and then on the older versions of SQL 2000 to 2005, we would be using or only have the opportunity to use server-side trace.

Now trace can scare people. This is not a profiler trace. This is a server-side trace that is very tuned for performance. So it is not something that would be very scary to enable. And I will say that you could use trace on any version of SQL Server that SQL Diagnostic Manager is monitoring.

Long Running Query in SQL Server

Now down below is what you would define as a long-running or poor-performing query, depending on what your requirements are. I say long-running because that is generally what I am looking for. But you have the opportunity to look at the disk from a read-and-write perspective, as well as CPU. I also do like to point out for new users that zero means it is not something I am concerned with. So, and these are additives, so if I said you know 1,000 milliseconds and maybe 800 milliseconds of CPU at a minimum.

Right. So both of those things would need to be true before that data is going to be displayed within the viewer. Generally, I look at time. But again it is really dependent on your specific SQL Server. It is kind of hidden. But down below, there is an advanced button. And the advanced button will let you filter in or out certain applications, databases, or text that you want to focus on exclusively or exclude. So there are some options to fine-tune this even more.

Enabling Query Monitor

Now one of the things that you can do to enable query monitor. If you do not want to run that 24/7, obviously you can mainly turn it on when required. But there are alert action responses around query monitoring. So if you want to enable query monitor when something bad is happening. I will show you an example here.

I have this configured for query wait monitoring. But I could change it and say we are not actually concerned about query waits in this example; we are concerned about query monitoring. So, query monitor, we want to enable that, and maybe we want to run that for an hour. In SQL Server CPU and OS [operating system], when this percent gets above a certain threshold it indicates its critical on production servers. And we will go with just 60 minutes instead of 600 that query monitor is going to enable itself. So you have some options there. I will say, though that in most environments, people run this all the time. It is not really one of those features that I think I would be too terribly concerned with around performance.

SQL Server View

Now there are three different views around query monitoring: center mode, statement mode, and query history. They are all basically sharing the same data; it is just different ways to look at it. And I always start with signature mode.

Signature Mode

One of the biggest benefits of signature mode is this column here called occurrence. So right now I am sorting by duration, so the longest-running query that we have seen is at the top of the list around 40 seconds. Now that is nice but is that really what I am looking for? You know this thing only ran once, the occurrence column is indicating one run. If we sort by occurrence though I have other queries that maybe are more important to me. For example, this guy here got picked up 783 times with an average duration of 1.7 seconds. So over the time frame in my case which is over the last 48 hours, this is actually the longest-running query if I add up every single run of it, right. So there might be different reasons that you are looking at poor performing queries. And one of the nice things about the signature mode is it lets you distinguish between something that might just be long-running once or twice or in this case something that is long-running 700 times.

Query History

From here, I can drill down and say show query history. So query history is actually going to show me all the different runs. I happen to choose this one because I did not want to look at 700 runs. I want to look at something a little bit smaller. But the idea is that I am now able to not look at just the average duration of this particular signature, which is the statement that we are running. I am actually looking at the individual runs that we picked up. So that is one way that I use that.

Statement Mode

I can generally drill down from a signature into its actual history. And then from a statement mode perspective that is just going to list out all the statements that have been picked up. So that does not have any drill filtering in it by default, and as you can see, it can take a bit of time.

I have hundreds of queries here that are being picked up. But in this particular view, I am no longer looking at averages. I am looking at actual CPU time, actual reads, actual writes, and actual durations. If you wanted more detail on the query, you can say show query text which would pull up the actual query that was being run. And I will also point out that if you wanted to run this individual query through our prescriptive analysis engine, you can hit the Diagnose button.

SQL Query Checker

By hitting diagnose, it is going to drop you over to the analysis screen. And then what is going on behind the scenes is it is taking that statement in my case that it saw and it ran it through an optimizer. And what it came back with, in this particular case, was a recommendation that we need to work on the where clause.

It is saying that the function probe on column ID reform user’s ID may be causing a table scan. So it is recommended that we consider redesigning the where clause to prevent index oppression and performance degradation.

Now, where is the problem? There is a little link up here at the top, “show problem”, that will actually pull up that statement and show me what I need to work on. This is a pretty simple use case, but the point is that those queries that we design or pick up within query monitor are going to be available within the prescription analysis functionality.

SQL Server Reporting

I will also say that one of the ways that I often will look at poor-performing queries that have been picked up in the query monitor is through something like a top queries report.

So if I need to take those worst performers and share them with a dev team on a regular basis, I might come in and build a weekly worst-performing query report. In this case, we are going to say the top five, run that, and make sure that is the data set that we are looking for. And then use the scheduling functionality that reaches out to the SSRS and build subscriptions to automate this to be emailed out to the team that is working on this particular application set.

But the cool thing about this view is that when we say top queries, that is somewhat subjective. You know are we talking about the longest runners. Are we talking about the most frequent? Are we talking about the most reads? This report is going to break that all down for you.

So the top queries by frequency: this select statement was picked up 3,000 times over the last week. And then we come down further. These are the longest runners that we picked up, the ones that are using the most CPU, etc. So this information in the top queries report is actually coming from that query monitor which is really designed to help fill in those gaps, retain pull, then pull intervals around worst-performing queries.

So with that, I appreciate your time and obviously, if you guys need help with anything, contact your account managers. Here at IDERA, we are always happy to help. Thank you.