Voice of the DBA

Tag: sql server

  • Computer Algebra

    I was a bit of a math nerd in high school and college. Some of you might have been as well, but I took advanced math all through high school, culminating with AP Calculus as a senior with 11 other kids (of about 320). In college, I started with Calculus III freshman year and went on to take 7 more semesters of various high level maths. One of those classes included analyzing data with linear regression, which we did with hand calculators and formulae.

    At SQL Saturday Pittsburgh 2024, I watched a talk from Jeff Moden on linear regression. It was a trip down memory lane, with Jeff explaining how the process worked, the flaws, and how this technique could be used to do some predictions on data stored in SQL Server. It was a great session on the topic, but I liked that Jeff showed how you can use SQL Server to do various math calculations that might be useful to analyze data. I see applications sometimes programming various formulas, but I don’t often see people doing this in database queries.

    This reminded me of some early programs I’d written in BASIC on an Apple II computer. We had to do labs and hand calculate out various results based on measurements. However, small mistakes in our notes sometimes created large errors in our lab reports, which would affect our grades. Sometimes we’d make a mistake in the chemistry process and sometimes we’d just write the wrong number down (or forget to do so). However, we couldn’t repeat the lab, so filling out reports at home was maddening and stressful.

    I wrote programs that create a form and let me enter all my data. This would calculate the results, and I could “massage” the data to get a low error count. Maybe not the most ethical method, but for high school labs in which we were time and resource limited, and where our grades depended on a high level of accuracy, it seemed acceptable to me. I also learned the formulas better since I had to program them and debug my work.

    Creating a simple system to do calculations can be very handy for many of us in our lives. While there is software available for many purposes, like examining mortgages or calculating a budget, doing some of that work yourself let’s you customize things, as well as ensure you understand how the program works. However, instead of C#, Python, or something else, I might use SQL these days as I use daily. I can store data across time, something that might help me better analyze my own data with SQL.

    If you’ve never tried math formula in SQL Server, give it a try. You might have some new appreciation for the equations you solved in school, while building a little more familiarity and skill with the language you use in your daily work.

    Steve Jones

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

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

  • New SQL Server 2022 Functions

    At the recent 2024 PASS Data Community Summit, I saw a lightning talk from Mikey Bronowski on the New T-SQL Functions in SQL Server 2022.  Before the talk, I made a joke with him that none of these were new because it’s 2024. They’d been out for 2 years.

    Mikey did a nice job, given that he only had 10 minutes, but it was enough to give attendees an idea of some new things they might investigate to use in their own code. If you missed the talk, or you don’t have access to the recordings, we have a series on SQL Server Central that covers these (Part 1, Part 2, Part 3) and helps you understand the new options. The list of language changes is also in the MS Docs under What’s New in the Language.

    I’m curious if any of you are using these new functions? There are a few time series ones, though I think GENERATE_SERIES is the one many of you might have used. Are any of you using DATE_BUCKET? That looks interesting, but I have to admit I haven’t played with it. STRING_SPLIT with the ordinal is my guess for the most popular function people use.

    There are also some new JSON and bit manipulation functions, which might be of use in some situations. GREATEST/LEAST are there, but I’ll have to say these functions haven’t come to mind as solutions in any of my queries or answers I write for questions. I do use the trim functions, though still only with spaces. I guess that some of you might find ways to incorporate trimming with other characters and possibly change old code.

    I do think that many of you can likely refactor code and make it cleaner with these functions, but you should test them extensively. As we’ve seen with some language changes, performance isn’t always better, and some changes (like FORMAT), can cause you resource issues. At the same time, if the code performs well, using cleaner code is a good way to perhaps update your codebase and gain some skill with new techniques.

    One warning. While I like refactoring code, make sure you do some testing, preferably automated, to be sure you aren’t introducing bugs or missing edge cases that your old code covers well. A cleaner codebase is nice, but having working code is more important.

    If you’re using any language features, leave a comment today.

    Steve Jones

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

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

  • The Stretch Database Retirement

    Stretch Database is finally going away. It is being retired. It was deprecated on Nov 16, 2022, from SQL Server 2022. Effective Jul 9, 2024, the supporting Azure service is retired. I saw this in an announcement on Jul 3, though I hope anyone using this service has been seeing lots of reminders over the last couple of years. I know I’m getting MySQL retirement notices for one of my services and need to migrate some workloads this month.

    If you tried this service, you might have realized that the pricing didn’t make sense for most of us. If you hadn’t tried it, it worked by moving some of the data in your tables into Azure, where it could be queried if needed. It was an interesting idea, though most of us would have wanted this to work between two SQL Server instances, not between SQL Server on-premises and Azure.

    In any case, if you’re on an older version of SQL Server, the recommendation is that you bring your data back on-premises. If you have SQL Server 2022, they recommend CETaS (Create External Table as Select), which lets you query data in Azure storage. This lets you put some data in text formats and query it as needed, reducing the use of expensive relational storage disks. Parquet is the recommended format here. There’s also a weird mention of Fabric in the announcement, which doesn’t seem to fit with the objective of the rest of article.

    I haven’t worked with this enough to know how well this performs or what patterns might fit here. I do know that reducing the queries from clients, especially SELECT * and unbounded queries across all your data helps. If your clients always want to query all data in a table, then nothing works well. Building systems that query hot data by default, and filter away from warm/cold data is always helpful. Having some good indexing is also important.

    I don’t know of anyone that uses Stretch Database, so I’m not sure how many people are affected here, but I hope they knew about this before Jul 9.

    Steve Jones

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

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

  • Inside SQL Server Backup and Restore History Pruning with sp_delete_backuphistory

    I had a customer that was looking to document a restore that had occurred on one of their systems and didn’t see it. They had concerns about SQL Server accurately tracking history across time and noted they hadn’t cleaned any history.

    We dug through some of their instance jobs and found one that ran sp_delete_backuphistory. The person didn’t realize this removes restore history as well. This post talks a bit about how this works.

    The important thing to understand here is that this removes backup and restore history. Not just backups. I don’t know I like this, but it is what is documented (emphasis mine).

    2024-06-23 11_09_00-Zoomit Zoom Window

    In this case, the sysadmin didn’t realize this removed restore entries. Once they did, they stopped worrying about things. We could have potentially restored an old backup of msdb and found this data, but they elected not to do this.

    How The Procedure Works

    We can actually see the code for this proc. I have expanded the msdb programmability section under system stored procedures.

    2024-06-23 11_02_10-SQLQuery1.sql - ARISTOTLE.msdb (ARISTOTLE_Steve (82)) - Microsoft SQL Server Man

    I won’t show it, but this works in the following way:

    1. create three table variables with a single ID column
    2. insert data into these two tables from backupset where the date is older than the parameter passed in.
      1. backup_set_id from backupset
      2. media_set_id from backupset
    3. insert data into the third table that matches the backup_set_id from the table in A
    4. start a transaction
      1. delete from backupfile the matching backup_set_id values
      2. delete from backupfilegroup the matching backup_set_id values
      3. delete from restorefile the matching backup_set_id values
      4. delete from restorefilegroup the matching backup_set_id values
      5. delete from restorehistory the matching backup_set_id values
      6. delete from backupset the matching backup_set_id values
      7. delete from backupmediafamily where the media_set_id values match
      8. delete from backupmediaset where the media_set_id values match
    5. commit the transaction (or rollback if errors).

    This is a pretty simple flow, and it works well. The tricky part is that the is joins data in a way that makes sense, but might not be what you expect. This doesn’t remove restores based on the date, but based on the backup rows being removed.

    Know Your Tools

    This is a poorly named procedure, but that’s not an excuse for anyone. If you use this, and likely should, you need to ensure that you understand how it works. The phrasing in the documentation makes sense, but it can be a little misleading as many of us might assume the date is applied to backup and restore history tables.

    It is not.