Voice of the DBA

Tag: databases

  • Data Improves Lives

    I’m not sure if this NYT article will get through, but I really liked “This Data Isn’t Dull. It Improves Lives“. It talks about how government data that’s around and using it to provide other services and enrich lives.

    Even though I mostly believe the Libertarian philosophy, I disagree in places and one of those places is that I think government ought to do a certain about of work in research and providing services in a way that private companies won’t do it. One of those is producing a lot of data on the world. However since that data is being funded by our tax dollars, I’d like to see the data available for anyone to use as a part of their company. With everyone on an equal footing, we could see some amazing applications.

    The app that shows the bus locations, schedules, and real time updates is cool. Very handy for people that need to use public transportation to move around the city. I know that there are times I would have loved to have had that in my life when I didn’t have a car. Heck, maybe apps like that will encourage more people to use public transport and have less cars on the road.

    Data is truly becoming the World 3.0, at least in my mind, and I’d like to see more government data available, and then used by private companies.

     

     

  • ACID

    What’s ACID in databases? I was asked this in an interview a long time ago and I couldn’t remember the exact meaning. For a (supposedly) senior DBA, that’s bad. I could, however, tell what it means and what it enforces in the database, I couldn’t remember the actual words.

    In building a presentation recently, I needed this, so I looked it up on Wikipedia. Isn’t everything on Wikipedia? And isn’t it true?

    In this case, they do a good job of explaining the various terms. ACID stands for

    • Atomicity
    • Consistency
    • Integrity
    • Durable

    I won’t repeat their explanations, but try to give my own take on this.

    Atomicity – If you have a transaction that makes some change, it has to all succeed or all fail. No partial transactions. That doesn’t mean that each statement succeeds, but everything wrapped in a transaction, whether 1 or 100 statements, all get completed or all rolled back.

    You can appear to muck with this by nesting transactions, but not really. SQL Server follows this rule of enforcing transactions as a complete unit or work. Either all committed, or all rolled back.
    SQLdependencytracker[1] 
    Consistency – This means that the database essentially enforces consistent change to the database from transactions, and that the database is logically consistent at all times. So references are enforced, cascades take place as part of transactions, etc. It’s a strange concept, but it really means that the database enforces all rules defined.
    Note that one funny thing here is the internal sysreferences aren’t always consistent. That one bugs me, though my employer, Red Gate Software, has a tool that finds these: SQL Dependency Tracker

    Isolation – I always get this one wrong, thinking it’s integrity, but really that’s the consistency piece. Isolation means  you cannot access data that is changed but still in an uncommitted transaction state. This essentially ensures that you get a consistent, accurate view of data. SQL Server allows you to bypass this with dirty reads, and lots of people do this to improve performance, but I think it’s a bad idea in general.

    Durability – If you have any type of failure, usually hardware, this ensures that all committed transactions can be restored. Or that uncommitted transactions are rolled back.

    SQL Server enforces this with its roll back/roll forward process when a database is started. This uses the write-ahead log to ensure that the database is in a durable state when users access the data.

  • Common SQL Server Mistakes – GUID as a Clustered PK

    I haven’t been thrilled with GUIDs as primary keys, mainly because I think that it’s hard for humans to work with GUIDs. A GUID, or uniqueidentifier, looks like this:
    ECB6ECB4-ACCB-4382-84D1-19990D59CA2F
    Not exactly something I want to try and type or include in a query. Cut and paste works, but it’s cumbersome. Much easier for me to work with integers.
    I understand that GUIDs have some good advantages. They can reduce round trips, allowing the client to build a primary key and send it to the server. That’s a nice performance trick, and one I’d encourage.
    The real issue, however, is when you make a GUID a primary key on your table, using the defaults. Most people use the defaults, and that’s typically OK. However in this case the defaults cause a problem.
    The default setting for a primary key is a clustered index. For an integer, especially with the identity property, this is OK. All new rows are added to the end of the index, in new space allocations. This creates a hot spot for heavy insertions, but SQL Server handles those OK.
    For a GUID, if I create new rows, I get values like this. These are three new GUIDs I created on my local instance.
    ECB6ECB4-ACCB-4382-84D1-19990D59CA2F
    3406A5AE-A963-48A6-B2FC-03197DC72478
    C5D75C4F-D9EA-4355-A025-2FCC541D6E1E
    If you examine these values, you’ll see that they appear to be random. That’s OK, and it can be a good thing. But for inserting new values, that means that item 3 would be inserted before item 1, and that can cause page splits.
    Page splits are bad for performance. Data has to be moved to a new page, so not only are you inserting xx amount of data onto a page, you might be moving yyy data to a new page. It’s entirely possible that yyy > xx, which could be really bad.
    There are a number of more technical explanations in the references below, but there really is a penalty there. This is in addition to the extra space (16 bytes v 4 bytes for an int). That’s less of an issue, but it’s still an issue.
    The other thing is that all this page splitting creates fragmentation. So not only are your inserts slower, but potentially your read queries are also slower.

    What can you do?

    I think that the first thing you ought to do is read some of the articles below, and consider if you really want to use a GUID as a PK. If you do this…
    GUID_a
    then do this:
    GUID_b
    That will at least minimize some of the performance issues that you might have.
    The other thing you can do on the server, if you are generating the keys with SQL Server, you can use NewSequentialID, which should generate sequential GUIDs, in the same manner that the identity property builds sequential numbers. There are some potential issues, so don’t assume these will always be sequential, especially if you generate some on .NET, but this is better than a clustered index on a GUID.
    Be careful when using defaults, and if you use GUIDs, make sure that it is a good choice for you.

    References:

    A few posts from around the web on the issues of GUIDs as clustered primary keys.