Voice of the DBA

Tag: T-SQL Tuesday

  • 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.

  • T-SQL Tuesday #156–Ready for Production

    tsqltuesdayIt’s a busy time for me, but it’s also T-SQL Tuesday blog party day. I’m rushing a bit as I forgot about this (thanks for the reminder, Deb) and had to help on the ranch this morning.

    In any case, a moment of Zen.

    20221108_082454

    Now, for the T-SQL Tuesday post to answer Tom’s invitation.

    The Quality Bar

    Lots of code gets through to production. I suspect many of us agree that not all of this is production quality. Often we find issues with code that doesn’t perform well or even meet the specs of what we required.

    I haven’t had to put much code in production in the last decade, but I do remember doing so, and I remember supporting code. To me, the code quality that defines production is this:

    Does it make my phone ring?

    If the answer is no, it’s production quality. If it does, then it’s not. That’s how I’ve worked on things in the past, and it’s served me well.

    You might argue, Steve, that doesn’t help. How do I know what code will meet that metric? There’s no easy answer there. You need to know the system, the requirements, the clients, and the workload. There is a lot that goes into deciding how to build code and what commands, structures, architecture, etc. is suitable.

    A few examples. In SQL Server, we avoid cursors because they are not efficient. However, if I have very rare processes, or sometimes on-offs, a cursor might work fine. If it doesn’t overload the server, runs fast enough, and gets the job done, why not?

    Another example is using CTEs to pre-aggregate some totals so that I can write a simpler query for a report with related data that isn’t aggregated. This might not be very efficient, and might create a lot of logical reads. However, if the report isn’t a problem with the server workload, is it production quality? I think it is.

    The caveat to this is you also need to know data growth. What is production quality today might not be in a year. Potentially we need to refactor code later. That’s fine for me if the situation changes, but it’s not fine if I can forecast this being a problem and I have a better technique, perhaps with some WINDOW functions and less CTEs. I don’t want to defer work unless I don’t have a choice. If I can write better code today, I should.

    This also means I ought to be learning more about how to produce better code from others on a regular basis.

  • T-SQL Tuesday #155 Round Up

    It’s time to look back at the 155th blog party. I was the host this month, asking about Dynamic SQL. I got quite a few responses, which I’ve gone through and summarized below. If I’ve missed someone, please ping me.

    The Round Up

    There are some great posts, so if you are interested in any of these areas, click through and read the original post.

    Rob Farley is first, as usual. He’s in Australia, so often he gets to publish close to the start of the day in the world. This month Rob writes about the dangers of dynamic SQL and how you can actually not take values of object names from the user. Instead, use their input to search sys.objects and get the value there.

    Vitaly Bruk writes about how single use plans with EF cause issues, and how to solve them.

    Richard Swinbank talks about how to use dynamic SQL to generate SQL code from metadata. Something I’ve used quite a bit in the past.

    Erik Darling takes time to discuss the datatypes used with dynamic SQL and how you might deal with the requirement for NVARCHAR when building the string.

    Ajay Dwivendi has a method for gathering SQL Server health metrics using dynamic SQL.

    Aaron Bertrand gets a post in, despite being on vacation. He writes about ow you might use dynamic SQL to execute code across all databases.

    Brent Ozar adds comments to Dynamic SQL. He suggest you should as well.

    Oliver Van Steenlandt writes about a couple of cases. Managing different levels of aggregates and for building ETL scripts.

    Raul Gonzalez reminds us of the security issues with dynamic SQL and SQL Injection.

    Reitse Eskins has a lot of schemas and uses dynamic SQL to build GRANT scripts.

    Shane O’Neill shows a few Dynamic SQL tricks to make it easier to debug.

    Gerard Jaryczewski is new to the T-SQL Tuesday party, but skips in with a solution to a nightmare for a SQL developer.

    Josh Smith gives us a three act play that is amusing to read.

    Nigel Foulkes-Nock takes a moment to examine the foreach db procedure.

    Kevin Martin writes about a search procedure you don’t have to write, because it’s generated.

    Ken Fisher notes that if we generate code, we need to generate comments as well.