Voice of the DBA

Tag: Redgate

Monday Monitor Tips: Finding the Hostname for Queries

I was chatting with a customer recently and they wanted to know which host was sending in queries that were causing problems in real time. This post looks at where you can find the hostname for running queries, which is in two places.

This is part of a series of posts on Redgate Monitor. Click to see the other posts.

The Server Overview

There are two places where you can see the host name. From the server overview, you can go to the current activity, or the new Query Executions tab in preview.

Current Activity

In the server overview, there is a lot of data, but most people scroll down to see the list of queries, such as this list from the ssc-db-n3 instance.

There is the database and lots of data, but no hostname. The reason is that these are aggregates. Each of these queries has run many times, possibly from different hosts. This is a historical view.

The better way to look for details on certain problem queries now is to look for current activity. I’ve zoomed in and highlighted this item below.

If I click that, I get an sp_who, or sp_whoisActive view of the server, and I can see the logins listed here. That’s helpful.

Still no hostname, but if I pick a query from a login, and see the details, I see this view.

In the lower left corner, I get the hostname. I’ve zoomed in below.

We could add that in the main box, but we’re trying to surface the most important info, and for most of our customers, that isn’t the hostname. However, we have added it in the detail.

If you’re like it in the main box, or would like to choose which fields are there (maybe host and not program?), send us a note to your rep or to sales@red-gate.com.

Query Executions

For some of our servers, we have a new Query Executions tab that uses Extended Events to get some data. The Workload02 system has this enabled. You can see the tab at the top, and then the view of this below.

If I zoom in to the lower right, you can see the hostname as part of the details for query executions, along with other data. As you are examining the details of those queries which have run for over 5 seconds, you can get the metadata about the host, application, and more.

Summary

Hostnames are available, you just need to learn where to look. Hence this post. Hopefully this gives you a quick tip on how to find them.

Redgate Monitor is a world class monitoring solution for your database estate. Download a trial today and see how it can help you manage your estate more efficiently.

April 21, 2025
The Book of Redgate–Being Reasonable

As a part of the Book of Redgate, we have a series of (red, of course) pages with the title “What we believe”. These are our values, as set up by the founders. The first one of these is:

You will be reasonable with us

We will be reasonable with you

Two simple sentences, but they really encapsulate how we try to work together. We know there are stressful times, there are hard times, and while we all want to follow the golden rule (treat others as you would want to be treated), we sometimes fail. However, we try to be reasonable with each other.

If I ask for something, others should try to accommodate me. If I ask for too much, and they tell me that, I should try understand that. Being reasonable is having sound judgment, being fair and sensible, not being extreme.

We try to get along with others. Some good examples of this are us setting normal working hours, but being willing to flex with others. If someone goes above and beyond, we recognize that and perhaps go out of our way to make it up to them.

One example of this stands out in my mind. At our annual company meeting our CEO told a story of a deal that they were trying to close during the year. A crucial part of this deal was one employee, who had scheduled a holiday previously. As the deal was getting close, and in danger of problems, this employee came off vacation to help finish something. Our CEO not only recognized this, but personally thanked them in front of the company, gave them more holiday to make up for it and sent a gift.

We are reasonable with each other, all of us being willing to bend and flex, but not abusing that willingness.

Just like a family does with each other. At least, my family does.

I have a copy of the Book of Redgate from 2010. This was a book we produced internally about the company after 10 years in existence. At that time, I’d been there for about 3 years, and it was interesting to learn a some things about the company. This series of posts looks back at the Book of Redgate 15 years later.

April 4, 2025
Comparing My Current Schema with a Backup with SQL Compare

A customer asked if they needed to restore a database from backup to compare the schema in a database. They don’t and this post shows that.

This is part of a series of posts on SQL Compare.

Setting Up a Comparison

When I open SQL Compare, I see a screen that looks like what I’ve shown below, with a database to database comparison.

At the top, to the left of “Source”, there is a drop down arrow. If I pick that I see these choices: database, backup, snapshow, scripts folder, SQL Source Control, SQL Change Automation, Flyway. Those last 3 are project types for Redgate tools.

If I select backup, I get a dialog where I can add my backup set files. I can add full or diff backup files, but not transaction log files. If I click the “+Add backup set flies”, I get a file picked, and I can find a backup file.

Once I pick one, I see it in my list. I can now clear the list or add more files. The details of how this work are documented at: https://documentation.red-gate.com/sc/working-with-other-data-sources/working-with-backups

Once I have my backup, I’ll set the target, in this case a copy of Northwind that I’ve altered and called Westwind. This is on my local instance.

When the comparison completes, I see the differences. This was without any sort of restore on my instance. Note that the top left icon for Northwind_FullRestore has a different icon. I have this database on this instance, but it’s different than the backup.

If I expand the results, these look like any comparison. I see those things that are the same, only in one or different. In this case, as we are trying to make the target look like the source, those objects in my db and not in my backup would be dropped if I deployed all changes.

Summary

This is a short demo of using a backup as a comparison source against a database. I haven’t really shown a flow or scenario, but I’ll do that in another post. This is just a short proof that this works.

SQL Compare is an amazing tool that millions of users have enjoyed for 25 years. If you’ve never tried it, give it an eval today and see what you think.

March 31, 2025
Monday Monitor Tips: Looking Back in Time

Often we find out about a problem reported by a customer after the incident has passed. This might be from a trouble ticket or even an email that we didn’t see until a period of time has passed.

How can we look back at the activity of a server in the past? This post looks how a DBA can time travel back to a situation that occurred in the past.

This is part of a series of posts on Redgate Monitor. Click to see the other posts

Time Traveling

Let’s imagine I get a ticket that said there was a problem at 2:15am from a user running a process. I didn’t get to this at 2am, but at 9:15am when I receive it, I need to look back at what was happening.

If I pick a server in Redgate Monitor, I’ll see the view below. This is of the staging02 server on monitor.red-gate.com. By default, this shows me the last hour of activity on the server.

In the upper right corner, I can see the time frame selected on the left (below) and the amount of time. I’ve selected the drop down, and there are many other choices. I also see the metric time at the top, just in case, I’ve started to mess with other values.

Note: there is a calendar control to the left that can go back to previous days if you don’t want to use the time duration drop down.

In this case, let’s jump to the last 12 hours. If I select that, you can see my display changes a bit, zoomed out to show 12 hours not 1. The four charts below haven’t changed, however.

Most of the top chart has a darker background, except for a portion at the far right, which has a white background. This white background part is the focus window, and it determines what the 4 graphs below show, as well as the query information and other data.

This is set to 1 hour, but I can expand it. If I drag the box on the left side of this further to the left, I can expand the amount of time shown. If you look below, I’ve expanded this to 7:37am as the start.

I can also slide this. I’ll slide this to the left to cover to 2:00am-3:00am part of the graph. Now I see different views below in the four graphs.

In this case, I now can focus on the 2:00am issue. I see an annotation that there was a Flyway deployment at 2:00am. You can see the annotation zoomed in with the tooltip when I hover the mouse on this icon.

I can scroll down to the query area, and I see the top queries, of which there were just a few.

The top one has a lot of duration, and if I expand it, I can see the query history. Note there was a query plan change just after 2:00, when my deployment occurred. The duration went up and then started to slightly drop. I see another plan change at 2:40am, and if I were to look back at the top, I’d see a second deployment from Flyway at that time.

I don’t quite know what changed in the deployment, but I’d start looking here to see if this affected my query.

Summary

The focus window in the overview for an instance allows you to set the time frame in which you see data related to that instance. This lets you time travel back to look at the server as it existed in the past. The amount of time you can travel back depends on your data retention settings, which we’ll examine in another tip.

Hopefully this gives you a quick tip on how you can focus your efforts to a relevant period of time when you get an issue to review.

Redgate Monitor is a world class monitoring solution for your database estate. Download a trial today and see how it can help you manage your estate more efficiently.

March 24, 2025