Voice of the DBA

Tag: T-SQL Tuesday

  • The AI T-SQL Tuesday–#160

    tsqltuesdayIt’s time for T-SQL Tuesday again, and this time there is an interesting invitation. Damien Jones reached out and wanted to host, so he gets to pick the topic this month. His invite is on the Microsoft OpenAI partnership.

    T-SQL Tuesday is a great way to keep the community engaged with a monthly topic hosted by someone new each month. I manage the party, and if you want to host, let me know. If you want to find a new topic for your blog, write each month and link to the invite.

    OpenAI

    If you read the invite, OpenAI is an artificial intelligence (AI) research company. They’re behind the and DALL-E 2 and ChatGPT services. I’ve only lightly used these, but they have been in the news quite a bit in the last year.

    Microsoft supports the company with investment and provides the cloud services they use. The invite this month is asking what we’d like to see from this partnership. What AI things should Microsoft try to integrate in Azure, O365, SQL Server, etc.

    My Wish List

    What I would love to see from this sort of partnership is more of a personal assistant AI that helps me with typing/coding/work on my laptop/desktop. I’ve seen great strides in autocorrect on my phone, and Google has some nice autocomplete stuff in Gmail and other apps. I see some of this from the laptop, but what I really would like is a better assistant that does stuff for me.

    I’m thinking of these types of things:

    • autocomplete, which I see is VS and Copilot, but more, smarter complete for the code I write. Recognize the types of mistakes I make.
    • In the Command Prompt, when I type “get status”, recognize I’ve erased and corrected this to “git status”.
    • Lately in every place I’ve needed it, I type “Floriday” for some reason. Please just fix this.
    • Help SQL Prompt be smarter, and auto complete the similar types of queries I run.
    • Recognize that I often write certain things over and over, bring up a template of sort, or remind me that it’s time to handle this task.
    • Recognize repeating actions and offer to write some code. I have a few import tasks where I have to download a file, run code to import it, and then run a SQL script. Offer to do that for me. That’s where another human would recognize a repeating task and handle it. In this case, it’s not easy to log in and download a file from a script, but an AI assistant could integrate some semi-manual tasks like that.
    • Handle some recurring search/replace stuff for me, maybe with a quick popup. In Evernote, there is some HTML added to notes that I need to remove constantly. Look for repetitive actions in an app and offer to handle them. It’s not a lot of keystrokes, but it’s also something hard for me to automate, but perhaps easy for an AI.

    These are selfish, quick things I’m thinking of, but beyond what a basic voice assistant can do today. ChatGPT seems to be moving in the direction where it might actually be able to be a better assistant, if integrated into the OS and also customized and adapting to each user.

    I’m sure we all repeat lots of actions, perhaps an AI might help us better handle these things. With notifications, reports of actions, and auditing, it might be something I’d trust.

  • T-SQL Tuesday #159– Resolutions

    tsqltuesdayI missed T-SQL Tuesday last month. I got busy and distracted with some travel. For #159, Deepthi Goguri hosted. I’ve enjoyed watching her blog and grow her knowledge the last few years and I was honored to meet her in person at the 2022 Summit. Hopefully the first of more meetings.

    She had two choices: SQL Server 2022 new features or New resolutions. Both are tempting. I’m actually in a SQL Server 2022 class with Bob Ward as I start this post, taking a break from overload.

    Hmmm, what do I write about?

    Note: You can always fill in your blog later with T-SQL Tuesday topics. Just pick one and link to it. That’s what I’m doing here.

    A Neat SQL Server 2022 Feature

    I’ve sometimes been excited by new features in SQL Server, sometimes not. In this case, one of the interesting things I learned from Bob Ward was on the new contained AGs in SQL Server 2022. This is a neat implementation that essentially creates a new master and msdb database for the AG that contains the server level objects needed by the AG databases. This gets logins, jobs, etc. into these extra dbs.

    There might be some holes in here, but being able to have these objects automatically synched is huge. The downside is you need to add these objects while connecting to the AG databases, so I can see some admins making mistakes and connecting to the instance rather than the AG, but I’m also hoping some automation, some scripting, etc. is used to ensure this works smoothly in your environment.

  • T-SQL Tuesday #158–Implementing Worst Practices

    tsqltuesdayNot that I’m looking to do this, but that’s the invitation from Raul Gonzalez this month.

    This is the monthly blog party where we write on a topic chosen by the host. All topics are tracked at T-SQL Tuesday, and if you want to host, you need a blog and then ping me.

    Just Do a Little

    I’m going to tackle two sides to this, the administrative/sysadmin side and the development side. I think it’s easy to implement worst practices, especially in busy environments, if you haven’t gained some knowledge and prepared yourself to do better.

    The main thing I’ll point out is that it can be hard to implement best practices, or sometimes even decide what is a best practice. However, you ought to be able to avoid the worst practices.

    Worst DBA Practices – Poor Setup

    One of the things that has often been done in technology, especially in the Microsoft-based world, is accept defaults, get software up and running, and forget to tackle ongoing practices. In particular, there are two things that I consider worst practices: backups and sysadmin.

    First, no backups. Above all, even above security, we need backups for our data. If we have those, at least we can recover. When you set up a new database, you ought to ensure you have backups implemented. Right away. I’m saddened that Microsoft hasn’t made it easy to implement this as a part of setup. While you can use unattended setup or dbatools or something, it takes a little prep.

    At the very least, schedule Ola’s backup solution in each instance that is set up. At least with USER_DATABASES set, this will pick up new dbs as a backup.

    Second, don’t use sa/sysadmin or any privileged account for applications or even DBA scripts. Set up another account that can be disabled, password changed, or some other security measure. Too often people never set up another account and get used to using sa.

    Truly a worst practice.

    Worst Developer Practices – Starting with SELECT * and NOLOCK

    Aaron Bertrand has a number of bad habits posts, which I think are worth reading. If you can’t adopt his best practices, at least avoid the other issues.

    Two worst practices I think create technical debt and later problems are SELECT * and NOLOCK. If you can’t do anything else, at least avoid these.

    The first (SELECT *) leads to issues with extra data movement across the wire, extra reads in SQL Server, and in general problems with refactoring items as you never know where an application requires certain columns. I know we won’t get perfect, but don’t use SELECT * in any production code. The only place for this is when you want a SELECT TOP 10 * to get a feel for what data is in the table. Every other query in an application ought to specify what columns it needs.

    Note: Using SQL Prompt and getting all columns is just as bad. Pick the ones you need.

    Secondly, NOLOCK should not be a default item. There are data integrity issues, which can cause you problems down the road. Putting this in often means everyone is terrified of removing it. Don’t start here.

  • T-SQL Tuesday #157–The End of Year

    tsqltuesdayIt’s that time of the month, and I’m late. I’ve been on holiday for a week, so this is a quick post for T-SQL Tuesday. This month is hosted by Garry Bargsley, and is a fitting topic for the last month of the year.

    Garry asks about end of the year data activities. Most of his examples are administrative, but I’m sure there might be some dev activities as well. I’ve got a few thoughts on each, some of which I’ve done, some of which I wish I’d have done in the past, but recommend now.

    T-SQL Tuesday is a great place to participate in the community and a great way to show your knowledge and skills off as well for potential employees. Write your own posts on a blog, or somewhere like LinkedIn, Medium, or another site. If you want to host, ping me @way0utwest or sjones at sqlservercentral dot com.

    End of Year Administration

    When I’ve been in the Operations side of data, there aren’t a lot of things I do at the end of the year, but I do find the downtime useful for some maintenance and cleanup. Usually we are doing well reactive stuff as business slows down, so I spend time on things that I’ve wanted to do all year, but haven’t had time to tackle.

    The things I try to do:

    • Index cleanup – look for dups, unused, etc.
    • archive/delete data or tables – I try to clean data where possible. Less always makes things faster
    • Chronic issues – Think about how I can solve something and prevent future problems
    • Space planning – look over trends and be sure we’re ready for next year.
    • Security – remove old accounts (or disable) as much as possible.

    End of Year Development

    Development tasks tend to be more tightly specified, and there often isn’t a good end of year list. However, similar to administration, one thing I have tried to do when times are slow is tackle things I’d want to change, but never have time. End of year is like this, as are some times after deployments.

    Apart from code I might want to refactor or change, the big things I might look at during the end of year are:

    • Branch cleanup – easy to have some of these hanging around.
    • Pipelines – Use time to improve these, or ensure that they are noisy. Either change something, reduce tests, or try to avoid any unnecessary things causing the pipeline to go red.
    • Learning – covered below.

    Bonus – End of Year Career

    This isn’t specifically for SQL Server, but it could be. This is a good time of year to stop and try to assess how things went. It’s also good to look forward, and use slower times to make the future better. A few recommendations:

    • Always have a list of things to read/learn/practice. Use slow times to work on something. If nothing else, tackle the Advent of Code.
    • Assess your career – is this the place for you? The job, the employer, the field? Think about what things excite you and what don’t, what make days drag, or go by quickly.
    • Plan for the future – Make a few career goals. I’ve done this for a few years, and it helps me continue to learn and grow.