Voice of the DBA

Tag: administration

  • Knowing When to Respond

    I ran into this quote on the Microsoft Learn site, which I thought was a great way to think about how to administer a system: “Without a baseline, every issue encountered could be considered normal and therefore not require any additional intervention.”

    When I’ve had users file tickets or complain about things not working well, I’ve found more often than not their perception has changed more than the actual performance. I’ve been called for “slow applications” only to find out that “slow” was 30 seconds and the complainer wasn’t sure how long it used to take, but today being end of the quarter, it is slow. Digging into monitoring history has shown that the query always took at least 20s and could take over 30s. My main takeaway was a little stress for users sometimes culminates in unnecessary work for operations staff.

    There certainly are times when a database query takes longer than expected but is it because the system is overloaded or there’s a lot more data? When was the last time this ran and what changed? Are there more queries against the same objects than in the past? Even when there are real problems, without knowing how a system typically looks at this time, we may struggle to quickly determine where the problem lies. We may not even know how to craft a good solution without some baseline.

    Maybe the best reason for me to know a baseline is for triaging and prioritizing issues. Seeing a server at 100% CPU is one thing, but if this is a daily occurrence, I might decide another issue is more important. Especially at 2 am.

    Having a baseline for your systems is important. Build a system if you must, buy one if you can, but get monitoring set up for your systems. It will help you focus development efforts when changed code doesn’t work as expected. It also helps your operations staff to help them respond more efficiently to future issues.

    Steve Jones

  • Reusing Tools for New Purposes

    One of the things you learn early on in programming is that you ought to reuse code whenever possible. This often means refactoring code into functions or methods. This ensures that the code is more easily maintained and that the knowledge and work of solving the problem is reused in many places.
    In database code we don’t do this too often. We certainly can reuse some code by encapsulating it in a view, but this often brings performance penalties. We often don’t want to reuse code in stored procedures and functions as embedding this into queries can cause lots of issues. In fact, it seems that much of the way databases optimize query performance isn’t that amenable to reusing code.

    That being said, I ran into an interesting case recently when thinking about maintenance in the cloud. Someone was asking about SQL Agent and the lack of support for it in many PaaS database systems. I understand that, and while there are some ways to do this, they feel complex compared to using a SQL Agent on a local instance, which is usually easy to set up and readily available. One of the speakers taking questions mentioned that they user shouldn’t forget about Azure Data Factory as an automation agent.

    That caught my attention as I hadn’t thought about it before. This week, there was a blog on that very topic and I read through it to see what I thought. While this isn’t as easy as SQL Agent, it does seem to be easier than Azure Automation, and likely more familiar than elastic jobs.  That’s if you already have ADF running in pipelines. In many ways, this feels like using the maintenance plans in SQL Server, though just the call a stored procedure task. Since many people use a solution like Ola’s, this is very easy to implement.

    I like that this pattern reuses skills and a system that you may already be using, transferring the skills from one area (ETL) to another (administration). This might not seem like much, but limiting the tools and technology, reduces complexity and means that each person needs to know less to support your environment. I’m a fan of code re-use, outside of T-SQL), and I think reusing other technology systems, where appropriate, is a good idea..

    Steve Jones

  • The Need for 256GB

    I have seen a few people call for raising the RAM limit in the Standard Edition of SQL Server. In 2016, Aaron Bertrand voiced this, and for 2019, Glenn Berry asked that the limit be raised to 256GB. In the last newsletter of the year, Brent Ozar asked Santa for a 256GB limit.

    I wonder how many of you would really take advantage of that. In the Azure SQL Database pricing table, to get beyond 128GB of RAM, you need to go to 32 cores. For Azure VMs, you need to purchase even more cores.  AWS EC2 VMs require 32 cores to get to 256GB.

    How many of you use this many cores for your SQL Server Standard Edition instances? I’m sure some of you do, but is it many instances that require this many cores and RAM without Enterprise Edition? If you do run EE, then is it because you need more resources or because you need some other EE feature?

    Certainly, the use of lots of resources is likely something Microsoft considers to be a feature. They want more for licensing when you have a large workload. I don’t know if I think this is morally fair, after all the bits are really the same and there’s an artificial limit that doesn’t allow the use of them with more underlying resources.

    As a side note, this has made it into other areas. My Tesla offers me the option for more acceleration if I pay them US$2000. The hardware will already support this, but it’s a software unlock for a price. That feels strange.

    Across all the instances you have, how many of them have the need for more RAM? Perhaps a better question is whether your organization would allocate more RAM given the cost involved. I still see too many organizations that underspend for hardware when it would make a difference for customers. Of course, many of you might also get better performance if you learned to write better code that efficiently solves query problems.

    Steve Jones

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

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