Voice of the DBA

Category: Blog

  • Isolation Levels–MCM Prep

    I was watching the Isolation Levels video for my MCM prep and learned something about the isolation levels. I knew there were four levels, but I hadn’t realized that SQL Server used the default of level 1, out of the 4 ANSI levels. The levels are:

    • 0 – Read Uncommitted
    • 1 – Read Committed
    • 2 – Repeatable Read
    • 3 – Serializable

    You can read more about them here, but what I learned was that these levels are actually organized in a logical manner. If you look at it this way, the levels can also be described as:

    • 0 – Dirty reads allowed, so data that has been changed in a transaction, but not committed could be included in a query. This means that you could return results in a query that don’t exist. The transaction could roll back.
    • 1 – Potential phantom reads or non repeatable read. Queries only read committed data, but since they don’t lock the whole slice of data, a multi-statement body of work could potentially get new rows between statements or return different results.
    • 2 – Repeatable reads guaranteed, but potentially phantom rows could appear.
    • 3 – No repeatable reads or phantoms possible.

    The flip side of the greater data integrity is that more locks are needed, and held longer as you move up the levels. That can impact concurrency.

    As with everything in databases, there is a tradeoff.

    Note that SQL Server allows you to actually get around some of these issues with row level versioning. Of course, this isn’t free. It comes at the expense of space in tempdb.

  • Multi-tenant Architecture

    When you design a database, or at least when I do, I think it’s important to build a database schema that is flexible, and anticipates change. You can’t foresee every change required, but you can consider the types of changes that might be required, the places where data can grow, and plan for some evolution in your design.

    Recently I saw a note asking what people thought a multi-tenant architecture implied. To me this has always been a series of data slices, often separate clients’ data, co-mingled in a single database. It might be separate schemas (not usually), but often was separated by the data itself, with each row having something like a clientid (or businessID, regionID, etc.) in each row.

    However the SQL Azure documentation (Connection constaints, first sentence) apparently implies this means a separate database on the same instance. I wasn’t sure this was correct, but apparently this is an interpretation of the term. According to this MSDN Whitepaper, multi-tenant architecture can be a shared server (separate dbs), shared database (separate schema), or shared schema architecture.

    As a note, Wikipedia’s definition could be one or multiple databases. A Joel-on-Software discussion seems to indicate the definition I was used to, and various blogs I’ve read seem to interpret things differently.

    In any case, however you define it, a multi-tenant architecture seems to imply that multiple groups of users or applications are sharing some resource. This might be the database, the SQL Server instance, or possibly, the Windows host server (mult-instant configuration.

    Mutli-tenant architectures are good in many cases, overall. They more efficiently use resources, and allow you to handle a potential larger group of users with limited resources. They can be overwhelmed, but there are ways to mitigate these issues. I will talk about some pros and cons in another post.

  • iPhone or Android?

    With the announcement of the iPhone on Verizon, I’m getting ready to switch carriers. I’ve had decent luck with a jailbroken iPhone on T-Mobile, but my coverage is spotty, I’ve been working with the two-gees on an Edge network, and I’m ready to pick something up that’s more modern and powerful.

    I was hoping for an LTE iPhone on Verizon, but that didn’t happen. It might happen with the iPhone 5, but I’m not sure. My guess is Verizon is going to see how their new Android phones work on LTE and gauge capacity first. Then we might see one in the fall.

    So my choices boil down to:

    • iPhone 4 on Verizon (3G)
    • 4G Android on Verizon
    • 4G Android on Spring

    I suppose I should consider a Windows Phone 7 device, but honestly I don’t know that I’ll get the app support I want. To me the differences between iOS and Android are non-existant on the hardware. Sure one phone might be slightly more powerful, but not by a lot. The interfaces are similar, I can get a real keyboard on Android, but the rest is just noise. The marketplace is important, and music sync is something I want.

    I looked around a bit and it seems that music sync is something that could work with Android, and I have a few choices there. So really it’s a question of the various apps I use working on Android. It seems that many of them are either on Android, or there are similar ones. So I could make it work on Android, and I wouldn’t necessarily need to re-invest a lot to move to Android.

    Ultimately I’m not sure that I want to move away from iOS if I don’t gain much. I am definitely leaning towards an iPhone 4, and living with the slower 3G speed. Heck, it will be an upgrade for me in cities, and out near the ranch it won’t matter. No 4G is coming here anytime soon. Half the time we can’t get 3G.

    I saw an interesting article about the iPhone on Verizon and how it’s not their best smartphone. I question if that’s true, since the raw hardware specs don’t mean everything. I have a few weeks to think about it and research a bit, so that’s what I’ll do.

  • Resource Governer–Memory Limits

    I had looked at the Resource Governor early on when it was being developed and first released on SQL Server 2008, but I hadn’t spent a lot of time on it. It was the first cut at a throttle that I had been asking for since 2001 or 2002 when I saw IIS get a CPU throttle. I loved the idea of being able to slow down the amount of CPU that an individual query could take, which I thought would really help limit the amount of damage that a particular query could do on your system.

    However I hadn’t spent much time on the memory limits. While studying for the MCM, I downloaded this white paper and watched the Paul Randal video from Technet. In it, I was reminded that the memory limits you set apply to the query memory, not the buffer pool memory.

    What does this mean? It means that while you can limit the memory that a query uses for it’s data, for it’s plans, etc., you can’t prevent a query from flushing the buffer pool when it causes a lot of reads from disk.

    This is a limitation of the Resource Governor, and we might see this changed later, but it’s a good thing to remember that you cannot use Resource Governor as a way to guarentee predictable performance from your SQL Server. The engine is still a shared resource and one person can still affect it’s operation. Resource Governor does allow you to limit the damage, and it can be used to prevent someone from bringing the instance to a standstill. However it does not provide the complete control that you might assume is implied from the name.