Monday Monitor Tips- Enabling Index Tracking in Redgate Monitor

There is a new index feature in Redgate Monitor, but it’s disabled by default. This post shows how to enable things.

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

Getting Indexing Information

If you look on monitor.red-gate.com, inside the details for one of your instances, there is a new Indexing tab. I’ve circled it below.

2024-07-02 15_00_39-ssc-db-n3_(local) - Server Overview — Mozilla Firefox

After clicking this, I see the data below for this instance. This shows me a lot of high level information usage on different indexes. The data shows index, then table, then database, but I can search/filter here. I can also sort by the columns, which contain size, updates, seeks, etc. Lots of data.

2024-07-02 15_03_21-ssc-db-n3_(local) - Server Overview — Mozilla Firefox

This is disabled by default, as the collection of this data used a lot of memory on the Base Monitor system for some users. Not necessarily something everyone wants, so we left it off.

You can, however, enable it.

Enabling Index Data

There is a doc page for this feature, which I show below. This is a fairly easy procedure, but not everyone knows how to do this, so I’ll explain.

2024-07-02 15_05_48-Indexes - Redgate Monitor 14 - Product Documentation

If you look above, in the warning box, it notes how to enable this. From the base monitor, you need to add an environment variable.

To do this on Windows, the easy thing is search the start menu for “environ”. You should see something like this. Pick the “edit the system environment variables” item at the top.

2024-07-02 15_08_19-Media Player

This should bring up the System Properties in Windows (desktop or server). You can also search for environment variables in the Control Panel. Earlier version of Windows let you right click the system and select properties.

Notice the Environment Variables button at the bottom. We’ll click this.

2024-07-02 15_08_59-System Properties

This brings up a dialog that has two sections. We want the bottom, system variables, section. Click the New button at the bottom, below this list of system variables.

2024-07-02 15_10_42-Environment Variables

In the dialog, paste this in the variable name: SQLMONITOR_Indexes

For the value, add “on”. I’m not sure if this is case sensitive, but past in lower case.

2024-07-02 15_18_52-New System Variable

Then you need to restart both the base monitor and the web server. This is in the Service Applet, where you can see the Base Monitor and the Web Service.

Note: depending on versions, you might see this as “SQL Monitor Base Monitor” or “Redgate Monitor Base Monitor”.

2024-07-02 15_23_01-LiveLabConnection (1) - 3.254.186.109 - Remote Desktop Connection

Restart both of these services.

When things come up, you’ll see blanks until some index data can be captured, but be patient and check back in an hour or two.

Make sure you monitor memory usage on your base monitor machine. If you find yourself running low, you can disable this by setting the system variable to off. Or delete it.

Or add more RAM. More RAM cures a lot of things.

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.

Posted in Blog | Tagged , , , | Comments Off on Monday Monitor Tips- Enabling Index Tracking in Redgate Monitor

Responding to a Disaster

Ryan had a planes, trains, and automobiles situation a few weeks ago. On the same day, I didn’t. A delay for a few hours, but an easy trip home. Lots of other people didn’t have a smooth trip, and I had a few friends who spent an extra night somewhere or had flights canceled and decided not to take a trip. The Crowdstrike outage hit the entire world, causing problems everywhere. Just before that happened, Azure had an outage in the central region.

If you were affected by these, you have my sympathies. If your IT job included responding to these, you get all the virtual hugs from me (and Brent). I know what it’s like when someone calls you to handle an outage, and I know it can turn your life upside down. Hopefully, it hasn’t been too stressful to you.

In Brent’s post, he talks about reviewing your DR plans and presenting your findings to your boss. Good advice, and worth reviewing yearly as most of our environments will experience some amount of change. If you follow this advice, make sure you present this in a logical, dispassionate way. I see plenty of technology professionals who get upset when DR isn’t given enough priority. That doesn’t help the situation. Channel your inner Spock and present your results, allowing someone else to decide what to do and accepting their priorities. You can advocate for change, but keep in mind that there is often no shortage of work and limited time. If someone else decides you can accept the risk of poor plans, document that and move on.

Plenty of us will experience a disaster at some point. I’ve had more than a few occur at various positions, and I know many colleagues who have gone through a wide variety of failures. Even for companies that have extensive DR plans, there will be challenges when a disaster occurs. We’re certainly not at the point where any AIs can handle reading our DR document and implementing fixes. Instead, those of us responding will have a guide, maybe a great guide, for how to respond, but we will need to adapt our plans to the current situation. Having the mindset that our plans might not be perfect (as Mike says), will help you deal with any challenges that arise.

Aside from the technical situation, there are also mental challenges in a disaster. You will feel pressure from your employer, but also pressure at home. Your partner might not understand the extra hours. If you miss commitments made to your family, they will be disappointed, and I hope you are as well. You might have expected your schedule to include fun events, and you likely didn’t expect to get less sleep. Your diet might suffer in times of crisis and long hours.

All of these situations can be managed, but it helps to think about them when you’re calm and make plans. Warn friends and family. Think about how to limit unhealthy foods or situations, set expectations with yourself that help you manage stress, and most of all, treat yourself with kindness. Many of us want to help and support our co-workers but ensure that you (and them) get breaks. Imagine you’re in a Crowdstrike situation that lasts for multiple days. Talk about how that will look, even with just a few people and you’ll be better prepared when a disaster event occurs.

Hopefully, it won’t be a large-scale event, but better to prepare for that and experience a minor situation than the reverse.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

Posted in Editorial | Tagged | Comments Off on Responding to a Disaster

A New Word: Bareleveling

bareleveling – v.  trying to improve yourself without anyone else knowing about it, afraid that they’ll think it’s silly or grandiose or unnecessary, or that they’’ll end up calling too much attention to your efforts, transforming a casual tweak into a flashy rebranding campaign.

I certainly engage in bareleveling. I bet a lot of you, especially musicians or singers, barelevel all the time. You practice and work, but don’t want anyone to know. I do that, though I’ve gotten better at playing my songs and not being too worried about how other perceive me. Having a guitar in the lobby of Redgate HQ has helped.

However, I’ve stopped short of traveling with a guitar. I might if it’s my wife and I, but if I have to present or visit a customer, I don’t want to be carrying a guitar around. Mostly I don’t want anyone to make a production or ask me to play when I might be focused on something else.

Hopefully you do some bareleveling in your career as well, though be sure you are getting some feedback from someone that ensures you’re improving in the ways that matter to you.

From the Dictionary of Obscure Sorrows

Posted in Blog | Tagged , | Comments Off on A New Word: Bareleveling

Finding Where xp_cmdshell is Used

I saw a post recently where someone was concerned about where xp_cmdshell was in use inside their system. They felt it was a security risk, and decided to get rid of it. I don’t agree with that, and I think xp_cmdshell can be safely used, by restricting who can run it.

That being said, I was happy to help. I saw someone say search in sys.modules, but that’s not enough. This post looks at what I thought was a better solution.

When you run a query like this one, you only search in the current database.

SELECT definition

FROM sys.system_sql_modules

WHERE definition LIKE ‘%xp_cmdshell%’;

This is fine if you’re concerned here. If I run this on a sample database, I find this:

2024-07-23 14_01_26-SQLQuery1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (70))_ - Microsoft SQL Server

However, that misses a few things. First, system_sql_modules isn’t everything. In this case, I have a proc that runs xp_cmdshell that doesn’t show up. I need all_sql_modules. This has user stuff. If I run that, I see this.

2024-07-23 14_03_06-SQLQuery1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (70))_ - Microsoft SQL Server

However, that’s one database. What is better?

All databases.

To do that, we’ll use the undocumented, but useful, sp_msforeachdb. In this, I can run code as a parameter. I can do this:

EXEC sp_msforeachdb  ‘use ? SELECT definition FROM sys.all_sql_modules WHERE definition LIKE ”%xp_cmdshell%”;’
GO

The problem is I see this:

2024-07-23 14_05_14-SQLQuery1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (70)) Executing..._ - Microso

In the 4th result set, where are these things?

A better piece of code actually tells me which database is in use.

2024-07-23 14_06_05-SQLQuery1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (70)) Executing..._ - Microso

Here’s the code I ran. Note that I use the current database parameter, the question mark, in the SELET as well as the USE.

EXEC sp_msforeachdb  ‘use ? SELECT ”?”, definition FROM sys.all_sql_modules WHERE definition LIKE ”%xp_cmdshell%”;’
GO

That gets me code inside databases, except for one place. What about jobs? I need this code:

USE msdb
GO
SELECT s2.job_id, s2.name, s.step_name FROM dbo.sysjobsteps AS s INNER JOIN dbo.sysjobs AS s2 ON s2.job_id = s.job_id
WHERE command LIKE ‘%xp_cmdshell%’

These two queries will get me the places I’ve used xp_cmdshell.

As long as I haven’t encrypted procs/functions. In that case, I need SQL Compare.

Posted in Blog | Tagged , | 1 Comment