Voice of the DBA

Tag: Database Weekly

  • AI Concerns

    I hosted a webinar a few weeks ago about Artificial Intelligence and how it might affect data professionals. It was an interesting discussion with Kellyn Pot’Vin-Gorman and Brian Randell, with all of us having slightly different perspectives. Overall, we all agree AI is amazing and can be a useful tool for data professionals.

    This week I’ve been in Redmond at the MVP Summit, and AI has been a topic among many MVPs. Lots of jokes have been told, no shortage of which dealt with getting rid of staff. Not that these MVPs want to see less staff, but they know that executives and managers might see all the AI hype about how GitHub CoPilot writes code and think they need less developers.

    I don’t know if that is really how executives will view the world, especially as most IT departments have more work than resources to complete their list of tasks. I can see AI helping get more done, which might mean less hiring (or slower hiring) in the future.

    There was an article this week talking about ways to protect your job in the age of AI hype. It was interesting in that the suggestions all revolve around bringing more value to your job. The suggestions about working in specialized areas, complex areas, being a better employee with documentation, these are all things that I’ve done in my career. It’s what I also see from many MVPs.

    Maybe the most interesting item was to think about incorporating AI into your work now. It’s not necessarily an expert on your work, but it is a tool. As the models absorb more information and become better trained, they can be a level to help you get more work done. They can assist you in tedious work, which is something many of us can benefit from.

    I am looking forward to getting access to CoPilot and keeping an AI tool up on my desktop, learning how it might help me, and maybe more importantly, where it won’t help me.

    I can’t stop this trend, but I can better understand it and learn how it might fit with my daily work. Knowing that helps me better understand how I might have an informed and rational discussion with management about the advantages or disadvantages of this new technology.

    Steve Jones

  • Has SQL Server Performance Improved?

    I saw a very interesting blog post this week from Brent Ozar that asked if SQL Server 2019 More CPU-Intensive Than SQL Server 2016? You can read the post, but a client was having CPU issues and thought everything between their SQL Server 2016 and 2019 environments was the same.

    Brent decided to test this and found that on identical systems, he has queries taking more CPU on SQL 2019 than 2016. A few commenters tested as well, with similar results. Not everyone had similar results, but most did. You can get the scripts from the post and try it yourself if you have SQL 2016 and 2019 instances.

    One would expect that more recent versions would run faster, or use fewer resources, for equivalent data sets and hardware. I know that’s not always the case, but it ought to be the case for lots of workloads. If not, then arguably the newer version isn’t better. It likely isn’t worth more money, and definitely needs more development work. This is my view of Windows 11, which seems to have returned to the habit of earlier Windows version of requiring and consuming more resources than its predecessor.

    I don’t often benchmark or evaluate SQL Server version. I don’t have to make those decisions, but I have seen SQL Server continue to improve on the TPC-E benchmark. However, this isn’t necessarily the same hardware. In fact, across versions, it likely isn’t. There could be more CPU consumed by the same queries, masked by hardware advances (and falling hardware prices).

    Is SQL Server using more CPU in newer versions? I’ll let you see if that’s the case on your systems. Even if it isn’t, you might document some queries (in addition to Brent’s) and record the results. That might help you decide when you upgrade.

    Steve Jones

  • The Top SQL Server Engine Errors

    For many of us, SQL Server just works. We might get some syntax errors if we mistype things, but for the most part, SQL Server runs smoothly in many environments. However, there are some common situations that do occur regularly, and I wonder if you can guess which errors often occur?

    I saw a blog this week from the SQL Server Support group where they covered the top 25 errors that come in support calls. Their goal was to see if they could document and help people better solve problems themselves and reduce the support load.

    Can you guess what the first error was? I’m assuming these are in descending order, but that’s not clear. In any case, the top error was #18456, which I didn’t recognize at first. Reading the documentation page shows this is the “login failed for xxx” error, which is probably my most common error. Often because I can’t type a password correctly, but also because of an inability to select the right instance or user name. There are other causes, and it’s nice to see a long list of things people can check.

    The next error was 19407, which is a cluster communication error. If that’s the second most common error, then maybe clusters and AGs need a bit more resiliency or better setup guidance. Third is an OS error with NTFS, which I’ve never run into.

    If you flip through the list, I wonder how many of these errors are common for you. Do they come up often? I know I’ve seen people post on 912, which is an upgrade error and very annoying. I think some of the upgrade scripts for CUs aren’t that well written and should have better error handling inside them. That would seem like an easy one to fix and reduce call volume.

    There are plenty of network errors, including the “error occurred while establishing a connection” one. That one is usually is a typo from me or a misconfiguration of an instance after installation. Lots of other errors seem network or backup related, which may not be common, but those are errors that likely cause people to call Microsoft Support.

    Maybe the most interesting one is 9002, log out of space. While I know lots of people might not know how to manage space, I also see lots of accidental DBAs get caught here because they set up full backups and not log backups. Their databases are small, storage is cheap, and they encounter this a year or so after they’ve set things up. To me, this is really low-hanging fruit by making it really easy to have an automatic backup process added for each database. Just add tooling to help make this easier, or create a job when a new database is created. If this isn’t needed, let it be disabled, but for those that are installing SQL Server for some COTS application, make this a easy.

    A lot of these errors are ones I’d never call support for, but I can imagine others not feeling that way. Plenty of these are errors I’ve never seen, but I’m glad the documentation is more than just a description of what happens. These updated pages give some possible causes and things that the user can do. That’s something all of us would like in documentation when something goes wrong.

    Steve Jones

  • Coding Magic Values

    It’s 2023. I would hope all software developers would learn that hard coding specific values in your system is more likely to cause issues than not. Across the years, we’ve learned not everything is installed on the c: drive, or that not everyone wants to put all data in a Documents folder (or in OneDrive). We’ve learned that any sort of magic number is poor practice, and we ought to know that hard-coded names are problematic as well.

    Yet, we still see it happening.

    This week I was reading about an admin issue in the Microsoft TechCommunity. This is related to Azure Managed Instance, but it’s really an In-Memory OLTP issue. That was introduced in SQL Server 2014, so I know the code for this was likely written in the 2011-2013 timeframe, but how can this type of issue get through code review and be released?

    In this case, the name of a filegroup is set specifically to XTP. It’s a logical name, and I’m sure that some developer thought that things might be faster with a known location. That doesn’t make sense, and while this might not be an issue for most customers, I’m sure there have been some databases built with a filegroup called XTP. After all, there are companies named XTP. What about if this feature evolves to allow a second filegroup, maybe because of some distributed architecture need in the future? Are there then code paths looking for XTP or XTP2?

    As much as possible, avoid coding values in your code that a user might enter as data. Names, paths, etc. Just don’t do it. Use variables, which are in every language, and let those values be read from the environment. This ensures that you don’t end up with weird support requests from customers because they chose the same value you did.

    Steve Jones