Voice of the DBA

Category: Blog

Creating a Quick Dashboard Widget

I read Carlos Robles blog on creating an Azure Data Studio (ADS) insight widget and decided to try this for myself. I decided to try and get a list of object types and a count of each. Following instructions, here’s what I did.

First, I wrote a query that would gather the types of objects and counts from sys.objects.

I saved this as a file under my Documents folder. Once I had this, I ran the query and in the results, I clicked on the Chart item to the right of the results.

This displays a bar chart, which is useful at times, but that’s not what I want.

Instead of this, I’ll expand the Chart Type drop down. This gives me a list of a number of items I can choose. For this insight, I’ll use the table type.

Once this is done, I’ll see the results I want. There is a “Create Insight” button above the chart. I’ll click this to get the JSON code that creates this.

A new editor tab opens with the JSON code in it. This is just one long line of code, which isn’t as helpful or easy to work with.

CTRL+Shift+P opens the command palette. Type “format” and you’ll see the Format Document command. Once you format the code, it will be easier to read.

The next step is to open the user settings. This is also in the command palette.

In my ADS, I get the settings list. I can search for dashboard and I’ll find the one I want.

If I put the cursor in the upper left, I’ll get an edit item. I can pick this and I’ll get one of two items. If I’ve never customized anything, I’ll get the “copy to user settings” option. If I have, I get the “Replace in settings” item.

Once I’ve done this, I want to modify some code. I don’t love the search box, so I’ll delete that json widget. Then I paste in my widget code. Be sure you put this in the right place, with a comma between the widgets braces. I changed the name from “my-widget” to “User Object Count”.

Once I do this, I save it and close it. Then I double click one of my server connections and I see my widget on the dashboard.

Pretty easy to get a query to appear on your dashboard as a chart if you want to get some insights. Any query could be used, with the results returned when you open the server connection. You can also leave the dashboard open and refresh it when convenient.

November 8, 2018
A Busy Summit

I’m only at the PASS Summit for two days, but they will be busy. If you’re at the event and want to find me, here’s my schedule. If you do see me, please feel free to say hi. If I can’t stop to chat for long, it’s because I’m likely late for something, but I do want to shake hands.

Thursday

Lunch: I’ll arrive mid morning and get myself checked in. I don’t know if I’ll be in the lunch area, but I’ll likely stop by the Redgate booth.

1:30-3:00 – Branding Yourself for a Dream Job, Rm 612. I’ve done this at the Summit before, and a number of other events, but people like it, so I got picked to do it again. I’ll talk about some practical ways to brand yourself and take control of your career.

3:00-6:00 – I’ll likely go to a few sessions or meet friends, but I should be around the expo area part of the time.

Dinner with friends later and an early night for me.

Friday

Early morning will be final rehearsal and prep for me. I’ll do some early in the week, but I like to practice my timing again, especially for my second session.

11:00-12:30 – Adopting a Compliant Database DevOps Process, Room 606. I did a version of this last year and people really liked it, so I’m back again. I’ll talk process and how you can do DevOps in the database, and will show demos, but really this is a process talk. The details of the demos don’t matter, but you get to see changes flowing through the system with a few taps of a finger.

Lunch – Expo hall for me.

15:30-17:00 – Minimizing the Impact of Data Breaches in Dev and Test Databases, Room 604. A higher level talk, mostly aimed at convincing you to be more careful with data. No real demos here, mostly just a talk that looks at how and why you need to protect data.

17:15 – I’m off, heading to the airport. As I mentioned, a short summit for me.

November 7, 2018
Setting Memory–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I had a great time away, and upon my return, I found lots of emails and messages to review from work. One of these was a note that Kevin Hill had updated his article on misconfigured SQL Server instances. I’d worked with Kevin before I left and thought this was a great topic. As I reviewed his update, I started thinking about one thing: memory.

I typically run 3-4 instances on a host. I usually do this to test different versions and their effect on Redgate products or to review questions from the SQL Server community. I don’t have unlimited memory, however, and need to be careful. At times I’ll set an older version of SQL Server to not start so that I don’t have too much memory pressure for my regular tasks.

I’d like to think I do a good job of setting up SQL Servers, and I did a double check on one of my machines. Sure enough, I had:

This was my SQL 2016 instance, which is the main one. For the 2014 and 2017 instances, I’d reduced this to 4096 as I use those less frequently. However, for SQL Server 2019, I got this:

The error is expected, since I set this up quickly after it was released (and before vacation) and hadn’t done anything. In this case, I need to enable advanced options.

I do that like this:
```
EXEC dbo.sp_configure 'show advanced options', 1

GO

RECONFIGURE WITH OVERRIDE
```
That will turn on the option, so when I run the memory command it works.

That’s not ideal, so let’s lower it to 4096. I can do that like this:
```
EXEC sp_configure 'max server memory', 4096

GO

RECONFIGURE WITH OVERRIDE
```
This will change the memory SQL Server uses. The doc pages describes this, and since I’ve done little on this instance, it hasn’t used much memory. My setting doesn’t do much, but it will prevent more pressure from activity in the future.

SQLNewBlogger

This was a quick post. Once I read the article and realized I ought to check things, I also realized this is a nice, short topic to write about and share with others. If you haven’t checked the settings on your dev machine, do so.

And write about it.
November 5, 2018
Adding SQL Search to Azure Data Studio

There are a limited number of extensions available for Azure Data Studio (ADS), but one that came out early is SQL Search. This is highlighted as a recommended extension from Microsoft. Redgate worked with them early on in the lifecycle of the product to get this extension working, including helping spec out the APIs for interaction with ADS.

My extensions pane is on the right, so my details are on the left. There’s a description of SQL Search as well as a link to install the extension. As with most extensions, the link takes you to a download page. For SQL Search, this is a Redgate Foundry page with a few builds. I picked the public one.

Installing extensions is basically using File | Install Extensions from VSIX and picking the downloaded file. You need to approve the extension and then reload once it’s installed. You can see it installed if you re-open the extensions pane.

Using SQL Search

To use this extension, you can use the CTRL+Shift+P command to bring up the command palette. When you do this, you get a way to run commands at the top of the ADS window. This is a handy tool that you will use often.

If we type “SQL S”, we’ll get the SQL Search commands. There is a reindex command and a search command. These are the two items implemented at this time.

The re-index command will update the SQL Search index with information from the current database connection. If you cannot find an object you need, then you might run the reindex.

The other command allows you to search the database. If we click this one, we get a new edit box at the top of the window. Focus will be in this window, so you can type a term. For me, I know there are a number of items in this database for blogs, so let’s enter that.

The results will appear in a new tab alongside your currently open tabs.

The results show me that I have an object name along with the schema and database. We see the type of the object and then the way the object was matched. Next to the object name, there is a circle with an ellipsis. If we click this, we have a couple of choices for what to do with our results.

We can highlight this object in our object explorer, which will, well, do nothing for me. I suspect that the call from the extension to the servers Object Explorer is flaky. Even if I open the Servers pane and then click “Reveal in Object Explorer”, nothing happens. I see whatever I had opened in the Servers Pane.

If I close the server pane and instead click View Definition, I get a split pane on the right with the object definition. I can resize this as needed, but this is a view only pane. I can’t edit or execute this code. It’s for reference, which can be helpful when coding, but is of limited use.

I can copy this code for another window if I click the start and end places in the search results while holding the Shift key. Then I right click in the window and select Copy.

This is a limited use extension, but it does allow one to find code quicker than browsing through the Object Explorer. We can keep the search results up as a separate pane, so that we can use this as a pick list if we were looking for all the dependent objects for an object.

One nice touch from ADS is that if you CTRL+Shift+P again, the SQL Search commands appear at the top as recently used items.

Give SQL Search a try and let us know what you think.

November 2, 2018