Voice of the DBA

Author: way0utwest

Daily Coping 4 Jan 2022
I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

Today’s tip is to set a career goal to focus on this year.

I have to admit this one is hard. I’m in my 50s, near the end of my career. I honestly hope this is the last job that I have where I need to work. I hope I find things to keep my busy in retirement, which might be paid jobs, but I’m hoping this is my last full time, need a paycheck job.

I do usually set some goals to help me keep working on myself, but seeing this as a “career goal” makes me think I ought to be more ambitious.

I don’t worry or aim for too many technical skills, but I would like to get better at helping my employer do well. As a result, I’m going to set a goal this year to brush up on Oracle and learn about PostgreSQL. In doing this, I want some way to measure this, so here it is:
- Set up a git repo with folders to SQL Server, Oracle, and PostgreSQL.
- Create 3 pipelines that will CI changes for each platform.
- Have a release pipeline to each platform, likely hitting a container somewhere for each platform.
- Ensure that I can demo this and that I know enough Pl/SQL and pgsql that when I write T-SQL, I can duplicate it in the other languages.
Not really a high bar, but something to work on this year.
January 4, 2022
Getting Started with KQL
I saw an episode of Data Exposed with my good friend, Hamish Watson. He talked about KQL (Kusto Query Language) being the next query language you need to learn. I was skeptical of the title, but I decided to give this a try.

In the episode, Hamish points out a cheat sheet from Microsoft, which I thought was a good resource. However, while watching the video, I browsed over to the demo site Microsoft has at https://aka.ms/lademo. You need an Azure account to log in, but this is a demo site where you can query some Log Analytics data. The new query window below is what appears when you go here:

Hamish shows some sample items, and I noted that to query a table, you just include the name. The query window in Azure has intellisense, and I used Hamish’ example to look at perf data.

The structure of queries is that you choose a table, then you can add a pipe (|) and add a where clause or other structures. I glanced at the cheat sheet, but really I played around a bit.

For example, I saw in the demo there was a “take” option, so I tried this:
```
Perf

| take 6
```
This gave me six results, so it’s like a TOP clause in T-SQL.

Next, I tried a WHERE. This is also structured as a line after a pipe. I picked a value I saw and then ran the query.

I noticed there was a summarize option, so I tried entering this. What I liked was that I had some intellisense options to help me with the various aggregates.

I ran this query, and got an average of the free space across all disks.

That isn’t a number that really means anything. I tried GROUP BY in a few ways, but eventually had to look at the cheat sheet to see just “by” is needed. I ended up with this query to get space by disk.

In some ways, I found KQL to be easier to work with than SQL. It’s cleaner, and logically follows what I’d do when writing a query. I build this in a way similar to how the query is executed, which is unlike how SQL works.

I don’t know if KQL is going to be that important to me, but I will play around with it a bit more.
January 3, 2022
Data Cleanup

The end of the year is when I do a little data cleanup. Not a lot, but some. Work slows down, with no major deadlines, so I’m able to spend a bit of time organizing myself. I usually go through my downloads and documents folders, deleting files I don’t expect to use. I ensure that my jobs removing older log and temp files are running, especially on the laptops. A lot of disuse in 2021 had a few of them filling disks with more files than I had expected.

In a few positions I’ve had as a DBA, this was a time when we’d look to archive away some older databases, and even some data. It was rare, but always invigorating because we knew queries would run faster in systems when we could archive off data. I haven’t ever started with clean databases in a new year, but in a couple of jobs we’d archive off all data that was more than one or two years old into another database. It was available if someone needed it, but the main OLTP databases would contain only one or two years’ worth of data, helping queries perform quicker at the start of the year.

Last month I was at the SQL Server and Azure SQL Conference, and someone asked about the future plans for archive and migration of SQL Server data. The person noted they wished they could easily archive off data, specifically using a feature like Stretch Database. That feature wasn’t cost-effective, but it might be if you could stretch to another instance rather than Azure. No good answer from Microsoft, but that is something I’d like to see.

Archiving older data, often data that is less queried is a good way to speed up systems. However, doing this seamlessly, and with easy access from applications, is cumbersome. Software should make this easy. I set up a database and then point the archive process to that location. The process manages moving data from a table based on a column value I’ve specified. Ideally, I could also decide if I allow queries to span the two databases automatically or I require some switch to allow querying of the archived data.

There are plenty of possibilities here, but I suspect we won’t see any of them soon. There isn’t a lot of extra money to be made by allowing customers to manage their own archival systems, and these days it seems every software vendor is trying to make money from renting features rather than selling them. Archival isn’t one I see many customers willing to pay a premium for, so I suspect the solution for most of us is to write better and code and ensure queries perform well, even when we have terabytes of data.

Steve Jones

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

January 3, 2022
Daily Coping 3 Jan 2022

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

Today’s tip is to make a resolution that you can keep this month.

Update: I wrote this before I went on holiday the last week of the year. However, I managed to tear a calf muscle and haven’t been able to do much. I’m going to need to delay this by a week and aim for Jan 10-Feb 10.

I’m not big on New Year’s resolutions. I think often we pick things are too hard to sustain across a year. I typically set some goals, but don’t look to make major changes.

However, focus is good, especially in this time of not really being in a familiar world. Many things are semi-normal for me, but plenty of things are not.

I’m going to resolve to do two things. First, weekly calls back home to my Mom. I don’t do that enough.

Second, I’m going to resolve to workout every day this month, barring injury. I like working out, and I find it keeps me feeling level headed. I’ve gotten close a few months, but I’m still often missing 7 or 8 days. I want to get back into the habit this month, knowing that I’ll miss some days, but hopefully less in 2022.

January 3, 2022