Voice of the DBA

Tag: Friday Poll

  • Fitting Into RAM

    RAM has always been a fairly limited resource in most of the computer systems I’ve worked with in my career. Often there is never enough RAM, and I’d always like more, often to speed up the systems. That has somewhat changed with laptops, as 16 GB really works well for me most of the time. Not that I wouldn’t take a 32GB machine, but I’m waiting for them to become more common and smaller.

    This has especially been true for database servers. It seems that I’ve rarely had a database server that could fit my entire database in RAM. Even now, I have an over-provisioned server for SQLServerCentral which has plenty of spare capacity, but I’m still slightly short on RAM. The target level for SQL Server is about one GB more than I have set. Not really worth complaining about, but still I don’t have the RAM I’d like.

    Last week I wrote about someone that attacked the RDBMS as old and troublesome technology. As a part of this, a method of storing all data in memory was presented. I’m not sure I think this is actually a good or practical idea for most systems, but I did wonder about the idea of data space and size. Certainly I have seen plenty of index space in databases, and certainly there is more index data than other data at times, but I suspect that’s not the case for many databases.

    Regardless, I was curious if anyone has large databases that couldn’t fit into RAM these days. If you think about the largest database you have, how big is it, in terms of data size. Not allocated size, but the total data space used. Would this fit into RAM if you could get 1TB or 2TB of memory? If you can, what about index sizes, are they large? There are a few scripts in this thread if you need one.

    I suspect there are certainly databases that don’t fit into RAM, and likely plenty of instances with more than 1 database that don’t have enough RAM. I still see plenty of people with less than 64GB on their servers, so that’s a battle still being fought. I certainly wouldn’t advocate an in-memory only database, likely because there are going to be other issues, but it’s still an interesting thought. Certainly my server has only 60GB allocated and the databases are well over that in aggregate.

    Maybe asking for a bit more RAM on those critical servers is the way to go, especially if you think you can get the entire database into memory.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.5MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • The Oddest Thing

    My desk is a mess, at least, it’s a mess compared to many people’s workspaces. Not as bad as some, but certainly worse than others. I sometimes care and will clean clear things away, maybe even get rid of some items, but for the most part I have an idea where things are and they can stay there. I’m not sure that a neat, ordered space has ever mattered to me. Some of you may feel differently, and I’m sure a few of you can’t work unless the clutter is clear. If you want to share a picture of your space, home or work, it might be a fun distraction today for others to see how you work.

    When I posted the picture of my desk, someone right away noticed that I had a hose clamp on my desk. I didn’t even realize it was there, and likely it got dropped there after making some repair outside and coming back in with an extra one in my hand. I probably dropped it on the desk as I was going to wash up, since my desk is on the way. It never got moved, and it’s actually still there today.

    Most of us use our desks for work, and I certainly do, but I also end up doing research and learning for home or ranch projects. I’ve been known to sit and watch a video on YouTube or read a manual with a tool or piece of equipment in front of me. As a result, I end up with some strange things on my desk, though most are too large to stay for long. After all, I do need to get technical work done.

    Today I wanted to see what you’re collecting. Is it swag from some event, a random item from your life, or even a strange momento you’re proud of. What’s the oddest thing on your desk?

    For me, right now, I think that since I’m a data professional, the hose clamp is certainly an odd item. There are various bits of electronics and parts, all of which make sense, as do the Aleve and souvenir baseball bat from SQL Saturday Louisville. There’s also a chainsaw clutch tool, waiting for me to get a spare hour to take a tooll apart. Maybe the strangest item is a jar of earplugs sitting to one side. Since I coach volleyball, I keep some around, and for some reason the spares ended up on my desk. Since they’re behind a monitor, I think I’ll leave them be.

    Take a look around your space today, post a picture if you like, and let us know what oddities abound for you.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.6MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Disable or Drop

    When I started working with SQL Server and Windows, it seemed that the administrative side of many actions was limited. We could add and drop many items, but that was it. Relatively few tools allowed setting limits or disabling them, which was a pain. After all, I’d gotten used to setting up accounts for vendors and contractors in Active Directory, often disabling them when they weren’t in use. I couldn’t do this in SQL Server for many objects, which was a pain and an administrative burden to reset them up when troubleshooting issues. This was pre-PowerShell and .NET when any SMO access was a project in and of itself.

    These days SQL Server has done a great job of adding in the ability to pause or disable many objects. We’ve had the ability to lock out an account for many versions, which is a great way to setup a vendor tech support account when it’s needed. This is especially important for security these days, as we may want to be sure that we prevent access by any suspect accounts. Disabling them allows us to prevent their use, but keep all their rights and permissions in the even they are valid accounts.

    We can disable indexes, which can be useful as a precursor to deleting them at some point. We can disable triggers, which is incredibly useful when you are testing or debugging actions on a table. We can disable Extended Events, audits, and more. All of these are useful actions for a developer or administrator, if you use them.

    When things go wrong, we’re often stressed and pressed for time. If there are issues with a system, many of us make snap decisions, which might fix the problem or make the situation worse. Even in those cases where we fix an issue, deleting or dropping objects might cause is extra work later. My question for you is what’s your first reaction? When you need to make a quick change to remove something, security access, an index, etc., do you drop or disable?

    I would hope that you disable, as this removes the effects but keeps the object in the system with associated meta data. Rebuilding permissions or trying to get the old trigger code is a pain (since few people use a VCS, please start doing this). It’s possible that you won’t even be able to get things reset back up in the same manner. That might be fine, but it’s not ideal as new code should be tested, and in a crisis, it likely won’t be looked at too closely. At least the previous version of the code was tested in production.

    Build the habit to disable, not drop, and I think you’ll be glad you did.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.8MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Singular or Plural

    There are all sorts of interesting debates that developers will have about programming. One of those I’ve written about in the past is Spaces v Tabs. There’s a lot of discussion about that one, and certainly no share of strong opinions. I’m sure there are plenty of other development paradigms and habits that will create debate, arguments, and perhaps strain some working relationships.

    While I’m not looking to upset anyone, I ran across a discussion recently and thought this would make an interesting debate. If you’ve got an opinion, please share how and why you might choose to follow your convention. Examples are helpful and may enhance the reasons why you go choose to build systems one way or the other.

    If you examine any sample databases out there, you will run across tables named like this: Customers, Orders, Cities, etc. You might also find Product, Person, Address as well. If you are paying attention, you might notice that my examples are both singular and plural in their form. There are no shortage of debates on the topic, but I’m wondering what many of you think.

    I’ve tended to build tables with plural nouns in the past, but I think that’s because the first few people that taught me did that. As I’ve read more and listened to others explain their design decisions, I’ve embraced singular names. After all, the entity being modeled is often a single instance of a type. A Person or a CreditCard, not a series of them, though we could certainly argue the table is a set of people, so use that.

    Ultimately I’m not sure that it matters much in any particular application. We certainly have databases that using each convention, and probably a few that use both inside the same schema. I think choosing an entity name that is easy to understand is important, and maybe the idea of singular or plural names matters less. After all, as long as you’re not naming tables F42 and H1492, everyone will get used to the convention.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio (3.5 MB) podcast or subscribe to the feed at iTunes and Libsyn.